none
Converting query result to XML

    Question

  • Hi All,
    I have a dynamic query which produces the following result set

    Col1        Col2           Col3
    -----        -----           -----
      a            z                12
      a            z                34
      a            y                56
      a            y                78
      a            y                90
      b            x                123
      b            x                456
      b            x                789
      b            w                580


    I want to convert this result set into xml format as shown below

    <root>
      <Col1 Name = "a">
        <Col2 Name = "z">12,34</Col2>
        <Col2 Name = "y">56,78,90</Col2>
      </Col1>
      <Col1 Name = "b">
        <Col2 Name = "x">123,456,789</Col2>
        <Col2 Name = "w">580</Col2>
      </Col1>
    </root>

    How can i do this in one query? Is there any way to do this. If you are not clear please feel free to ask any questions.

    I am using SQL Server 2005 SP2.

    Regards,
    Sulaman
    Friday, September 25, 2009 4:07 AM

Answers

  • Try this one

    /*create table #tmp (Col1 varchar(10),Col2 varchar(10),Col3 Int)
    
    Insert into #tmp values ('a','z',12)
    Insert into #tmp values ('a','z',34)
    Insert into #tmp values ('a','y',56)
    Insert into #tmp values ('a','y',78)
    Insert into #tmp values ('a','y',90)
    Insert into #tmp values ('b','x',123)
    Insert into #tmp values ('b','x',456)
    Insert into #tmp values ('b','x',789)
    Insert into #tmp values ('b','w',580)
    */
    
    SELECT
    	col1 AS '@Name',
    	( SELECT
    		col2 AS '@Name',
    		(
    			REPLACE(
    				(SELECT col3 AS 'data()' 
    					FROM #tmp c WHERE c.col1 = b.col1 and c.col2 = b.col2
    					FOR XML PATH(''))
    				, ' ',',')
    		)
    	  FROM #tmp b WHERE a.col1 = b.col1
    	  GROUP BY col1,col2
    	  FOR XML PATH('Col2'), TYPE )
    FROM #tmp a
    GROUP BY col1
    FOR XML PATH('Col1'),ROOT
    
    /*
    <root>
      <Col1 Name="a">
        <Col2 Name="y">56,78,90</Col2>
        <Col2 Name="z">12,34</Col2>
      </Col1>
      <Col1 Name="b">
        <Col2 Name="w">580</Col2>
        <Col2 Name="x">123,456,789</Col2>
      </Col1>
    </root>
    */


    Beyond Relational
    • Proposed as answer by Arif Hasan Friday, September 25, 2009 7:40 AM
    • Marked as answer by Sulaman Friday, September 25, 2009 9:15 AM
    Friday, September 25, 2009 7:19 AM

All replies

  • Hi,

    It will be some thing like this, but need to modify.


    SELECT '<root>'
    SELECT  '<Col1 Name ="' + CONVERT(VARCHAR,[Col1])  + '">' +  '<Col2 Name ="' + CONVERT(VARCHAR,[Col2]) + '">'  + Col3 + '</Col2></Col1>'
    FROM TableName
    SELECT '</root>'
    
    



    Regards,
    Kundan Sinha
    www.java-script.in
    Friday, September 25, 2009 6:03 AM

  •  create table #tmp (Col1 varchar(10),Col2 varchar(10),Col3 Int)

      Insert into #tmp values ('a','z',12)
      Insert into #tmp values ('a','z',34)
      Insert into #tmp values ('a','y',56)
      Insert into #tmp values ('a','y',78)
      Insert into #tmp values ('a','y',90)
      Insert into #tmp values ('b','x',123)
      Insert into #tmp values ('b','x',456)
      Insert into #tmp values ('b','x',789)
      Insert into #tmp values ('b','w',580)

     

    Select * from
    (
     SELECT col1,col2,Left(Col3,Len(Col3)-Len(',')) AS Col3 From (SELECT distinct col1,col2 from #tmp  ) T1     
        CROSS APPLY      
     (SELECT convert(varchar(1000),Col3) + ',' from #tmp T2 where T1.col1=T2.col1 And T1.col2=T2.col2  FOR XML PATH('')) T (Col3)
    )A FOR XML RAW

     

    Friday, September 25, 2009 6:11 AM
  • Try this one

    /*create table #tmp (Col1 varchar(10),Col2 varchar(10),Col3 Int)
    
    Insert into #tmp values ('a','z',12)
    Insert into #tmp values ('a','z',34)
    Insert into #tmp values ('a','y',56)
    Insert into #tmp values ('a','y',78)
    Insert into #tmp values ('a','y',90)
    Insert into #tmp values ('b','x',123)
    Insert into #tmp values ('b','x',456)
    Insert into #tmp values ('b','x',789)
    Insert into #tmp values ('b','w',580)
    */
    
    SELECT
    	col1 AS '@Name',
    	( SELECT
    		col2 AS '@Name',
    		(
    			REPLACE(
    				(SELECT col3 AS 'data()' 
    					FROM #tmp c WHERE c.col1 = b.col1 and c.col2 = b.col2
    					FOR XML PATH(''))
    				, ' ',',')
    		)
    	  FROM #tmp b WHERE a.col1 = b.col1
    	  GROUP BY col1,col2
    	  FOR XML PATH('Col2'), TYPE )
    FROM #tmp a
    GROUP BY col1
    FOR XML PATH('Col1'),ROOT
    
    /*
    <root>
      <Col1 Name="a">
        <Col2 Name="y">56,78,90</Col2>
        <Col2 Name="z">12,34</Col2>
      </Col1>
      <Col1 Name="b">
        <Col2 Name="w">580</Col2>
        <Col2 Name="x">123,456,789</Col2>
      </Col1>
    </root>
    */


    Beyond Relational
    • Proposed as answer by Arif Hasan Friday, September 25, 2009 7:40 AM
    • Marked as answer by Sulaman Friday, September 25, 2009 9:15 AM
    Friday, September 25, 2009 7:19 AM
  • Thanks for your responses guys.

    @Kundan Sinha: This code returns only the concatenated strings and as long as i know it can't be converted into xml in this way. Some XML methods need to be used for this purpose.

    @sanoj_av : Your code returns the proper XML but output format is not correct. Your code returns the xml in the following format

    <row col1="a" col2="y" Col3="56,78,90" />
    <row col1="a" col2="z" Col3="12,34" />
    <row col1="b" col2="w" Col3="580" />
    <row col1="b" col2="x" Col3="123,456,789" />

    There is no parent child relationship for XML elements.

    Thanks again for you quick replies. I am still searching to find out the correct solution. Any other suggestion would be highly appreciated.

    Regards,
    Sulaman
    Friday, September 25, 2009 7:22 AM
  • did you try the solution I posted earlier?
    Beyond Relational
    Friday, September 25, 2009 7:31 AM
  • yeh i tried it now. It works fine for me. Thanks a lot dear!!!
    Friday, September 25, 2009 9:15 AM