SQL Server XQuery Part 4


Insert an XML Variable into an XML Variable – Part 2

Carrying on with our example from the previous post, we will make things a little bit more complicated.

Here is the xml we are working with. We will put these into three xml variables. You can see from the xml below what our intention might be. We need to end up with one xml file where the second and third xml fragment is inserted into the first one at the proper place.

In this example we are using the modify() function with “as first into”. We can also, when necessary, use “as last into”, “before” or “after”. For more information on Insert (XML DML) go to Microsoft’s Books Online. DML stands for Data Modification Language.

Our objective here is to have all of this xml data inside the two root tags.

<root>
<supplier>
	<suppliername>ABC Inc.</suppliername>
	<suppliercode>ABC0001</suppliercode>
	<supplierorder />
</supplier>
<details />
</root>

<order>
	<ordernumber>0001</ordernumber>
	<orderdate>2017-04-07</orderdate>
</order>

<detail>
	<itemname>Widget 45</itemname>
	<itemcode>0045</itemcode>
	<itemquantity>3</itemquantity>
	<itemname>Widget 21</itemname>
	<itemcode>0021</itemcode>
	<itemquantity>1</itemquantity>
</detail>

Here is our sql code in SQL Server.

DECLARE @root XML;
DECLARE @order XML;
DECLARE @detail XML;

SET @root = '
<root>
<supplier>
	<suppliername>ABC Inc.</suppliername>
	<suppliercode>ABC0001</suppliercode>
	<supplierorder />
</supplier>
<details />
</root>'
SET @order = 
'<order>
	<ordernumber>0001</ordernumber>
	<orderdate>2017-04-07</orderdate>
</order>'
SET @detail = '
<detail>
	<itemname>Widget 45</itemname>
	<itemcode>0045</itemcode>
	<itemquantity>3</itemquantity>
	<itemname>Widget 21</itemname>
	<itemcode>0021</itemcode>
	<itemquantity>1</itemquantity>
</detail>'

SET @root.modify('insert sql:variable("@order") as first into (/root/supplier/supplierorder)[1]')
SET @root.modify('insert sql:variable("@detail") as first into (/root/details)[1]')
SELECT @root;

Here is our result.

<root>
  <supplier>
    <suppliername>ABC Inc.</suppliername>
    <suppliercode>ABC0001</suppliercode>
    <supplierorder>
      <order>
        <ordernumber>0001</ordernumber>
        <orderdate>2017-04-07</orderdate>
      </order>
    </supplierorder>
  </supplier>
  <details>
    <detail>
      <itemname>Widget 45</itemname>
      <itemcode>0045</itemcode>
      <itemquantity>3</itemquantity>
      <itemname>Widget 21</itemname>
      <itemcode>0021</itemcode>
      <itemquantity>1</itemquantity>
    </detail>
  </details>
</root>
Series Navigation<< SQL Server XQuery Part 3SQL Server XQuery Part 5 >>

Leave a comment

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