XMLMODIFY scalar function

The XMLMODIFY function returns an XML value that might have been modified by the evaluation of an XQuery updating expression and XQuery variables that are specified as input arguments.

The schema is SYSIBM.

xquery-update-constant

Specifies an SQL character string constant that is interpreted as an XQuery updating expression that uses supported XQuery language syntax. xquery-update-constant must be one of the following items:

An insert expression.
A delete 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.
A sequence expression that contains two or more updating expressions, separate by commas. All operands are either updating expressions or an empty sequence.

xquery-update-constant must not be an empty string or a string of all blanks.

xquery-variable expression

xquery-variable-expression specifies an SQL expression whose value is available to the XQuery expression that is specified by xquery-update-constant during execution.

The data type of xquery-variable-expression can be XML, integer, decimal, or a character or graphic string that is not a LOB. xquery-variable-expression must not return a ROWID, TIMESTAMP, binary string, REAL, DECFLOAT data types, or a character string that is bit data, and xquery-variable-expression must not reference a sequence expression. If the result value is of type XML, it is passed by reference, which means that the original values, not copies, are used in the evaluation of the XQuery expression. A null XML value is converted to an XML empty sequence. If the resulting value is not of type XML, the result of the expression must be castable to an XML value. A null value is converted to an XML empty sequence. The non-XML values creates a new copy of the value during the cast to XML.

An XQuery variable is created for each xquery-variable-expression this is specified, and the XQuery variable is set to a value that is equal to the input-xml-value.

AS identifier

Specifies that the value that is generated by xquery-variable-expression is passed to xquery-update-constant as an XQuery variable named identifier. The length of the name must not be longer than 128 bytes. If the length of the name is longer than 128 bytes, an error is returned. The leading dollar sign ($) that precedes variable names in the XQuery language is not included in identifier. The name must be an XML 1.0 NCName that is not the same as the identifier for another xquery-variable-expression in the same PASSING clause. If the identifier is not an XML 1.0 NCName an error is returned. If more than one xquery-variable expression have the same name, an error is returned. If the result of an xquery-variable expression is null, an empty sequence is assigned to the corresponding XQuery variable.

The XMLMODIFY function can only be used in an SQL UPDATE statement or within the update clause of an SQL MERGE statement. The XMLMODIFY function must be the topmost expression on the right hand side of the SET assignment clause of the update.

Start of change In an XMLMODIFY invocation, 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 newly created node. End of change

Start of change For an XMLMODIFY invocation, the Db2 database manager applies updating expressions in the following order: End of change

The following operations, in a non-deterministic order:
- Insert operations that do not use ordering keywords, such as before, after, as first, or as last
- Replace operations in which the keyword value of is specified, and the target node is an attribute node, text node, comment node, or processing instruction node
Insert operations that use ordering keywords such as before, after, as first, or as last
Replace operations in which the keyword value of is not specified
Replace operations in which the keyword value of is specified, and the target node is an element node
All delete operations

The target-xml-column is the XML column in the SET assignment clause that is to be updated by the value that is returned by the XMLMODIFY function. The initial context item in the XQuery updating expression is the value of the target-xml-column that is passed by reference. Only the value of the target-xml-column can be modified by the XQuery updating expression. In other words, the target expression nodes in the XQuery updating expression must be a node in the value of target-xml-column. The target-xml-column must be an XML column that is defined in the XML versioning format.

The value of target-xml-column that is modified by the XQuery updating expression is returned by the function. If the value of target-xml-column is null, the function returns null. Otherwise, the result of the XMLMODIFY function must be a well-formed XML document. If the XQuery updating expressions makes no modifications to the value of target-xml-column, the unmodified XML value is returned by the function. The XMLMODIFY function preserves the original node identities and the document order of target-xml-column. Although XMLMODIFY modifies target-xml-column by reference, for each row that is updated by the SQL UPDATE statement, any reference to target-xml-column in the SQL UPDATE statement is the value of the target-xml-column before the row is updated.

Example 1: The following is an example of an XMLMODIFY function with an XQuery insert expression. Assume that a table contains a column named PO that contains an XML document, 'purchaseOrders':

UPDATE purchaseOrders
   SET PO = XMLMODIFY('declare namespace ipo="http://www.example.com/IPO";
                       declare namespace pyd="http://www.examplepayment.com";
                       insert node $payment/@pyd:paidDate 
                         as first into /ipo:purchaseOrder/billTo', 
                       XMLPARSE(DOCUMENT 
												'<payment xmlns:pyd="http://www.examplepayment.com"
                                    pyd:paidDate="2000-01-07">278.94
												 </payment>') AS "payment")

The result of the purchaseOrders XML document in the PO column is as follows:

<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>
  .
  .
  .
 </ipo:purchaseOrder>

Example 2: Assume that table PURCHASEORDER contains a column named PORDER that contains the following XML document:

<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>

The following is an example of an XMLMODIFY function with an XQuery replace expression and an XQuery delete expression.

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 UPDATE statement executes, the result is as follows: End of change

<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>