Paste the child nodes from the xml 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 @insert line child nodes, so that I have the resulting XML that looks like this:

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

I am good at knowing how to embed xml from sql variables (using sql: variable ("@insert")), but this inserts all the XML, including the root element. I only want to insert the children of the root element into the @insert variable.

Thanks a lot.

Answer 1

One way is to change the data in a variable that needs to be inserted 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 

Conclusion:

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

Answer 2

, , , , , , . . XML.

-- 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

:

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