SQL Server 2012 creating specific XML from a table

Question

I have a table Table with one column ID. It contains simple letters, let's say Table = 'A', 'B', 'C'.

I need to create an xml file which looks like this

<Table>
  <ID>A</ID>
  <ID>B</ID>
  <ID>C</ID>
</Table>

So I tried

select * from Table for XML AUTO, ELEMENTS

But this yields

<Table>
  <ID>A</ID>
</Table>
<Table>
  <ID>B</ID>
</Table>
<Table>
  <ID>B</ID>
</Table>

So as far as I understand, the inner node collection represents one single record of a table (in this case, consisting of a single field/node) and the surrounding tag indicates the record itself. But this is not what I would prefer to see.

My (quite dirty) solution converts the xml into a string, removes the

</Table><Table>

pairs and then reconstructs the XML

declare @x nvarchar(max)
set @x = (select id from Table for xml auto, elements)
set @x = replace(@x, '</Table><Table>', '')
select cast(@x as xml)

My questions is simple: Is there a correct way to achieve my goal or do I have to live with this ugly 'solution'?

Dave Sexton · Accepted Answer · 2013-02-11 16:58:05Z

3

I think you want to avoid using AUTO if you want a specific output format and use PATH instead. Not tested but how about this:

SELECT * 
FROM Table 
FOR XML PATH('ID'), ROOT('Table')

answered Feb 11, 2013 at 16:58

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

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

2 Comments

alzaimar Over a year ago

Almost, but not quite right. It creates <Table><ID><ID>A</ID></ID><ID>... so an additional inner node '<ID>':-(

alzaimar Over a year ago

So, I got it! "select * from Table for XML Path(''), Root('Table')" Does the trick!

alzaimar · Accepted Answer · 2013-02-11 18:26:41Z

1

@Dave Sexton gave me the right kick:

The solution to the above is as follows:

select ID 
  from Table 
   for XML Path(''), Root('Table')

answered Feb 11, 2013 at 18:26

alzaimar

4,6321 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL Server 2012 creating specific XML from a table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related