Return XML which contains node from XML column

Question

I have a SQL Server table with an XML column like this:

create table tab
(
     Id int, 
     Title nvarchar(200), 
     XmlCol xml
)

I need to return an XML like this

<tabs>
    <tab Id="1" Title="Some Title">
        <xmlcontent>...</xmlcontent>
        <xmlcontent>...</xmlcontent>
        <xmlcontent>...</xmlcontent>
    </tab>
</tabs>

My best approach so far is:

select Id, Title, XmlCol.Query('xmlcontent')
from tab
for xml raw('tab'), root('tabs')

but I am getting this:

<tabs>
    <tab Id="1" Title="Some Title">
        <XmlCol>
            <xmlcontent>...</xmlcontent>
            <xmlcontent>...</xmlcontent>
            <xmlcontent>...</xmlcontent>
        <XmlCol>
    </tab>
</tabs>

XmlCol column contains XML data like this (not well-formed)

 <xmlcontent>...</xmlcontent>
 <xmlcontent>...</xmlcontent>
 <xmlcontent>...</xmlcontent>

Any idea how to flatten the structure to omit the XmlCol node?

lptr · Accepted Answer · 2020-12-07 18:10:30Z

1

declare @tab table(Id int, Title nvarchar(200), XmlCol xml);
insert into @tab(id, Title, XmlCol)
values(1, N'1 Title test 1', N'<xmlcontent><e1 x="1">a</e1><e2 x="1" y="2">b</e2><test>a test</test> xmlcontent1 text</xmlcontent>'),
(2, N'2 Title test 2', N'<xmlcontent><e1 x="1">111</e1><e2 x="1" y="2">222</e2><test>333</test>xmlcontent2 text</xmlcontent>');

select Id, Title, XmlCol.query('xmlcontent/node()')
from @tab as tab
for xml raw('tab'), root('tabs');

answered Dec 7, 2020 at 18:10

lptr

6,8582 gold badges10 silver badges16 bronze badges

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

Comments

Lukasz Szozda · Accepted Answer · 2020-12-07 18:09:32Z

0

Using:

SELECT Id, Title , XmlCol.query('//xmlcontent')
FROM tab
FOR XML RAW('tab'), ROOT('tabs');

db<>fiddle demo

Output:

<tabs>
  <tab Id="1" Title="Some Title">
     <xmlcontent>...</xmlcontent>
     <xmlcontent>...</xmlcontent>
     <xmlcontent>...</xmlcontent>
  </tab>
</tabs>

answered Dec 7, 2020 at 18:09

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Return XML which contains node from XML column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related