Create XML from Sql query

Question

How to create XML in below format from SQL Query in SQL Server?

<ROOT><ELEMENT>VALUE1</ELEMENT></ROOT>
<ROOT><ELEMENT>VALUE2</ELEMENT></ROOT>
<ROOT><ELEMENT>VALUE3</ELEMENT></ROOT>

The root-node is the one (and only!) outer-most element. There is no rule that it must be named <root>, nor is there a rule, that nodes within the XML should not be named as such. But it is - at least - very unusual... The multiple <ROOT> nodes could mean, that you need several independant XMLs... If the existing answers do not solve your issue, please poste some sample data and the expected output, together with some details about your goal. And - last but not least - show what you've tried already... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 20, 2017 at 20:35

j.v. · Accepted Answer · 2017-01-20 14:47:26Z

1

 SELECT t.value AS ELEMENT          
     FROM [dbo].tbl t
     FOR XML PATH ('ROOT');

answered Jan 20, 2017 at 14:47

j.v.

9976 silver badges15 bronze badges

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

Comments

Andrey Korneyev · Accepted Answer · 2017-01-20 14:54:16Z

1

You can do it something like:

select T.Element as 'Element'
from 
(
    select 'VALUE1' as Element
    union all 
    select 'VALUE2'
    union all 
    select 'VALUE3'
) as T
for xml path('ROOT')

See MSDN for more information.

edited Jan 20, 2017 at 14:54

answered Jan 20, 2017 at 14:42

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

3 Comments

Pரதீப் Over a year ago

Derived table is not necessary

Andrey Korneyev Over a year ago

@Prdp well, it is just "ready-to-use" snippet and subquery is just emulation of source table.

gkarya42 Over a year ago

got the solution SELECT ROW.ELEMENT AS ELEMENT FROM table_name ROW FOR XML AUTO,elements

Collectives™ on Stack Overflow

Create XML from Sql query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related