DB2 Version 9.7 for Linux, UNIX, and Windows

Use of updating expressions in a transform expression

DB2® XQuery updating expressions must be used in the modify clause of a transform expression. The updating expressions operate on the copied nodes created by the copy clause of the transform expression.

The following expressions are updating expressions:

A delete expression
An insert expression
A rename expression
A replace 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
Two or more updating expressions, separated by commas where all operands are either updating expressions or an empty sequence

DB2 XQuery returns an error for updating expressions that are not valid. For example, DB2 XQuery returns an error if one branch of a conditional expression contains an updating expression and the other branch contains a non-updating expression that is not the empty sequence.

A transform expression is not an updating expression, because it does not modify any existing nodes. A transform expression creates modified copies of existing nodes. The result of a transform expression can include nodes created by updating expressions in the modify clause of the transform expression and copies of previously existing nodes.

Processing XQuery updating operations

In a transform expression, the modify clause can specify multiple updates. For example, the modify clause can contain two updating expressions, one expression that replaces an existing value, and the other expression that inserts a new element. When the modify clause contains multiple updating expressions, each updating expression is evaluated independently and results in a list of change operations associated with specific nodes that were created by the copy clause of the transform expression.

Within a modify clause, updating expressions cannot modify new nodes that are added by other updating expressions. For example, if an updating expression adds a new element node, another updating expression cannot change the node name of the newly created node.

All the change operations specified in the modify clause of the transform expression are collected and effectively applied in the following order:

The following updating operations are performed in a nondeterministic order:
- Insert operations that do not use ordering keywords such as before, after, as first, or as last.
- All rename operations.
- Replace operations where the keywords value of are specified and the target node is an attribute, text, comment, or processing instruction node.
Insert operations that use ordering keywords such as before, after, as first, or as last.
Replace operations where the keywords value of are not specified.
Replace operations where the keywords value of are specified and the target node is an element node.
All delete operations.

The order in which change operations are applied ensures that a series of multiple changes will have a deterministic result. For an example of how the order of update operations guarantees that a series of multiple changes will have a deterministic result, see the last XQuery expression in Examples.

Invalid XQuery updating operations

During processing of a transform expression, DB2 XQuery returns an error if any of the following conditions occur:

Two or more rename operations are applied to the same node.
Two or more replace operations that use the value of keywords are applied to the same node.
Two or more replace operations that don't use the value of keywords are applied to the same node.
The result of the transform expression is not a valid XDM instance.
An example of an invalid XDM instance is one that contains an element with two attributes where both attributes have the same name.
The XDM instance contains inconsistent namespace bindings.
The following are examples of inconsistent namespace bindings:
- A namespace binding in the QName of an attribute node does not agree with the namespace bindings in its parent element node.
- The namespace bindings in two attribute nodes with the same parent do not agree with each other.

Examples

In the following example, the copy clause of a transform expression binds the variable $product to a copy of an element node, and the modify clause of the transform expression uses two updating expressions to change the copied node:

xquery
transform
copy $product := db2-fn:sqlquery(
  "select description from product where pid='100-100-01'")/product
modify(
  do replace value of $product/description/price with 349.95,
  do insert <status>Available</status> as last into $product )
return $product

The following example uses an XQuery transform expression within an SQL UPDATE statement to modify XML data in the CUSTOMER table. The SQL UPDATE statement operates on a row of the CUSTOMER table. The transform expression creates a copy of the XML document from the INFO column of the row, and adds a status element to the copy of the document. The UPDATE statement replaces the document in the INFO column of the row with the copy of the document modified by the transform expression:

UPDATE customer
SET info = xmlquery( 'transform
   copy $newinfo := $info
   modify do insert <status>Current</status> as last into $newinfo/customerinfo
   return $newinfo' passing info as "info")
WHERE cid = 1003

The following examples use the CUSTOMER table from the DB2 SAMPLE database. In the CUSTOMER table, the XML column INFO contains customer address and phone information.

In the following example, the SQL SELECT statement operates on a row of the CUSTOMER table. The copy clause of the transform expression creates a copy of the XML document from the column INFO. The delete expression deletes address information, and non-work phone numbers, from the copy of the document. The return uses the customer ID attribute and country attribute from the original document from the CUSTOMER table:

SELECT XMLQUERY( 'transform
  copy $mycust := $d
  modify
    do delete ( $mycust/customerinfo/addr, 
       $mycust/customerinfo/phone[@type != "work"] )
  return
  <custinfo>
    <Cid>{data($d/customerinfo/@Cid)}</Cid>
    {$mycust/customerinfo/*}
    <country>{data($d/customerinfo/addr/@country)}</country>
  </custinfo>'
  passing INFO as "d")
FROM CUSTOMER
WHERE CID = 1003

When run against the SAMPLE database, the statement returns the following result:

<custinfo>
  <Cid>1003</Cid>
  <name>Robert Shoemaker</name>
  <phone type="work">905-555-7258</phone>
  <country>Canada</country>
</custinfo>

In the following example, the XQuery expression demonstrates how the order of update operations guarantees that a series of multiple changes will have a deterministic result. The insert expression adds a status element after a phone element, and the replace expression replaces the phone element with an email element:

xquery 
let $email  := <email>jnoodle@my-email.com</email>
let $status := <status>current</status>
return
  transform
  copy $mycust := db2-fn:sqlquery('select info from customer where cid = 1002')
  modify (
    do replace $mycust/customerinfo/phone with $email,
    do insert $status after $mycust/customerinfo/phone[@type = "work"] )
  return $mycust

In the modify clause, the replace expression is before the insert expression. However, when updating the copied node sequence $mycust, the insert update operation is performed before the replace update operation to ensure a deterministic result. When run against the SAMPLE database, the expression returns the following result:

<customerinfo Cid="1002">
  <name>Jim Noodle</name>
  <addr country="Canada">
    <street>25 EastCreek</street>
    <city>Markham</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N9C 3T6</pcode-zip>
  </addr>
  <email>jnoodle@my-email.com</email>
  <status>current</status>
</customerinfo>

If the replace operation were performed first, the phone element would not be in the node sequence, and the operation to insert the status element after the phone element would have no meaning.

For information about the order of update operations, see Processing XQuery updating operations.