Insert expression

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

Syntax

insert nodes or insert node

The keywords that begin an insert expression. insert nodes or insert node is valid, regardless of the number of nodes that are to be inserted.

source-expression

An XQuery expression that is not an updating expression. The result of the source-expression is a sequence of zero or more nodes that are to be inserted. Those nodes are 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 an adjacent sequence of one or more atomic values, the atomic values are replaced in the insertion sequence with a new text node that contains the result of casting each atomic value to a string, and inserting a single space character between adjacent atomic values.

If the insertion sequence contains attribute nodes that appear first in the sequence, the attributes are added to the target-expression node or to its parent, depending on the keyword that is specified. If the insertion sequence contains an attribute node that follows a node that is not an attribute node, Db2 returns an error.

before

Keyword that specifies that the source-expression nodes become the preceding siblings of the target-expression node.

If multiple nodes are inserted before target-expression, the nodes remain adjacent and their order is preserved. If the insertion sequence contains attribute nodes, the attribute nodes become attributes of the parent of the target node.

after

Keyword that specifies that the source-expression nodes become the following siblings of the target-expression node.

If multiple nodes are inserted after target-expression, the nodes remain adjacent and their order is preserved. If the insertion sequence contains attribute nodes, the attribute nodes become attributes of the parent of the target node.

as first into

Keywords that specify that the source-expression nodes become the first children of the target-expression node.

The source-expression nodes are inserted as the first children of the target-expression node. If multiple nodes are inserted as the first children of the target-expression node, the nodes remain adjacent and their order is preserved. If the insertion sequence contains attribute nodes, the attribute nodes become attributes of the target node.

as last into

Keywords that specify that the source-expression nodes become the last children of the target-expression node.

The source-expression nodes are inserted as the last children of the target-expression node. If multiple nodes are inserted as the last children of the target-expression node, the nodes remain adjacent and their order is preserved. If the insertion sequence contains attribute nodes, the attribute nodes become attributes of the target node.

into

Has the same behavior as as last into.

target-expression

An XQuery expression that is not an updating expression. If the result of target-expression is an empty sequence, an error is returned. The following rules apply to target-expression:

If the before or after keyword is specified:
- The result of target-expression must be a single element, text, processing instruction, or comment node whose parent property is not empty.
- If the insertion sequence contains an attribute node, the parent of the target-expression node must be an element node.
- The parent property of the target-expression node cannot be empty.
- If an attribute node in the insertion 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 the parent of the target-expression node is a document node and before or after is specified, the insertion sequence cannot contain an attribute node.
If the into, as first into, or as last into keywords are specified:
- The result of target-expression must be a single element, text, processing instruction, or comment node whose parent property is not empty.
- If the insertion sequence contains an attribute node, the target-expression node cannot be a document node.
- The parent property of the target-expression node cannot be empty.
- If an attribute node in the insertion sequence has a qualified name (QName) with an implied namespace binding, the namespaces property 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 target-expression node, an error is returned.

If insertion 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 insert 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 insert a new item node as the first node under the items node in the purchaseOrder document.

UPDATE T1   
  SET X1 = XMLMODIFY(
  'declare namespace ipo="http://www.example.com/IPO";
   insert nodes $item as first
   into /ipo:purchaseOrder/items',
   XMLPARSE(DOCUMENT
   '<item partNum="747-BB">
    <productName>Ruby Ring</productName>
    <quantity>1</quantity>
    <USPrice>75.50</USPrice>
    <shipDate>2007-05-13</shipDate>
    </item>') as "item")

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>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="747-BB">
     <productName>Ruby Ring</productName>
     <quantify>1</quantity>
     <USPrice>75.50</USPrice>
     <shipDate>2007-05-13</shipDate>
    </item>
    <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>