Partial updates of XML documents

To update part of an XML document in an XML column, use the SQL UPDATE statement with the XMLMODIFY built-in scalar function.

The XMLMODIFY function specifies one of the following types of expressions:

  • A single basic updating expression that you can use to insert a node, delete a node, replace a node, or replace the values of a node in an XML document that is stored in XML columns.
  • An updating expression that includes more than one basic updating expression. The updating expression can be in one of the following expressions:
    • A sequence expression
    • A FLWOR expression that contains an updating expression in its return clause
    • A conditional expression that contains an updating expression in its then or else clause

Before you can use XMLMODIFY to update part of an XML document, the column that contains the XML document must support XML versions.

The types of basic updating expressions are:

insert expression
Inserts copies of one or more nodes into a designated position in a node sequence.
replace expression
Replaces an existing node with a new sequence of zero or more nodes, or replaces a node's value while preserving the node's identity.
delete expression
Deletes zero or more nodes from a node sequence.

Example

Suppose that you want to replace the second shipTo node in a purchaseOrder document that has purchase order ID (POID) 5000, and looks like this:

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>

You can use an SQL UPDATE statement like this to replace the shipTo node:

UPDATE PURCHASEORDER
 SET INFO = XMLMODIFY(
  'declare namespace ipo="http://www.example.com/IPO";
  replace node /ipo:purchaseOrder/shipTo[name="Joe Lee"]
  with $x', XMLPARSE(
   '<shipTo exportCode="1" xsi:type="ipo:USAddress">
    <name>Joe Lee</name>
    <street>555 Quarry Road</street>
    <city>Palo Alto</city>
    <state/>CA
    <postcode>94304</postcode>
    </shipTo>') AS "x") 
 WHERE POID=5000

After the statement is executed, the contents of the document in the PORDER column are:

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:USAddress">
   <name>Joe Lee</name>
   <street>555 Quarry Road</street>
   <city>Palo Alto</city>
   <state>CA</state>
   <postcode>94304</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>505 First Street</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>

Example

Suppose that, in the following document, you want to make these changes in a single XMLMODIFY invocation:

  • Change the quantity to 2 for the item with part number 872-AA
  • Delete the item with part number 945- ZG
<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>1</quantity>
   <USPrice>149.99</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
  <item partNum="945-ZG">
   <productName>Sapphire Bracelet</productName>
   <quantity>2</quantity>
   <USPrice>178.99</USPrice>
   <comment>Not shipped</comment>
  </item>
 </items>
</ipo:purchaseOrder>

To make the changes, you can invoke XMLMODIFY with two basic updating expressions like this:

UPDATE PURCHASEORDER
 SET PORDER = XMLMODIFY(
 'declare namespace ipo="http://www.example.com/IPO";
  replace value of node /ipo:purchaseOrder/items/item[@partNum="872-AA"]/quantity
  with xs:integer(2),
  delete node /ipo:purchaseOrder/items/item[@partNum="945-ZG"]’);

After the statement is executed, the contents of the document are:

<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>2</quantity>
   <USPrice>149.99</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
</items>
</ipo:purchaseOrder>

Example

Suppose that, in the following document, you want to increase the price of each item by 10% in a single XMLMODIFY invocation:

<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>1</quantity>
   <USPrice>149.99</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
  <item partNum="945-ZG">
   <productName>Sapphire Bracelet</productName>
   <quantity>2</quantity>
   <USPrice>178.99</USPrice>
   <comment>Not shipped</comment>
  </item>
 </items>
</ipo:purchaseOrder>

To make the changes, you can invoke XMLMODIFY with a FLWOR expression like this:

UPDATE PURCHASEORDER
 SET PORDER = XMLMODIFY(
  'declare namespace ipo="http://www.example.com/IPO";
   for $i in /ipo:purchaseOrder/items/item
    let $p := $i/USPrice
     where xs:decimal($p)>0
   return
    replace value of node $p with $p *1.1’);

After the statement is executed, the contents of the document are:

<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>1</quantity>
   <USPrice>164.99</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
  <item partNum="945-ZG">
   <productName>Sapphire Bracelet</productName>
   <quantity>2</quantity>
   <USPrice>196.89</USPrice>
   <comment>Not shipped</comment>
  </item>
 </items>
</ipo:purchaseOrder>

Example

Suppose that, in the following document, you want to make the following changes in a single XMLMODIFY invocation:
  • For any items with a quantity of more than one, decrease the price by 10%
  • For any items with a quantity of one, increase the price by 5%
<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>1</quantity>
   <USPrice>149.99</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
  <item partNum="945-ZG">
   <productName>Sapphire Bracelet</productName>
   <quantity>2</quantity>
   <USPrice>178.99</USPrice>
   <comment>Not shipped</comment>
  </item>
 </items>
</ipo:purchaseOrder>

To make the changes, you can invoke XMLMODIFY with a conditional expression like this:

UPDATE PURCHASEORDER
 SET PORDER = XMLMODIFY(
  'declare namespace ipo="http://www.example.com/IPO";
   for $i in /ipo:purchaseOrder/items/item
    let $p := $i/USPrice
    let $q := $i/quantity
   where xs:decimal($p)>0 and xs:integer($q)>0
   return
   (
    if (xs:integer($q) > 1) then
     replace value of node $p with $p *0.9
    else
     replace value of node $p with $p *1.05
   )’
 );

After the statement is executed, the contents of the document are:

<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
  <name>Helen Zoe</name>
  <street>47 Eden Street</street>
  <city>Cambridge</city>
  <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
  <item partNum="872-AA">
   <productName>Lawnmower</productName>
   <quantity>1</quantity>
   <USPrice>157.49</USPrice>
   <shipDate>2011-05-20</shipDate>
  </item>
  <item partNum="945-ZG">
   <productName>Sapphire Bracelet</productName>
   <quantity>2</quantity>
   <USPrice>161.09</USPrice>
   <comment>Not shipped</comment>
  </item>
 </items>
</ipo:purchaseOrder>