SQL Server XQuery Part 3


SQL Server Insert xml variable into an xml variable

We have two xml variables. We want to insert one into the other. We want to combine the two. Here is a simple example from stackoverflow.com. Here with XQuery we use modify().

There are two xml variables. We are using SQL Server for this.

modify()

Here is the code.

DECLARE @main XML;
DECLARE @inserted XML;
SET @main = '<root></root>';
SET @inserted = '<rows><row>SomeRow</row></rows>';

SELECT @inserted = @inserted.query('/rows/row');

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

SELECT @main;

Here is the result xml.

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

Note that we can change SELECT to SET in this line of code: SELECT @inserted = @inserted.query(‘/rows/row’);

What Happened to rows?

If we change the script to the following we will have rows as part of our results. Also notice that we shortened our code by two rows by initializing the two variables when we declared them. We also added semicolons at the end of each line, although not necessary.

DECLARE @main XML = '<root></root>';
DECLARE @inserted XML = '<rows><row>SomeRow</row></rows>';

SET @inserted = @inserted.query('rows');

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

SELECT @main;

Resuls

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

Another Example

Here we have another level. With query we can specify how many levels we want to insert.

DECLARE @main XML = '<root></root>';
DECLARE @inserted XML = '<rows><row><data>SomeRow</data></row></rows>';

SET @inserted = @inserted.query('/rows/row/data');

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

SELECT @main;

Results

<root>
  <data>SomeRow</data>
</root>

Multiple Rows

Having more than one row is no problem. Our code doesn’t change, only the xml variable does as shown below.

SET @insert = '<rows><row>SomeRow</row><row>SecondRow</row></rows>'

Here is the output.

<root>
  <row>SomeRow</row>
  <row>SecondRow</row>
</root>
Series Navigation<< SQL Server XQuery Part 2SQL Server XQuery Part 4 >>

Leave a comment

Your email address will not be published. Required fields are marked *