Start of change

XMLMODIFY

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.

Read syntax diagram
>>-XMLMODIFY--(--xquery-update-constant--+---------------------------------------------------+--)-><
                                         |    .-,------------------------------------------. |      
                                         |    V                                            | |      
                                         '-,----xquery-variable-expression--AS--identifier-+-'      

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 an insert expression, a delete expression, or a replace expression. 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.

  1. Insert operations that use ordering keywords such as before, after, as first, or as last
  2. Replace operations in which the keyword value of is not specified
  3. Replace operations in which the keyword value of is specified, and the target node is an element node
  4. 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':Start of change
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")
End of change 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>
End of change