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.
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>
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With