Generating an XML output using XML OPTIONS in SQL Server

Question

With a SQL Server database, I try to write a query which looks like -

SELECT DATE, ID, NAME
FROM TEST_TBL 
FOR XML ??

but I'm not sure which XML option (RAW, EXPLICIT, PATH, AUTO) can be used in the query to have an XML output like this:

<resultset>
    <row>
        <column name="DATE">01/01/2016</column>
        <column name="ID">A01</column>
        <column name="NAME">JOHN DOE</column>
    </row>
    <row>
        <column name="DATE">01/02/2016</column>
        <column name="ID">A02</column>
        <column name="NAME">MARY DOE</column>
    </row>
</resultset>

score 2 · Accepted Answer · edited Oct 11 '18 at 01:42

2

Please try this:

SELECT 
'DATE' as 'column/@name', [DATE] as 'column', null, 
'ID' as 'column/@name', [ID] as 'column', null, 
'NAME' as 'column/@name', [NAME] as 'column', null
FROM TEST_TBL
FOR XML PATH('row'), ROOT('resultset')

edited Oct 11 '18 at 01:42

coconuts

7
4

answered Oct 08 '18 at 04:10

Ivien

427
2
5

1

Good answer, +1 from my side, but you'd want to use `'DATE'` instead of `'col1'` and so on... Don't you? – Shnugo Oct 08 '18 at 06:51
very close, but the field names are not shown at the XML output - 01/01/2016 A01 JOHN DOE – coconuts Oct 08 '18 at 18:52
@coconuts, Do not place the column twice... At the beginning of the line replace the `'col1' ` with `'DATE' `. Don't forget the quotes there... – Shnugo Oct 08 '18 at 19:14
@Shungo, many thanks for the reminder. It works most of time, but if the value is NULL, the output of the XML file becomes instead of – coconuts Oct 08 '18 at 22:02
@coconuts, You can use `CASE WHEN` to check whether the value is NULL, then set the value you want – Ivien Oct 09 '18 at 03:24
@Ivien, thanks for very useful tip. Now I run into another issue - each line in the output XML file gets truncated at 2034 in length. Is there any way to not to get truncated or have a nice, organized output form as above by using FOR XML PATH('row'), ROOT('resultset') :OUT TEST.XML GO – coconuts Oct 09 '18 at 18:52
@coconuts, 2 possible ways you can try: 1. `Declare` a XML variable and assign it to your xml select query; 2. `CAST` your xml select query to `VARCHAR(MAX)` – Ivien Oct 10 '18 at 01:58
@Ivien, it is the answer I'm looking for. Thank you so much for giving comments for how-to. It all works now. – coconuts Oct 10 '18 at 20:55
Also thanks for @Shungo, for your very useful comments. – coconuts Oct 10 '18 at 20:55

score 0 · Answer 2 · answered Oct 08 '18 at 03:13

Try this query

declare @XML xml

    set @XML = 

  '<resultset>
    <row>
        <column name="DATE">01/01/2016</column>
        <column name="ID">A01</column>
        <column name="NAME">JOHN DOE</column>
    </row>
    <row>
        <column name="DATE">01/02/2016</column>
        <column name="ID">A02</column>
        <column name="NAME">MARY DOE</column>
    </row>
</resultset>'

    select 
    T.N.value('column[1]', 'varchar(max)') as Date,
    T.N.value('column[2]', 'varchar(max)') as ID,
    T.N.value('column[3]', 'varchar(max)') as Name
    from @XML.nodes('/resultset/row') as T(N)

Result:

Thank you very much for your help. Please pardon me actually what I'm seeking for is the opposite - a SQL server query by using 'FOR XML' options to output an XML file just like the one shown above. — coconuts, Oct 08 '18 at 04:15

Generating an XML output using XML OPTIONS in SQL Server

2 Answers2

Linked