The XQuery Update Facility 1.0 Recommendation is supported by Oracle XML DB starting with Oracle Database 12c Release 1 (12.1.0.1). Prior to this release, to update XML data your queries necessarily used Oracle-specific SQL functions: appendChildXML, deleteXML, insertChildXML, insertchildXMLafter, insertChildXMLbefore, insertXMLafter, insertXMLbefore, and updateXML.

These functions are covered in detail in the other sections of this appendix.

If you have legacy code that uses these functions, Oracle recommends that you migrate that code to use XQuery Update. This section provides information about which XQuery Update constructs you can use to replace the use of the Oracle-specific XML updating functions in queries.

Table C-1 provides a mapping from typical queries that use Oracle-specific updating SQL functions to queries that use XQuery Update.

There is no Oracle-specific equivalent for the XQuery Update constructs rename and insert as first into.

Note too that if the target XPath expression matches more than one node then the Oracle updating functions act on all such nodes, whereas the XQuery Update functions raise an error in this case. To act on multiple nodes using XQuery Update you need to use explicit iteration (that is, a for expression).

-- Insert a node as the last child of a node.
UPDATE warehouses SET warehouse_spec =
  appendChildXML(
    warehouse_spec,
    '/Warehouse/Parking',
    XMLType('<Spaces>250</Spaces>'));

-- Insert a node as the last child of a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node 
            <Spaces>250</Spaces>
            as last into $tmp/Warehouse/Parking
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  deleteXML(value(po), '/Warehouse/VClearance');

-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify delete node
            $tmp/Warehouse/VClearance return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert a node as a child of a node.
UPDATE warehouses SET warehouse_spec =
  insertChildXML(
    warehouse_spec,
    '/Warehouse/Parking',
    'Spaces',
    XMLType('<Spaces>300</Spaces>');

-- Insert a node as a child of a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node
            <Spaces>300</Spaces>
            into $tmp/Warehouse/Parking
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert a node before a node.
UPDATE warehouses SET warehouse_spec = 
  insertXMLbefore(
    warehouse_spec, 
    '/Warehouse/RailAccess', 
    XMLType('<SkyAccess>N</SkyAccess>');

-- Insert a node before a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node
            <SkyAccess>N</SkyAccess>
            before $tmp/Warehouse/RailAccess 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert a node after a node.
UPDATE warehouses SET warehouse_spec = 
  insertXMLafter(
    warehouse_spec,
    '/Warehouse/RailAccess', 
    XMLType('<SkyAccess>N</SkyAccess>');

-- Insert a node after a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node 
            <SkyAccess>N</SkyAccess>
            after $tmp/Warehouse/RailAccess 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Replace the text value of a set of nodes.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,  
            '/Warehouse/Building/text()',
            'Owned');

-- Replace the text value of a set of nodes.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify
            (for $i in $tmp/Warehouse/Building/text()  
             return replace value of node $i
                    with ''Owned'') 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert a child under each node in a collection.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec = 
  insertChildXML(
    warehouse_spec, 
    '/Warehouse/Building',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));

-- Insert a child under each node in a collection.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify
            (for $i in $tmp/Warehouse/Building
             return insert node       
             <Owner>LesserCo</Owner>
             into $i)
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert as child before the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec = 
  insertChildXMLbefore(
    warehouse_spec, 
    '/Warehouse/Building[1]',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));

-- Insert as child before the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     (for $i in $tmp/Warehouse/Building[1]/Owner
      return insert node <Owner>LesserCo</Owner>
             before $i)
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Insert as child after the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  insertChildXMLafter(
    warehouse_spec, 
    '/Warehouse/Building[1]',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));

-- Insert as child after the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     (for $i in $tmp/Warehouse/Building[1]/Owner
      return insert node <Owner>LesserCo</Owner>
             after $i)
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Delete a node.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,
            '/Warehouse/Docks',
            NULL);

-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify delete node 
            $tmp/Warehouse/Docks return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Replace with an empty node.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec, '/Warehouse/Docks', '' );

-- Replace with an empty node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := $p1 modify
            (for $j in $tmp/Warehouse/Docks
             return replace node $j with $p2)
            return $tmp'
           PASSING warehouse_spec "p1", '' AS "p2" 
           RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

-- Update multiple paths.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,  
            '/Warehouse/Docks/text()', '4',
            '/Warehouse/Area/text()', '3500');

-- Update multiple paths.
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     ((for $i in $tmp/Warehouse/Docks/text()  
       return replace value of node $i with 4),
      (for $i in $tmp/Warehouse/Area/text()  
       return replace value of node $i with 3500))
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;

Prior to their deprecation, you used Oracle SQL functions to update XML data incrementally — that is, to replace, insert, or delete XML data without replacing the entire surrounding XML document. This is also called partial updating.

These are the deprecated Oracle SQL functions for updating XML data:

• updateXML – Replace XML nodes of any kind.

• insertChildXML – Insert XML element or attribute nodes as children of a given element node.

• insertChildXMLbefore – Insert new collection elements immediately before a given collection element of the same type.

• insertChildXMLafter – Insert new collection elements immediately after a given collection element of the same type.

• insertXMLbefore – Insert XML nodes of any kind immediately before a given node (other than an attribute node).

• insertXMLafter – Insert XML nodes of any kind immediately after a given node (other than an attribute node).

• appendChildXML – Insert XML nodes of any kind as the last child nodes of a given element node.

• deleteXML – Delete XML nodes of any kind.

Functions insertChildXML, insertChildXMLbefore, insertChildXMLafter, insertXMLbefore, insertXMLafter, and appendChildXML are for inserting XML data. Function deleteXML deletes XML data. Function updateXML replaces XML data.

In particular, do not use function updateXML to insert or delete XML data by replacing a parent node in its entirety. That works, but it is less efficient than using one of the other functions, which perform more localized updates.

These Oracle SQL functions do not, by themselves, change database data – they are all pure functions, without side effect. Each applies an XPath-expression argument to input XML data and returns a modified copy of the input XML data. You can then use that result with SQL DML operator UPDATE to modify database data. This is no different from the way you use SQL function upper to convert database data to uppercase: you must use a SQL DML operator such as UPDATE to change the stored data.

Each of these functions can be used on XML documents that are either schema-based or non-schema-based. For XML schema-based data, these Oracle SQL functions perform partial validation on the result, and, where appropriate, argument values are also checked for compatibility with the XML schema.

• Insertion of XML Elements Using Deprecated Oracle SQL Functions
  There are several deprecated Oracle SQL functions for inserting XML nodes into (a copy of) existing XML data. Each can insert nodes at multiple locations that are referenced by an XPath expression. They differ in the placement of the new nodes and how the target XML data is referenced.

Deprecated Oracle SQL function updateXML replaces XML nodes of any kind. The XML document that is the target of the update can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function updateXML has the following parameters (in order):

• target-data (XMLType) – The XML data containing the target node to replace.

• One or more pairs of xpath and replacement parameters:

  • xpath (VARCHAR2) – An XPath 1.0 expression that locates the nodes within target-data to replace. Each targeted node is replaced by replacement. These can be nodes of any kind. If xpath matches an empty sequence of nodes then no replacement is done, and target-data is returned unchanged (and no error is raised).

  • replacement (XMLType or VARCHAR2) – The XML data that replaces the data targeted by xpath. The data type of replacement must correspond to the data to be replaced. If xpath targets an element node for replacement, then the data type must be XMLType. If xpath targets an attribute node or a text node, then it must be VARCHAR2. For an attribute node, replacement is only the replacement value of the attribute (for example, 23), not the complete attribute node including the name (for example, my_attribute="23").

• namespace (VARCHAR2, optional) – The XML namespace for parameter xpath.

Deprecated Oracle SQL function updateXML can be used to replace existing elements, attributes, and other nodes with new values. It is not an efficient way to insert new nodes or delete existing ones. You can perform insertions and deletions with updateXML only by using it to replace the entire node that is the parent of the node to be inserted or deleted.

Function updateXML updates only the transient XML instance in memory. Use a SQL UPDATE statement to update data stored in tables.

Figure C-1 illustrates the syntax.

Figure C-1 UPDATEXML Syntax

Description of "Figure C-1 UPDATEXML Syntax"

Example C-1 uses updateXML on the right side of an UPDATE statement to update the XML document in a table instead of creating a new document. The entire document is updated, not just the part that is selected.

Example C-2 updates multiple nodes using Oracle SQL function updateXML.

Example C-3 uses SQL function updateXML to update selected nodes within a collection.

SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]' PASSING po.OBJECT_VALUE AS "p"
                                                     RETURNING CONTENT) action
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
ACTION
--------------------------------
<Action>
  <User>SVOLLMAN</User>
</Action>

UPDATE purchaseorder po
  SET po.OBJECT_VALUE = updateXML(po.OBJECT_VALUE, 
                                  '/PurchaseOrder/Actions/Action[1]/User/text()',
                                  'SKING')
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]' PASSING po.OBJECT_VALUE AS "p"
                                                     RETURNING CONTENT) action
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
ACTION
---------------------------------
<Action>
  <User>SKING</User>
</Action>

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) name,
       XMLQuery('$p/PurchaseOrder/LineItems'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>

UPDATE purchaseorder
  SET OBJECT_VALUE = updateXML(OBJECT_VALUE,
                               '/PurchaseOrder/Requestor/text()','Stephen G. King',
                               '/PurchaseOrder/LineItems/LineItem[1]/Part/@Id','786936150421',
                               '/PurchaseOrder/LineItems/LineItem[1]/Description/text()','The Rock',
                               '/PurchaseOrder/LineItems/LineItem[3]',
                               XMLType('<LineItem ItemNumber="99">
                                          <Description>Dead Ringers</Description>
                                          <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
                                        </LineItem>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) name,
       XMLQuery('$p/PurchaseOrder/LineItems'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------
Stephen G. King  <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>The Rock</Description>
                     <Part Id="786936150421" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="99">
                     <Description>Dead Ringers</Description>
                     <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                 </LineItems>

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) name,
       XMLQuery('$p/PurchaseOrder/LineItems'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
NAME             LINEITEMS
---------------- ----------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      updateXML(OBJECT_VALUE,
                '/PurchaseOrder/Requestor/text()','Stephen G. King',
                '/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity',
                25,
                '/PurchaseOrder/LineItems/LineItem[Description/text() =
                                                   "The Unbearable Lightness Of Being"]',
                XMLType('<LineItem ItemNumber="99">
                           <Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
                           <Description>The Rock</Description>
                         </LineItem>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) name,
       XMLQuery('$p/PurchaseOrder/LineItems'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 
NAME             LINEITEMS
---------------- -------------------------------------------------------------
Stephen G. King  <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="25"/>
                   </LineItem>
                   <LineItem ItemNumber="99">
                     <Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
                     <Description>The Rock</Description>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>

• Deprecated Oracle SQL Function UPDATEXML and NULL Values
  Several considerations apply to using deprecated Oracle SQL function updateXML with NULL values.
• Update of the Same XML Node More Than Once Using UPDATEXML (Deprecated)
  You can update the same XML node more than once in an updateXML expression. The order of updates is determined by the order of the XPath expressions, in left-to-right order. Each successive update works on the result of the previous one.
• Guidelines for DOM Fidelity When Using UPDATEXML (Deprecated)
  Guidelines are presented for DOM fidelity when using Oracle SQL function updateXML.

In most cases, the deprecated Oracle SQL functions that modify XML data materialize a copy of the entire input XML document in memory, then update the copy. However, functions updateXML, insertChildXML, insertChildXMLbefore, insertChildXMLafter, and deleteXML — that is, all except insertXMLbefore, insertXMLafter, and appendChildXML — are optimized for XMLType UPDATE operations.

For object-relational storage, if particular conditions are met, then the function call can be rewritten to update the object-relational columns directly with the values. For binary XML storage, data preceding the targeted update is not modified, and, if SecureFiles LOBs are used (the default behavior), then sliding inserts are used to update only the portions of the data that need changing.

As an example with object-relational storage, the XPath argument to updateXML in Example C-6 is processed by Oracle XML DB and rewritten into equivalent object-relational SQL code, as illustrated in Example C-7.

SET LONG 2000

--

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/User'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
       AS VARCHAR2(30))
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
  PASSING po.OBJECT_VALUE AS "p");
 
XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
SBELL

UPDATE purchaseorder
  SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/User/text()', 'SVOLLMAN')
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
  PASSING OBJECT_VALUE AS "p");

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/User'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
       AS VARCHAR2(30))
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
  PASSING po.OBJECT_VALUE AS "p");
 
XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
SVOLLMAN

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/User'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30))
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
SBELL
 
UPDATE purchaseorder p
   SET p."XMLDATA"."USERID" = 'SVOLLMAN'
   WHERE p."XMLDATA"."REFERENCE" = 'SBELL-2002100912333601PDT';

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/User'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30))
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");
 

XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
SVOLLMAN

You can use the deprecated Oracle SQL functions that modify XML data (updateXML, insertChildXML, insertChildXMLbefore, insertChildXMLafter, insertXMLbefore, insertXMLafter, appendChildXML, and deleteXML) to create new views of XML data.

CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
  SELECT updateXML(OBJECT_VALUE,
                   '/PurchaseOrder/Actions', NULL,
                   '/PurchaseOrder/ShippingInstructions', NULL,
                   '/PurchaseOrder/LineItems', NULL) AS XML
  FROM purchaseorder p;

SELECT OBJECT_VALUE FROM purchaseorder_summary
  WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
                  PASSING OBJECT_VALUE AS "p");
 
OBJECT_VALUE
---------------------------------------------------------------------------
<PurchaseOrder
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation=
      "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>DAUSTIN-20021009123335811PDT</Reference>
  <Actions/>
  <Reject/>
  <Requestor>David L. Austin</Requestor>
  <User>DAUSTIN</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions/>
  <SpecialInstructions>Courier</SpecialInstructions>
  <LineItems/>
</PurchaseOrder>

Deprecated Oracle SQL function insertChildXML inserts new children (one or more elements of the same type or a single attribute) under parent XML elements. The XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function insertChildXML has the following parameters (in order):

• target-data (XMLType) – The XML data containing the target parent element.

• parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data. The child-data is inserted under each parent element.

  If parent-xpath matches an empty sequence of element nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

• child-name (VARCHAR2) – The name of the child elements or attribute to insert. An attribute name is distinguished from an element name by having an at-sign (@) prefix as part of child-name, for example, @my_attribute versus my_element. (The at-sign is not part of the attribute name, but serves in the argument to indicate that child-name refers to an attribute.)

• child-data (XMLType or VARCHAR2) – The child XML data to insert:

  • If one or more elements are being inserted, then this is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same name (tag) as child-name (or else an error is raised).

  • If an attribute is being inserted, then this is of data type VARCHAR2, and it represents the (scalar) attribute value. If an attribute of the same name already exists at the insertion location, then an error is raised.

• namespace (VARCHAR2, optional) – The XML namespace for parameters parent-xpath and child-data.

XML data child-data is inserted as one or more child elements, or a single child attribute, under each of the parent elements located at parent-xpath.

In order of decreasing precedence, function insertChildXML has the following behavior for NULL arguments:

• If child-name is NULL, then an error is raised.

• If target-data or parent-xpath is NULL, then NULL is returned.

• If child-data is NULL, then:

  • If child-name names an element, then no insertion is done, and target-data is returned unchanged.

  • If child-name names an attribute, then an empty attribute value is inserted, for example, my_attribute = "".

Figure C-2 shows the syntax.

Figure C-2 INSERTCHILDXML Syntax

Description of "Figure C-2 INSERTCHILDXML Syntax"

If target-data is XML schema-based, then the schema is consulted to determine the insertion positions. For example, if the schema constrains child elements named child-name to be the first child elements of a parent-xpath, then the insertion takes this into account. Similarly, if the child-name or child-data argument is inappropriate for an associated schema, then an error is raised.

If the parent element does not yet have a child corresponding in name and kind to child-name (and if such a child is permitted by the associated XML schema, if any), then child-data is inserted as new child elements, or a new attribute value, named child-name.

If the parent element already has a child attribute named child-name (without the at-sign), then an error is raised. If the parent element already has a child element named child-name (and if more than one child element is permitted by the associated XML schema, if any), then child-data is inserted so that its elements become child elements named child-name, but their positions in the sequence of children are unpredictable.

If you need to insert elements into an existing, non-empty collection of child elements, and the order is important to you, then use SQL/XML function appendChildXML or insertXMLbefore.

Example C-9 shows how to use a SQL UPDATE statement and Oracle SQL function insertChildXML to insert a new LineItem element as a child of element LineItems.

If the XML data to be updated is XML schema-based and it refers to a namespace, then the data to be inserted must also refer to the same namespace. Otherwise, an error is raised because the inserted data does not conform to the XML schema.

Example C-10 is the same as Example C-9, except that the LineItem element to be inserted refers to a namespace. This assumes that the relevant XML schema requires a namespace for this element.

This use of namespaces is different from the use of a namespace argument to function insertChildXML. Namespaces supplied in that optional argument apply only to the XPath argument, not to the content to be inserted.

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'
---------------------------------------------------------------

1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        insertChildXML(OBJECT_VALUE, 
                       '/PurchaseOrder/LineItems', 
                       'LineItem', 
                       XMLType('<LineItem ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" 
                                        UnitPrice="22.95" 
                                        Quantity="1"/>
                                </LineItem>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'
---------------------------------------------------------------
<LineItem ItemNumber="222">
  <Description>The Harder They Come</Description>
  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>

1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        insertChildXML(OBJECT_VALUE, 
                       '/PurchaseOrder/LineItems', 
                       'LineItem', 
                       XMLType('<LineItem xmlns="films.xsd" ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" 
                                        UnitPrice="22.95" 
                                        Quantity="1"/>
                                </LineItem>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING OBJECT_VALUE AS "p");

Deprecated Oracle SQL function insertChildXMLbefore inserts one or more collection elements as children of target parent elements. The insertion for each target occurs immediately before a specified existing collection element. The existing XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function insertChildXMLbefore has the following parameters (in order):

• target-data (XMLType) – The XML data that is the target of the insertion.

• parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data. The child-data is inserted under each parent element.

  If parent-xpath matches an empty sequence of element nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

• child-xpath (VARCHAR2) – A relative XPath 1.0 expression that locates the existing child that will become the successor of the inserted child-data. It must name a child element of the element indicated by parent-xpath, and it can include a predicate.

• child-data (XMLType) – The child element XML data to insert. This is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same data type as the element indicated by child-xpath (or else an error is raised).

• namespace (optional, VARCHAR2) – The namespace for parameters parent-xpath, child-xpath, and child-data.

XML data child-data is inserted as one or more child elements under each of the parent elements located at parent-xpath.

In order of decreasing precedence, function insertChildXMLbefore has the following behavior for NULL arguments:

• If child-xpath is NULL, then an error is raised.

• If target-data or parent-xpath is NULL, then NULL is returned.

• If child-data is NULL, then no insertion is done, and target-data is returned unchanged.

Figure C-3 shows the syntax.

Figure C-3 INSERTCHILDXMLBEFORE Syntax

Description of "Figure C-3 INSERTCHILDXMLBEFORE Syntax"

Deprecated Oracle SQL function insertChildXMLafter inserts one or more collection elements as children of target parent elements. The insertion for each target occurs immediately after a specified existing collection element. The existing XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function insertChildXMLafter has the following parameters (in order):

• target-data (XMLType) – The XML data that is the target of the insertion.

• parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data. The child-data is inserted under each parent element.

  If parent-xpath matches an empty sequence of element nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

• child-xpath (VARCHAR2) – A relative XPath 1.0 expression that locates the existing child that will become the predecessor of the inserted child-data. It must name a child element of the element indicated by parent-xpath, and it can include a predicate.

• child-data (XMLType) – The child element XML data to insert. This is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same data type as the element indicated by child-xpath (or else an error is raised).

• namespace (optional, VARCHAR2) – The namespace for parameters parent-xpath, child-xpath, and child-data.

XML data child-data is inserted as one or more child elements under each of the parent elements located at parent-xpath.

In order of decreasing precedence, function insertChildXMLafter has the following behavior for NULL arguments:

• If child-xpath is NULL, then an error is raised.

• If target-data or parent-xpath is NULL, then NULL is returned.

• If child-data is NULL, then no insertion is done, and target-data is returned unchanged.

Figure C-4 shows the syntax.

Figure C-4 INSERTCHILDXMLAFTER Syntax

Description of "Figure C-4 INSERTCHILDXMLAFTER Syntax"

Deprecated Oracle SQL function insertXMLbefore inserts one or more nodes of any kind immediately before a target node that is not an attribute node. The XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function insertXMLbefore has the following parameters (in order):

• target-data (XMLType) – The XML data that is the target of the insertion.

• successor-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data of any kind except attribute nodes. XML-data is inserted immediately before each of these nodes. Thus, the nodes in XML-data become preceding siblings of each of the successor-xpath nodes.

  If successor-xpath matches an empty sequence of nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If successor-xpath does not match a sequence of nodes that are not attribute nodes, then an error is raised.

• XML-data (XMLType) – The XML data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

• namespace (optional, VARCHAR2) – The namespace for parameter successor-xpath.

The XML-data nodes are inserted immediately before each of the non-attribute nodes located at successor-xpath.

Function insertXMLbefore has the following behavior for NULL arguments:

• If target-data or parent-xpath is NULL, then NULL is returned.

• Otherwise, if child-data is NULL, then no insertion is done, and target-data is returned unchanged.

Figure C-5 shows the syntax.

Figure C-5 INSERTXMLBEFORE Syntax

Description of "Figure C-5 INSERTXMLBEFORE Syntax"

Example C-11 uses deprecated Oracle SQL function insertXMLbefore to insert a LineItem element before the first LineItem element.

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[1]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                   PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[1]'PASSINGPO.OBJECT_
------------------------------------------------------------------
<LineItem ItemNumber="1">
  <Description>Salesman</Description>
  <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      insertXMLbefore(OBJECT_VALUE, 
                      '/PurchaseOrder/LineItems/LineItem[1]', 
                      XMLType('<LineItem ItemNumber="314">
                                 <Description>Brazil</Description>
                                 <Part Id="314159265359" 
                                       UnitPrice="69.95" 
                                       Quantity="2"/>
                               </LineItem>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[position() <= 2]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                   PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[POSITION()<=2]'PASSINGPO.OBJECT_
------------------------------------------------------------------------------
<LineItem ItemNumber="314">
  <Description>Brazil</Description>
  <Part Id="314159265359" UnitPrice="69.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="1">
  <Description>Salesman</Description>
  <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>

Deprecated Oracle SQL function insertXMLafter inserts one or more nodes of any kind immediately after a target node that is not an attribute node. The XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

Function insertXMLafter is thus similar to insertXMLbefore, but it inserts after, not before, the target node.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function insertXMLafter has the following parameters (in order):

• target-data (XMLType) – The XML data that is the target of the insertion.

• successor-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data of any kind except attribute nodes. XML-data is inserted immediately after each of these nodes. Thus, the nodes in XML-data become succeeding siblings of each of the successor-xpath nodes.

  If successor-xpath matches an empty sequence of nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If successor-xpath does not match a sequence of nodes that are not attribute nodes, then an error is raised.

• XML-data (XMLType) – The XML data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

• namespace (optional, VARCHAR2) – The namespace for parameter successor-xpath.

The XML-data nodes are inserted immediately after each of the non-attribute nodes located at successor-xpath.

Function insertXMLafter has the following behavior for NULL arguments:

• If target-data or parent-xpath is NULL, then NULL is returned.

• Otherwise, if child-data is NULL, then no insertion is done, and target-data is returned unchanged.

Figure C-6 shows the syntax.

Figure C-6 INSERTXMLAFTER Syntax

Description of "Figure C-6 INSERTXMLAFTER Syntax"

Deprecated Oracle SQL function appendChildXML inserts one or more nodes of any kind as the last children of a given element node. The XML document that is the target of the insertion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function appendChildXML has the following parameters (in order):

• target-data (XMLType)– The XML data containing the target parent element.

• parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more element nodes in target-data that are the targets of the insertion operation. The child-data is inserted as the last child or children of each of these parent elements.

  If parent-xpath matches an empty sequence of element nodes, then no insertion is done, and target-data is returned unchanged (no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

• child-data (XMLType) – Child data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

• namespace (optional, VARCHAR2) – The namespace for parameter parent-xpath.

XML data child-data is inserted as the last child or children of each of the element nodes indicated by parent-xpath.

Function appendChildXML has the following behavior for NULL arguments:

• If target-data or parent-xpath is NULL, then NULL is returned.

• Otherwise, if child-data is NULL, then no insertion is done, and target-data is returned unchanged.

Figure C-7 shows the syntax.

Figure C-7 APPENDCHILDXML Syntax

Description of "Figure C-7 APPENDCHILDXML Syntax"

Example C-12 uses deprecated Oracle SQL function appendChildXML to insert a Date element as the last child of an Action element.

SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/ACTIONS/ACTION[1]'PASSINGPO.OBJECT_VALUE
-------------------------------------------------------------------
<Action>
  <User>KPARTNER</User>
</Action>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      appendChildXML(OBJECT_VALUE, 
                     'PurchaseOrder/Actions/Action[1]', 
                     XMLType('<Date>2002-11-04</Date>'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/ACTIONS/ACTION[1]'PASSINGPO.OBJECT_VALUE
-------------------------------------------------------------------
<Action>
  <User>KPARTNER</User>
  <Date>2002-11-04</Date>
</Action>

Deprecated Oracle SQL function deleteXML deletes XML nodes of any kind. The XML document that is the target of the deletion can be XML Schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned. The original data is unaffected. You can then use the returned data with SQL operation UPDATE to modify database data.

Function deleteXML has the following parameters (in order):

• target-data (XMLType) – The XML data containing the target nodes (to be deleted).

• xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data that are the targets of the deletion operation. Each of these nodes is deleted.

  If xpath matches an empty sequence of nodes, then no deletion is done, and target-data is returned unchanged (no error is raised). If xpath matches the top-level element node, then an error is raised.

• namespace (optional, VARCHAR2) – The namespace for parameter xpath.

The XML nodes located at xpath are deleted from target-data. Function deleteXML returns NULL if target-data or xpath is NULL.

Figure C-8 shows the syntax.

Figure C-8 DELETEXML Syntax

Description of "Figure C-8 DELETEXML Syntax"

Example C-13 uses deprecated Oracle SQL function deleteXML to delete the LineItem element whose ItemNumber attribute has value 222.

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'PASSINGPO
------------------------------------------------------------------------
<LineItem ItemNumber="222">
  <Description>The Harder They Come</Description>
  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      deleteXML(OBJECT_VALUE, 
                '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
                PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'PASSINGPO
------------------------------------------------------------------------
 
1 row selected.