XMLModify function: sub-document update on an XML document by Jane Man

XMLModify function: sub-document update on an XML document

xml1.jpg

In DB2 9 for z/OS, an update on an XML document is a delete of whole document following by a new insert. Since DB2 10 for z/OS, we are able to do sub-document update using XMLModify function.

As you may know, DB2 for z/OS stores the XML data in 16k chuck (record) in its internal format. XMLModify function will only update the records that need to be changed. There is a big performance saving if your change is small (say an attribute value) when comparing to the total size of the document.

XMLModify function supports the following actions:

  • insert before
  • insert as first into
  • insert as last into
  • insert into (behave same as “insert as last into”)
  • insert after
  • delete
  • replace node
  • replace value of node   

In the following sections, we will learn about this feature.

Suppose we have created a CUSTOMER table, populated with XML documents.

CREATE TABLE CUSTOMER (ID INTEGER, CUSTXML XML)

The value of ID column is 1 to 10.

The content of the XML document is same for all the rows:

<?xml version="1.0" encoding="UTF-8"?>
<demopo:purchaseOrder id="0" orderDate="2001-01-01" shipDate="2001-01-01" status="" xmlns:demopo="http://www.purchaseOrder.com/purchaseOrder" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.purchaseOrder.com/purchaseOrder POSchema_unq3.xsd ">
  <customer id="0">
    <CustomerName>Jane Man</CustomerName>
  </customer>
  <shipping shippingName="Jane Man">
    <address>
      <Address1>555 Bailey Avenue</Address1>
      <City>San Jose</City>
      <State>CA</State>
      <Zip>95141</Zip>
    </address>
  </shipping>
  <items>
    <item itemName="Toyota Camry" pid="1000" price="20000" quantity="1"/>
  </items>
  <billing billingName="Guogen Zhang">
    <address>
      <Address1>123 Sesame Street</Address1>
      <City>San Jose</City>
      <State>CA</State>
      <Zip>95141</Zip>
    </address>
  </billing>
</demopo:purchaseOrder>

(Please note each of the following UPDATE statement is executed separately on different row.)

Insert Before

Suppose we want to insert the following shippingRemark element before the shipping element for ID=2.

<shippingRemark>Little baby, please be quiet</shippingRemark>

Below is the SQL statement:

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  insert nodes $remark
  before /demopo:purchaseOrder/shipping',
  XMLPARSE(DOCUMENT
   '<shippingRemark>Little baby, please be quiet</shippingRemark>'
   preserve whitespace)
    AS "remark")
WHERE ID =2           

Note:
1. namespace need to be declared
     (
declare namespace demopo=http://www.purchaseOrder.com/purchaseOrder;)
here, otherwise, DB2 will issue error as it cannot find any match to update.
2. the parameter passed to XMLPARSE
     ('
<shippingRemark>Little baby, please be quiet</shippingRemark> ') must be a well-formed document, otherwise, DB2 will issues a non well-formed error.

Insert as First into

Suppose we want to insert a comment

This is an urgent order!

as first child in the demopo:purchaseOrder element for ID=4.

Below is the SQL statement. In this example, we use XML publishing function (XMLCOMMENT) to create the source.

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  insert nodes $remark
  as first into /demopo:purchaseOrder',
  XMLCOMMENT('This is an urgent order!')  
  AS "remark")
WHERE ID =4

 insert as last into, insert into , and insert after can be done similarly.

 

Delete nodes

Suppose we want to delete the item where pid=1000 (i.e. the following item)

<item itemName="Toyota Camry" pid="1000" price="20000" quantity="1"/>

in the items element for ID=6.

Below is the SQL statement:

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  delete nodes
   /demopo:purchaseOrder/items/item[@pid="1000"]' )
WHERE ID =6

Replace node (Element)

Suppose we want to replace /demopo:purchaseOrder/customer

  with <CustomerName>Eric Smith</CustomerName>

Below is the SQL statement:

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  replace node /demopo:purchaseOrder/customer
  with <CustomerName>Eric Smith</CustomerName>' )
WHERE ID =9

Note: XQuery constructor can be directly used to create source (<CustomerName>Eric Smith</CustomerName>) for XMLModify function.

Below the document after update (output truncated):

jane1.jpg

Replace value of node (Attribute)

We want to replace the value of status (attribute in 

/demopo:purchaseOrder element) to shipped for ID=7.

Below is the SQL statement:

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  replace value of node
   /demopo:purchaseOrder/@status with "shipped"'  )
WHERE ID =7

Replace value of node (Element)

Suppose we want to change the value of /demopo:purchaseOrder/customer

  with <CustomerName>Eric Smith</CustomerName>

Below is the SQL statement:

UPDATE CUSTOMER
SET CUSTXML= XMLMODIFY(
 'declare namespace demopo="http://www.purchaseOrder.com/purchaseOrder";
  replace value of node /demopo:purchaseOrder/customer
  with <CustomerName>Eric Smith</CustomerName>' )
WHERE ID =8

 Below the document after update (output truncated):

jane2.jpg

By now, you may notice difference of replace value of node and replace node on an element node.
When the target is an element node, replace value of node will NOT change the element name and the attribute of the target,
while replace node change the whole element, including the element name, attributes of the element.

Summary

XMLModify function allow us to update an XML document with great performance saving.

Author: Jane Man

Senior Software Engineer

janeman@us.ibm.com

Recent Stories
Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas

Fun with Date Ranges