Combine Multiple Rows of XML Data into One XML Value (SQL Server)

Question

I have a table with two columns like this (DataXML is xml data type):

 RowID DataXML
 ––––– –––––––––––––––––––––––––––
 1     <ELEM FOO="11" BAR="A" />
 2     <ELEM FOO="22" BAR="B" />
 3     <ELEM FOO="33" BAR="C" />
 4     <ELEM FOO="44" BAR="D" />

I would like to write a T-SQL statement something like this:

declare @MyXML xml;
set @MyXML = (
   SELECT  DataXML
   FROM    MyTable
   WHERE   RowID BETWEEN 2 AND 3
   FOR     XML ??????
);

and end up with this document in @MyXML:

<ROOT>
  <ELEM FOO="22" BAR="B" />
  <ELEM FOO="33" BAR="C" />
</ROOT>

How do I write the query for my set @MyXML = ... statement to get my desired result?

Thank you for reading my question.

Dan

Kittoes0124 · Accepted Answer · 2017-08-30 18:55:14Z

2

Subselects without a column alias won't have their results wrapped in an XML element:

declare @data table (
    [RowID] int not null
  , [DataXML] xml not null
);

insert into @data ([RowID], [DataXML])
values (1, N'<ELEM FOO="11" BAR="A" />')
     , (2, N'<ELEM FOO="22" BAR="B" />')
     , (3, N'<ELEM FOO="33" BAR="C" />')
     , (4, N'<ELEM FOO="44" BAR="D" />');

select (
           select (select a.[DataXML])
           from @data as a
           where a.RowID between 2 and 3
           for xml path(''), type
       )
for xml path('ROOT'), type;

Returns:

<ROOT>
    <ELEM FOO="22" BAR="B" />
    <ELEM FOO="33" BAR="C" />
</ROOT>

edited Aug 30, 2017 at 18:55

answered Aug 30, 2017 at 18:37

Kittoes0124

5,1024 gold badges30 silver badges48 bronze badges

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

1 Comment

JediSQL Over a year ago

Awsome! Perfect. Thank you.

Collectives™ on Stack Overflow

Combine Multiple Rows of XML Data into One XML Value (SQL Server)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related