C Deprecated Functions for Updating XML Data

Oracle SQL functions for updating XML data that are deprecated starting with Oracle Database 12c Release 1 (12.1.0.1) are described. Use XQuery Update instead to update XML data (see XQuery and Oracle XML DB).

C.1 Migration from Oracle Functions for Updating XML Data to XQuery Update

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.

Note that 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).

Table C-1 Migrating Oracle-Specific XML Updating Queries to XQuery Update

Original Expression Replacement 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;

C.2 Deprecated Oracle SQL Functions for Updating XML Data

Prior to their deprecation, you used the following 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.

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.

Note:

Oracle SQL functions and XMLType methods respect the W3C XPath recommendation, which states that if an XPath expression targets no nodes when applied to XML data, then an empty sequence must be returned. An error must not be raised in this case.

The specific semantics of an Oracle SQL function or XMLType method that applies an XPath expression to XML data determines what is returned. For example, SQL/XML function XMLQuery returns NULL if its XPath-expression argument targets no nodes, and the deprecated updating Oracle SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but the deprecated updating Oracle SQL functions can raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.

See Also:

"Partial and Full XML Schema Validation" for more information about partial validation against an XML schema

C.2.1 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.

  • Function appendChildXML appends nodes to the target elements. That is, for each target element, it inserts one or more nodes of any kind as the element's last children.

  • Function insertChildXML inserts new children (one or more elements of the same type or a single attribute) under target elements. The position of a new child element under its parent is not specified. If the target data is XML schema-based, then the schema can sometimes be used to determine the position. Otherwise, the position is arbitrary.

  • Function insertXMLbefore inserts one or more nodes of any kind immediately before a target node (which is not an attribute node).

    Function insertXMLafter inserts a node similarly, but after the target, not before.

  • Function insertChildXMLbefore is similar to insertChildXML, except that the inserted node must be an element (not an attribute), and you specify the position of the new element among its siblings. It is similar to insertXMLbefore, except that it inserts only collection elements, not arbitrary elements. The insertion position specifies a successor collection member. The actual element to be inserted must correspond to the element type for the collection.

    Function insertChildXMLafter inserts a node similarly, but after the target, not before.

Though the effect of insertChildXMLbefore (-after) is similar to that of insertXMLbefore (-after), the target location is expressed differently. For the former, the target is the parent of the new child. For the latter, the target is the succeeding (or preceding) sibling. This difference is reflected in the function names (Child).

For example, to insert a new LineItem element before the third LineItem element under element /PurchaseOrder/LineItems, you can use insertChildXMLbefore, specifying the target parent as /PurchaseOrder/LineItems and the succeeding sibling as LineItem[3]. Or you can use insertXMLbefore, specifying the target succeeding sibling as /PurchaseOrder/LineItems/LineItem[3]. If you use insertChildXML for the insertion, then you cannot specify the position of the new element in the collection — the resulting position is indeterminate.

Another difference among these functions is that all of them except insertXMLbefore, insertXMLafter, and appendChildXML —are optimized for SQL UPDATE operations on XMLType tables and columns that are stored object-relationally or as binary XML.

See Also:

"Optimization of Deprecated Oracle SQL Functions that Modify XML Data"

C.3 UPDATEXML Deprecated Oracle SQL Function

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

Example C-1 Updating XMLTYPE Using UPDATE and UPDATEXML (Deprecated)

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>

Example C-2 Updating Multiple Text Nodes and Attribute Values Using UPDATEXML (Deprecated)

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>

Example C-3 Updating Selected Nodes within a Collection Using UPDATEXML (Deprecated)

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>

C.3.1 Deprecated Oracle SQL Function UPDATEXML and NULL Values

Several considerations apply to using deprecated Oracle SQL function updateXML with NULL values.

  • If you update an XML element to NULL, the attributes and children of the element are removed, and the element becomes empty. The type and namespace properties of the element are retained. See Example C-4.

  • If you update an attribute value to NULL, the value appears as the empty string. See Example C-4.

  • If you update the text node of an element to NULL, the content (text) of the element is removed. The element itself remains, but it is empty. See Example C-5.

Example C-4 updates all of the following to NULL:

  • The Description element and the Quantity attribute of the LineItem element whose Part element has attribute Id value 715515009058.

  • The LineItem element whose Description element has the content (text) "The Unbearable Lightness Of Being".

Example C-5 updates the text node of a Part element whose Description attribute has value "A Night to Remember" to NULL. The XML data for this example corresponds to a different, revised purchase-order XML schema – see "Scenario for Copy-Based Evolution". In that XML schema, Description is an attribute of the Part element, not a sibling element.

See Also:

Example 3-26

Example C-4 NULL Updates with UPDATEXML (Deprecated) – Element and Attribute

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/LineItems/LineItem[Part/@Id="715515009058"]/Description', NULL,
             '/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity', NULL,
        '/PurchaseOrder/LineItems/LineItem[Description/text()=
                                           "The Unbearable Lightness Of Being"]', NULL)
  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
---------------- ----------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description/>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity=""/>
                   </LineItem>
                   <LineItem/>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>

Example C-5 NULL Updates with UPDATEXML (Deprecated) – Text Node

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(128)) part
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

PART
----
<Part Description="A Night to Remember" UnitCost="39.95">715515009058</Part>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        updateXML(OBJECT_VALUE, 
                  '/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]/text()', NULL)
  WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
                  PASSING OBJECT_VALUE AS "p");

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(128)) part
  FROM purchaseorder po
  WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

PART
----
<Part Description="A Night to Remember" UnitCost="39.95"/>

C.3.2 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.

For example, you can update both /EMP[EMPNO=217] and /EMP[EMPNAME="Jane"]/EMPNO, where the first XPath identifies the EMPNO node containing it as well.

C.3.3 Guidelines for DOM Fidelity When Using UPDATEXML (Deprecated)

Guidelines are presented for DOM fidelity when using Oracle SQL function updateXML.

  • When DOM fidelity is preserved —

    When you update an element to NULL, you make that element appear empty in its parent. When you update a text node inside an element to NULL, you remove the text node. When you update an attribute node to NULL, you make the attribute value the empty string.

    An example of an empty element is <myElem/>. An example of an attribute with an empty-string value is myAttr="".

  • When DOM fidelity is not preserved —

    When you update a complexType element to NULL, you make it appear empty in its parent. When you update a SQL-inlined simpleType element to NULL, you make it disappear from its parent. Updating a text node to NULL is the same as setting the parent simpleType element to NULL.

    Furthermore, text nodes can only appear inside simpleType elements when DOM fidelity is not preserved, since there is no positional descriptor with which to store mixed content. When you update an attribute node to NULL, you remove the attribute from the element.

  • How to tell whether DOM fidelity is preserved —

    You can determine whether or not DOM fidelity is preserved for particular parts of a given XMLType instance in a given XML schema by querying the XML schema metadata for attribute maintainDOM.

    See Also:

C.4 Optimization of Deprecated Oracle SQL Functions that Modify XML Data

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.

See Also:

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.

Note:

The use of XMLDATA for DML is shown here only as an illustration of internal Oracle XML DB behavior. Do not use XMLDATA yourself for DML operations. You can use XMLDATA directly only for DDL operations, never for DML operations.

More generally, in your code, do not rely on the current mapping between the XML Schema object model and the SQL object model. This Oracle XML DB implementation mapping might change in the future.

Example C-6 XPath Expressions in UPDATEXML Expression

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

Example C-7 Object Relational Equivalent of UPDATEXML Expression

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

C.5 Creating XML Views Using Deprecated Oracle SQL Functions that Modify XML Data

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.

Example C-8 creates a view of table purchaseorder using deprecated Oracle SQL function updateXML.

Example C-8 Creating a View Using UPDATEXML (Deprecated)

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>

C.6 INSERTCHILDXML Deprecated Oracle SQL Function

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

Note that 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.

Example C-9 Insertion into a Collection Using INSERTCHILDXML (Deprecated)

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.

Example C-10 Inserting an Element that Uses a Namespace

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");

C.7 INSERTCHILDXMLBEFORE Deprecated Oracle SQL Function

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 follows
Description of "Figure C-3 INSERTCHILDXMLBEFORE Syntax"

C.8 INSERTCHILDXMLAFTER Deprecated Oracle SQL Function

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 follows
Description of "Figure C-4 INSERTCHILDXMLAFTER Syntax"

C.9 INSERTXMLBEFORE Deprecated Oracle SQL Function

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

Note:

Queries that use Oracle SQL function insertXMLbefore are not optimized. For this reason, Oracle recommends that you use function insertChildXML, insertChildXMLbefore, or insertChildXMLafter instead. See "Performance Tuning for XQuery".

Example C-11 Insertion Before an Element Using INSERTXMLBEFORE (Deprecated)

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>

C.10 INSERTXMLAFTER Deprecated Oracle SQL Function

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 follows
Description of "Figure C-6 INSERTXMLAFTER Syntax"

Note:

Queries that use Oracle SQL function insertXMLafter are not optimized. For this reason, Oracle recommends that you use function insertChildXML, insertChildXMLbefore, or insertChildXMLafter instead. See "Performance Tuning for XQuery".

C.11 APPENDCHILDXML Deprecated Oracle SQL Function

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

Note:

Queries that use Oracle SQL function appendChildXML are not optimized. For this reason, Oracle recommends that you use function insertChildXML, insertChildXMLbefore, or insertChildXMLafter instead. See "Performance Tuning for XQuery".

Example C-12 Insertion as the Last Child Using APPENDCHILDXML (Deprecated)

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>

C.12 DELETEXML Deprecated Oracle SQL Function

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

Example C-13 Deletion of an Element Using DELETEXML (Deprecated)

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.