Start of change

Replace expression

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

Syntax

Read syntax diagram
>>-replace--+----------+--node--target-expression--with--source-expression-><
            '-value of-'                                                     
replace
The keyword that begins a replace expression.
value of
The keywords that specify replacing the value of the target-expression node that is to be replaced.
node
The keyword that begins the target expression.
target-expression
An XQuery expression that is not an updating expression. The result of target-expression must be a single node that is not a document node. If the result of target-expression is an empty sequence, an error is returned.

If the value of keywords are not specified, the result of target-expression must be a single node whose parent property is not empty.

with
The keyword that begins the source expression.
source-expression
An XQuery expression that is not an updating expression.

If the value of keywords are specified, the result of source-expression is a single text node or an empty sequence. During processing, atomization is applied to source-expression, to convert it to a sequence of atomic values. If the result of atomization is an empty sequence, the result of source-expression is an empty sequence. Otherwise, each atomic value in the atomized sequence is cast to a string. All of the strings are concatenated, with a single space character between each pair of strings.

If the value of keywords are not specified, the result of source-expression must be a sequence of nodes. If the source-expression sequence contains a document node, the document node is replaced by its children. If source-expression contains an adjacent sequence of one or more atomic values, a new text node is constructed containing the result of casting each atomic value to a string, with a single space character inserted between adjacent values. The source-expression sequence must consist of the following node types:

  • If the target-expression node is an attribute node, the replacement sequence must consist of zero or more attribute nodes.
  • If the target-expression node is an element, text, comment, or processing instruction node, the replacement sequence must consist of some combination of zero or more element, text, comment, or processing instruction nodes.
The following updates are generated when the value of keywords are specified:
  • If the target-expression node is an element node, the existing children of the target-expression node are replaced by the text node returned by the source-expression. If the source-expression returns an empty sequence, the children property of the target-expression node becomes empty. If the target-expression node contains attribute nodes, they are not affected.
  • If the target-expression node is not an element node, the string value of the target-expression node is replaced by the string value of the text node that is returned by the source-expression. If the source-expression does not return a text node, the string value of the target-expression node is replaced by a zero-length string.

    If the target-expression node is a comment node, and if the string value of the text node that is returned by source-expression contains two adjacent hyphens or ends with a hyphen, an error is returned.

    If the target-expression node is a processing instruction node, and if the string value of the text node that is returned by source-expression contains the substring "?>", an error is returned.

The following updates are generated when the value of keywords are not specified:
  • source-expression nodes replace the target-expression node. The parent node of the target-expression node becomes the parent of each of the source-expression nodes. The source-expression nodes occupy the position in the node hierarchy that is occupied by the target-expression node.
  • The target-expression node, and all of its attributes and descendants are detached from the node sequence.
  • If target-expression has an attribute node, and an attribute node in the replacement sequence has a qualified name (QName) with an implied namespace binding, the namespaces property of the parent node of the target-expression node is modified to include a namespace binding for any attribute namespace prefixes that do not already have bindings. If the implied namespace binding conflicts with a namespace binding in the namespaces property of the parent node of the target-expression node, an error is returned.

If replacement of nodes results in adjacent text nodes with the same parent, the adjacent text nodes are merged into a single text node. The string value of the resulting text node is the concatenation of the string values of the adjacent text nodes, with no spaces added between string values.

If the result of the replace violates any constraint of the XPath 2. 0 and XQuery 1.0 data model, an error is returned.

Example

Suppose that a purchaseOrder document 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>

The following SQL UPDATE statement uses a basic updating expression to replace the value of the street node in the billTo node of the purchaseOrder document with a new value.

Start of change
UPDATE PURCHASEORDER
 SET PORDER = XMLMODIFY(
 'declare namespace ipo="http://www.example.com/IPO";
 replace value of node /ipo:purchaseOrder/billTo/street
 with "505 First Street"')
End of change

The result of the statement is:

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