CODEWITHSUNDEEP
sql-serverxmlsql-server-2012
alzaimar
alzaimar

Reputation: 4622

SQL Server 2012 creating specific XML from a table

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'?

Upvotes: 1

Views: 2112

Answers (2)

alzaimar
alzaimar

Reputation: 4622

@Dave Sexton gave me the right kick:

The solution to the above is as follows:

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

Upvotes: 1

Dave Sexton
Dave Sexton

Reputation: 11188

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')

Upvotes: 3

Related Questions