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>

coconuts · Accepted Answer · 2018-10-11 01:42:52Z

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, 2018 at 1:42

coconuts

74 bronze badges

answered Oct 8, 2018 at 4:10

Ivien

4672 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Gottfried Lesigang Over a year ago

Good answer, +1 from my side, but you'd want to use 'DATE' instead of 'col1' and so on... Don't you?

coconuts Over a year ago

very close, but the field names are not shown at the XML output - <column name="01/01/2016">01/01/2016</column> <column name="A01">A01</column> <column name="JOHN DOE">JOHN DOE</column>

Gottfried Lesigang Over a year ago

@coconuts, Do not place the column twice... At the beginning of the line replace the 'col1' with 'DATE' . Don't forget the quotes there...

coconuts Over a year ago

@Shungo, many thanks for the reminder. It works most of time, but if the value is NULL, the output of the XML file becomes <column name="ID"/> instead of <column name="ID"></column>

Ivien Over a year ago

@coconuts, You can use CASE WHEN to check whether the value is NULL, then set the value you want

|

Dhana · Accepted Answer · 2018-10-08 03:13:30Z

0

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:

answered Oct 8, 2018 at 3:13

Dhana

1,6584 gold badges24 silver badges40 bronze badges

1 Comment

coconuts Over a year ago

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.

Collectives™ on Stack Overflow

Generating an XML output using XML OPTIONS in SQL Server

2 Answers 2

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related