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

Answer 1

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>