How to merge XML in T-SQL?

Question

It doesn't seem that any amount of reading the docs will help me. Consider the simplified example:

declare @table1 table ( id int, parent xml )
insert @table1 values( 1, '<Root></Root>' )
declare @table2 table ( id int, guts xml )
insert @table2 values( 1, '<Guts>hi mom!</Guts>' )

select t1.parent.query('')
from @table1 t1 inner join @table2 t2 on t1.id = t2.id

What would be passed to the query function to generate this result?

<Root><Guts>hi mom!</Guts></Root>

Answer 1

You are asking for an XML operation, not for a relational operation. What you want is to produce a new XML by inserting a fragment of XML into it, which means you have to use the xml.modify() method. Technically this is possible, but the modify() must be called within an update context, so it won't work in a SELECT. It can work in a SET or in an UPDATE:

UPDATE t1
 SET parent.modify(N'insert sql:column("t2.guts") into (/Root)[1]')
FROM @table1 t1
 JOIN @table2 t2 on t1.id = t2.id;
SELECT * from @table1;

If you must have the result in a SELECT then you'll have to shred the XML into relational table, join that and reconstruct the XML back using FOR XML.

Answer 2

The following is not set based, but maybe it will help (SQL2008 only)

declare @table1 table ( id int, parent xml )
insert @table1 values( 1, '<Root></Root>' )
declare @table2 table ( id int, guts xml )
insert @table2 values( 1, '<Guts>hi mom!</Guts>' )

DECLARE @id int;
DECLARE @results table (id int, results xml);

DECLARE idCursor CURSOR FOR 
    select id from @table1
OPEN idCursor

FETCH NEXT FROM idCursor INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @parent xml, @guts xml

    SELECT @parent = parent FROM @table1 where id = 1;
    SELECT @guts = guts FROM @table2 where id = 1;
    SET @parent.modify('insert sql:variable("@guts") into (/Root[1])');

    INSERT @results (id, results) values (@id, @parent);

    FETCH NEXT FROM idCursor INTO @id

END 

CLOSE idCursor
DEALLOCATE idCursor

select * from @results;