CODEWITHSUNDEEP
sqlsql-serverxml
user7739833
user7739833

Reputation:

Wrap XML values with a root - sql server

I am trying to wrap all xml values with a root value.

Create table #temp( ID bigint, Child_XMl xml)

Insert into #temp values (1,'<Root>A</Root>')
Insert into #temp values (1,'<Root>B</Root>')
Insert into #temp values (1,'<Root>C</Root>')
Insert into #temp values (2,'<Root>D</Root>')

Select Child_XMl  from #temp
where ID=1
for xml path(''), type,elements

Expected Result :

<Roots>
    <Root>A</Root>
    <Root>B</Root>
    <Root>C</Root>
<Roots>

Upvotes: 2

Views: 173

Answers (2)

D-Shih
D-Shih

Reputation: 46219

You can try to use ROOT in FOR XML

SELECT Child_Xml.query('Root')
FROM #temp
WHERE ID = 1
FOR XML PATH(''), ROOT('Roots')

sqlfiddle

Upvotes: 1

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

The trick is to make sure the column is nameless; when the column is of type XML, it will be inlined. This requires the use of an expression, as explicitly aliasing a column to an empty name is not allowed.

SELECT Child_Xml.query('.')
FROM #temp
WHERE ID = 1
FOR XML PATH(''), ROOT('Roots'), TYPE

Upvotes: 3

Related Questions