Insert child nodes from an SQL xml variable into another xml variable

Question

I have two xml variables:

@main xml = '<root></root>'
@insert xml = '<rows><row>SomeRow</row></rows>'

And I would like to insert child nodes of @insert rows, so that I have a resultant XML that looks like this:

<root>
   <row>SomeRow</row>
</root>

I am well aware of inserting xml from sql variables (using sql:variable("@insert")) but this inserts the whole XML including the root element. What I want is to only insert the child nodes of the root element in the @insert variable.

Thanks a bunch.

Cavein

Answer 1

One way is to change data in variable you need to insert into another xml:

DECLARE @main xml = '<root></root>',
        @insert xml = '<rows><row>SomeRow</row></rows>'

SELECT @insert = @insert.query('/rows/row')

SET @main.modify('             
insert sql:variable("@insert")             
into (/root)[1] ')             
SELECT @main 

Output:

<root>
  <row>SomeRow</row>
</root>

Inharmonic

Thanks gofr1, you are right, and this is what I usually do, but wanted to see if there was some syntax that allowed for it to happen in one fowel swoop! – Cavein

Answer 2

Okay, in the spirit of closing this, I have a workaround which is technically a one-liner that can be used to do what I want. It has proven to work well for me and avoids creating an intermediate variable. But also only works in mainly single-level XMLs.

-- The same variables, I added '<tag>' to demonstrate the insert
DECLARE @main xml = '<root><tag>Some data Here</tag></root>'
DECLARE @insert xml = '<rows><row>SomeRow</row></rows>'

-- One-liner that inserts '@insert' into @main 
SET @main = (
             SELECT @main.query('/root/*'), @insert.query('/rows/*') 
             FOR XML RAW(''),ROOT('root'), ELEMENTS, TYPE
             )
SELECT @main

Output:

<root>
  <tag>Some data Here</tag>
  <row>SomeRow</row>
</root>

Cavein