Start of change

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 a basic updating expression that you can use to insert nodes, delete nodes, replace nodes, or replace the values of a node in XML documents that are stored in XML columns.

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>
End of change