none
sql-For XML not supporting Operators

    Question

  • Hi ,

    I am working on SQL2008,
    I have a Table where I want to Concatinate Multiple Row to Make them as One Column.
    I have used below Code .
    BUT SIGN like ">","<","<=" are not coming in Proper Format .

    CREATE TABLE TEST_DATA (ID INT,CODE INT ,QUERY_FORM VARCHAR(MAX))
    	INSERT INTO TEST_DATA
    	SELECT 1,1,'(COLNAME > 1)+' UNION ALL
    	SELECT 1,2,'(COLNAME2 < 2)*' UNION ALL
    	SELECT 1,3,'(COLNAME2.COL4 >= 2)+' 
    	SELECT * FROM TEST_DATA
    	
    	SELECT DISTINCT ID,(SELECT ''+ SUB.QUERY_FORM FROM TEST_DATA SUB WHERE SUB.ID=OUTSIDE.ID ORDER BY ID,CODE FOR XML PATH('')  )AS TEXT_STRING FROM TEST_DATA OUTSIDE

    ANY HELP Why Operators are not coming Correct ...Could some One Modify the above Query to get Correct Output ??

    • Moved by Kalman Toth Monday, August 26, 2013 3:27 PM Not t-sql
    Saturday, August 24, 2013 12:07 PM

Answers

  • If you want to use FOR XML to concatenate strings that can contain XML control characters (such as < > &), you need to use the TYPE directive.  You should use TYPE unless you know the data cannot contain such characters.  So you want

    Select Distinct OUTSIDE.ID,
      (Select '' + SUB.QUERY_FORM From TEST_DATA SUB Where SUB.ID = OUTSIDE.ID Order By SUB.CODE 
    	For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)') As TEXT_STRING
    From TEST_DATA OUTSIDE;

    Tom


    • Proposed as answer by Kalman Toth Saturday, August 24, 2013 5:33 PM
    • Marked as answer by SQL_helpus Tuesday, August 27, 2013 4:34 PM
    Saturday, August 24, 2013 3:12 PM

All replies

  • these are not operators while working with xml data(><)

    may be you can try this,

    SELECT DISTINCT ID,
    	replace(replace((SELECT ''+ SUB.QUERY_FORM FROM TEST_DATA SUB WHERE SUB.ID=OUTSIDE.ID 
    	ORDER BY ID,CODE FOR XML PATH('')),'&gt;','>'),'&lt;','<')
    	AS TEXT_STRING FROM TEST_DATA OUTSIDE



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, August 24, 2013 12:20 PM
  • If you want to use FOR XML to concatenate strings that can contain XML control characters (such as < > &), you need to use the TYPE directive.  You should use TYPE unless you know the data cannot contain such characters.  So you want

    Select Distinct OUTSIDE.ID,
      (Select '' + SUB.QUERY_FORM From TEST_DATA SUB Where SUB.ID = OUTSIDE.ID Order By SUB.CODE 
    	For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)') As TEXT_STRING
    From TEST_DATA OUTSIDE;

    Tom


    • Proposed as answer by Kalman Toth Saturday, August 24, 2013 5:33 PM
    • Marked as answer by SQL_helpus Tuesday, August 27, 2013 4:34 PM
    Saturday, August 24, 2013 3:12 PM
  • Hi TOM,

    Thanks for Reply ..Your Query gives me desire Output ,but Could you please help me understanding

    1)WHAT "Type" is doing?

    2) Will This Query support all the Operators ?? As I have no idea in future what all operators could come in "Query_form" column

    Monday, August 26, 2013 5:36 AM
  • I am moving it XML.

    >1)WHAT "Type" is doing?

    You need to do some reading on your own. When you get stuck we help you.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Monday, August 26, 2013 3:27 PM