DB2 10.5 for Linux, UNIX, and Windows

Insert expression

An insert expression inserts copies of one or more nodes into a designated position in a node sequence.

Syntax


>>-do insert--SourceExpression--+-before--------+--TargetExpression-><
                                +-after---------+                     
                                +-as first into-+                     
                                +-as last into--+                     
                                '-into----------'

do insert

The keywords that begin an insert expression.

SourceExpression

An XQuery expression that is not an updating expression. If the expression includes a top-level comma operator, then the expression must be enclosed in parentheses. The expression is evaluated as though it were an enclosed expression in an element constructor. The result of the SourceExpression is a sequence of zero or more nodes to be inserted, called the insertion sequence. If the insertion sequence contains a document node, the document node is replaced in the insertion sequence by its children.

If the insertion sequence contains attribute nodes that appear first in the sequence, the attributes are added to the TargetExpression node or to its parent, depending on the keyword specified. If the insertion sequence contains an attribute node following a node that is not an attribute node, DB2® XQuery returns an error.

before

Keyword that specifies the SourceExpression nodes become the preceding siblings of the TargetExpression node.

The SourceExpression nodes are inserted immediately before the TargetExpression node. If multiple nodes are inserted before the TargetExpression, they are inserted in nondeterministic order, but the set of inserted nodes appear immediately before the TargetExpression. If the insertion sequence contains attribute nodes that appear first in the sequence, the attribute nodes become attributes of the parent of the target node.

after

Keyword that specifies the SourceExpression nodes become the following siblings of the TargetExpression node.

The SourceExpression nodes are inserted immediately after the TargetExpression node. If multiple nodes are inserted after the TargetExpression, they are inserted in nondeterministic order, but the set of inserted nodes appear immediately after the TargetExpression. If the insertion sequence contains attribute nodes that appear first in the sequence, the attribute nodes become attributes of the parent of the target node.

as first into

Keywords that specify the SourceExpression nodes become the first children of the TargetExpression node.

If multiple nodes are inserted as the first children of the TargetExpression node, they are inserted in nondeterministic order, but the set of inserted nodes appear as the first children of the TargetExpression. If the insertion sequence contains attribute nodes that appear first in the sequence, the attribute nodes become attributes of the target node.

as last into

Keywords that specify the SourceExpression nodes become the last children of the TargetExpression node.

If multiple nodes are inserted as the last children of the TargetExpression node, they are inserted in nondeterministic order, but the set of inserted nodes appear as the last children of the TargetExpression node. If the insertion sequence contains attribute nodes that appear first in the sequence, the attribute nodes become attributes of the target node.

into

Keyword that specifies the SourceExpression nodes become the children of the TargetExpression node in a nondeterministic order.

The SourceExpression nodes are inserted as children of the TargetExpression node in nondeterministic positions. If the insertion sequence contains attribute nodes that appear first in the sequence, the attribute nodes become attributes of the target node.

TargetExpression

An XQuery expression that is not an updating expression. If the expression includes a top-level comma operator, then the expression must be enclosed in parentheses. Based on the keywords specified before the TargetExpression, the following rules apply:

If before or after is specified, the result of TargetExpression must be an element, text, processing instruction, or comment node whose parent property is not empty. If the parent of the TargetExpression node is a document node and before or after is specified, the insertion sequence cannot contain an attribute node.
If into, as first into, or as last into is specified, the result of TargetExpression must be a single element node or a single document node.
If into is specified and TargetExpression is a document node, the insertion sequence cannot contain an attribute node.

Examples

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 copy clause of the transform expression creates a copy of the XML document from column INFO. The insert expression inserts the billto element and all its children after the last phone element :

xquery 
   transform
   copy $mycust := db2-fn:sqlquery('select info from customer where cid = 1004') 
   modify
     do insert
       <billto country="Canada">
          <street>4441 Wagner</street>
          <city>Aurora</city>
          <prov-state>Ontario</prov-state>
          <pcode-zip>N8X 7F8</pcode-zip>
       </billto>
    after $mycust/customerinfo/phone[last()]
   return $mycust

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

<customerinfo Cid="1004">
  <name>Matt Foreman</name>
  <addr country="Canada">
    <street>1596 Baseline</street>
    <city>Toronto</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>M3Z 5H9</pcode-zip>
  </addr>
  <phone type="work">905-555-4789</phone>
  <phone type="home">416-555-3376</phone>
  <billto country="Canada">
    <street>4441 Wagner</street>
    <city>Aurora</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N8X 7F8</pcode-zip>
  </billto>
  <assistant>
    <name>Gopher Runner</name>
    <phone type="home">416-555-3426</phone>
  </assistant>
</customerinfo>

The following example inserts the attribute extension with the value x2334 into the phone element where the phone's type attribute is work:

xquery
let $phoneext :=  attribute extension { "x2334" }
return
  transform
  copy $mycust := db2-fn:sqlquery('select info from customer where cid = 1004') 
  modify 
    do insert  $phoneext into $mycust/*:customerinfo/*:phone[@type="work"]
  return $mycust

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

<customerinfo Cid="1004">
  <name>Matt Foreman</name>
    <addr country="Canada">
      <street>1596 Baseline</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M3Z 5H9</pcode-zip>
    </addr>
  <phone extension="x2334" type="work">905-555-4789</phone>
  <phone type="home">416-555-3376</phone>
  <assistant>
    <name>Gopher Runner</name>
    <phone type="home">416-555-3426</phone>
  </assistant>
</customerinfo>