none
T-SQL query as XML

    Question

  • Hi,

    i would like to query data from a table and save it as an xml file.

    The xml should have the following format:

    <row>

    <field name="columnname">columndata</field>

    <field name="columnname">columndata</field>

    <field name="columnname">columndata</field>

    </row>

     

    For example:

    column1|column2|column3

    -----------------------------

    data1|data2|data3

    data11|data21|data31

     

    should result in:

    <row>

    <field name="column1">data1</field>

    <field name="column2">data2</field>

    <field name="column3">data3</field>

    </row>

    <row>

    <field name="column1">data12</field>

    <field name="column2">data21</field>

    <field name="column3">data31</field>

    </row>

     

    Thank you!

    Wednesday, February 02, 2011 12:45 PM

Answers

  • Try this simple solution using FOR XML PATH and FLWOR loops to reformat the XML.

    CREATE TABLE #tmp ( column1 VARCHAR(20), column2 VARCHAR(20), column3 VARCHAR(20) )
    
    INSERT INTO #tmp VALUES ( 'data1', 'data2', 'data3' )
    INSERT INTO #tmp VALUES ( 'data11', 'data21', 'data31' )
    
    -- FOR XML PATH with ELEMENTS will automatically unpivot the data for you
    -- Then reshape your XML using nested FLWOR loops
    SELECT
    	(
    	SELECT *
    	FROM #tmp t
    	FOR XML PATH, ELEMENTS, TYPE
    	).query('
    for $e in row
     return 
     <row>{
      for $f in $e/*
       return <field name="{local-name($f)}">{data($f)}</field>
    	 }
     </row>
    ')
    

    This is nice and dynamic so the column names in the table can really be anything.  However you should know that this won't be a great performer for large volumes of data.

    You should also know XML is quite bulky and not a great way to move large volumes of data between SQL Servers.

    • Proposed as answer by Naomi NModerator Thursday, February 03, 2011 3:29 AM
    • Marked as answer by Kirizzle Monday, February 07, 2011 8:56 AM
    Wednesday, February 02, 2011 2:10 PM

All replies

  • Hello Krizzle,

    What you are asking is FOR XML PATH

     

    select * from TableName for XML path('row')
    

    SQL Server and T-SQL Tutorials
    SQL XML Query
    Wednesday, February 02, 2011 1:04 PM
  • Try this simple solution using FOR XML PATH and FLWOR loops to reformat the XML.

    CREATE TABLE #tmp ( column1 VARCHAR(20), column2 VARCHAR(20), column3 VARCHAR(20) )
    
    INSERT INTO #tmp VALUES ( 'data1', 'data2', 'data3' )
    INSERT INTO #tmp VALUES ( 'data11', 'data21', 'data31' )
    
    -- FOR XML PATH with ELEMENTS will automatically unpivot the data for you
    -- Then reshape your XML using nested FLWOR loops
    SELECT
    	(
    	SELECT *
    	FROM #tmp t
    	FOR XML PATH, ELEMENTS, TYPE
    	).query('
    for $e in row
     return 
     <row>{
      for $f in $e/*
       return <field name="{local-name($f)}">{data($f)}</field>
    	 }
     </row>
    ')
    

    This is nice and dynamic so the column names in the table can really be anything.  However you should know that this won't be a great performer for large volumes of data.

    You should also know XML is quite bulky and not a great way to move large volumes of data between SQL Servers.

    • Proposed as answer by Naomi NModerator Thursday, February 03, 2011 3:29 AM
    • Marked as answer by Kirizzle Monday, February 07, 2011 8:56 AM
    Wednesday, February 02, 2011 2:10 PM
  • Thank you! But my problem is, that i can't get the comlumn names into the elements attribute.

     

    Greetings

    Monday, February 07, 2011 8:50 AM
  • Hey Naomi!

     

    Thank you. This seems like a nice solution for my problem!

    I use the xml data for a migration, because *.csv is way bulkier that xml :)

     

    Thank you!

    Monday, February 07, 2011 8:52 AM
  • I only proposed this nice solution as a solution, the credit for it goes to wBob.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, February 08, 2011 2:14 AM