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.

gofr1 · Accepted Answer · 2016-07-22 08:46:34Z

5

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>

answered Jul 22, 2016 at 8:46

gofr1

16k11 gold badges47 silver badges56 bronze badges

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

1 Comment

Vinnie Amir Over a year ago

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!

Vinnie Amir · Accepted Answer · 2017-09-27 00:26:23Z

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>

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related