4 XMLType Operations

This chapter describes XMLType operations for XML applications (XML schema-based and non-schema-based). It includes guidelines for creating, manipulating, updating, and querying XMLType columns and tables.

This chapter contains these topics:

Selecting and Querying XML Data
Updating XML Data

See Also:

Chapter 3, "Using Oracle XML DB" for XMLType storage recommendations
Chapter 7, "XML Schema Storage and Query: Basic" for how to work with XML schema-based XMLType tables and columns

Selecting and Querying XML Data

You can query XML data from XMLType columns in the following ways:

Select XMLType columns using SQL, PL/SQL, or Java.
Use the XQuery language. See "Using XQuery with XMLType Data".
Query XMLType columns directly or using SQL/XML functions such as XMLQuery.
Use Oracle Text operators for full-text search. See Chapter 6, "Indexing XMLType Data" and Chapter 12, "Full-Text Search Over XML Data".

Searching XML Documents using XPath Expressions

The XPath language is a W3C Recommendation for navigating XML documents. It is a subset of the XQuery language, in the sense that an XPath expression is also an XQuery expression.

XPath models an XML document as a tree of nodes. It provides a rich set of operations that walk this tree and apply predicates and node-test functions. Applying an XPath expression to an XML document can result in a set of nodes. For example, the expression /PO/PONO selects all PONO child elements under the PO root element of the document.

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 updating SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but updating SQL functions can raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.

Table 4-1 lists some common constructs used in XPath.

Table 4-1 Common XPath Constructs

XPath Construct	Description
`/`	Denotes the root of the tree in an XPath expression. For example, `/PO` refers to the child of the root node whose name is `PO`.
`/`	Also used as a path separator to identify the children node of any given node. For example, `/PurchaseOrder/Reference` identifies the purchase-order name element `Reference`, a child of the root element.
`//`	Used to identify all descendants of the current node. For example, `PurchaseOrder//ShippingInstructions` matches any `ShippingInstructions` element under the `PurchaseOrder` element.
`*`	Used as a wildcard to match any child node. For example, `/PO/*/STREET` matches any street element that is a grandchild of the `PO` element.
`[ ]`	Used to denote predicate expressions. XPath supports a rich list of binary operators such as `or`, `and`, and `not`. For example, `/PO[PONO = 20 and PNAME = "PO_2"]/SHIPADDR` selects the shipping address element of all purchase orders whose purchase-order number is `20` and whose purchase-order name is `PO_2`. Brackets are also used to denote a position (index). For example, `/PO/PONO[2]` identifies the second purchase-order number element under the `PO` root element.
Functions	XPath and XQuery support a set of built-in functions such as `substring`, `round`, and `not`. In addition, these languages provide for extension functions through the use of namespaces. Oracle XQuery extension functions use the namespace prefix `ora`, for namespace `http://xmlns.oracle.com/xdb`. See "Oracle XQuery Extension Functions".

The XPath must identify a single node, or a set of element, text, or attribute nodes. The result of the XPath cannot be a Boolean expression.

You can select XMLType data using PL/SQL, C, or Java. You can also use XMLType method getNumberVal() to retrieve XML data as a NUMBER.

Querying XMLType Data using SQL/XML Functions XMLExists and XMLCast

You can query XMLType data and extract portions of it using SQL/XML standard functions XMLQuery, XMLTable, XMLExists, and XMLCast.

See Chapter 5, "Using XQuery with Oracle XML DB" for more information about functions XMLQuery and XMLTable. Functions XMLExists and XMLCast are described in this section.

XMLEXISTS SQL/XML Function

Figure 4-1 describes the syntax for SQL/XML standard function XMLExists. This function checks whether a given XQuery expression returns a non-empty XQuery sequence. If so, the function returns TRUE. Otherwise, it returns FALSE.

Figure 4-1 XMLExists Syntax

Description of "Figure 4-1 XMLExists Syntax"

XML_passing_clause ::=

Description of the illustration xml_passing_clause.gif

XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string. It can contain XQuery variables that you bind using the XQuery PASSING clause (XML_passing_clause in the syntax diagram). The predefined namespace prefixes recognized for SQL/XML function XMLQuery are also recognized in XQuery_string — see "Predefined Namespaces and Prefixes".
The XML_passing_clause is the keyword PASSING followed by one or more SQL expressions (expr) that each return an XMLType instance or an instance of a SQL scalar data type. All but possibly one of the expressions must each be followed by the keyword AS and an XQuery identifier. The result of evaluating each expr is bound to the corresponding identifier for the evaluation of XQuery_string. If there is an expr that is not followed by an AS clause, then the result of evaluating that expr is used as the context item for evaluating XQuery_string. Oracle XML DB supports only passing BY VALUE, not passing BY REFERENCE, so the clause BY VALUE is implicit and can be omitted.

If an XQuery expression such as /PurchaseOrder/Reference or /PurchaseOrder/Reference/text() targets a single node, then XMLExists returns true for that expression. If XMLExists is called with an XQuery expression that locates no nodes, then XMLExists returns false.

Function XMLExists can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.

Note:

Oracle XML DB limits the use of XMLExists to a SQL WHERE clause or CASE expression. If you need to use XMLExists in a SELECT list, then wrap it in a CASE expression:

CASE WHEN XMLExists(...) THEN 'TRUE' ELSE 'FALSE' END

Example 4-1 uses SQL/XML standard function XMLExists to select rows with SpecialInstructions set to Expedite.

Example 4-1 Finding a Node using SQL/XML Function XMLExists

SELECT OBJECT_VALUE
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
OBJECT_VALUE
--------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 
13 rows selected.

You can create function-based indexes using SQL/XML function XMLExists to speed up the execution. You can also create an XMLIndex index to help speed up arbitrary XQuery searching.

Note:

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function existsNode to do some of what can be done better using SQL/XML function XMLExists. Function existsNode is deprecated in Oracle Database 11g Release 2. The two functions differ in these important ways:

Function existsNode returns 0 or 1. Function XMLExists returns a Boolean value, TRUE or FALSE.
You can use existsNode in a query SELECT list. You cannot use XMLExists directly in a SELECT list, but you can use XMLExists within a CASE expression in a SELECT list.

See Also:

XMLCAST SQL/XML Function

Figure 4-2 describes the syntax for SQL/XML standard function XMLCast.

Figure 4-2 XMLCast Syntax

Description of "Figure 4-2 XMLCast Syntax"

SQL/XML standard function XMLCast casts its first argument to the scalar SQL data type specified by its second argument. The first argument is a SQL expression that is evaluated. Any of the following SQL data types can be used as the second argument:

NUMBER
VARCHAR2
CHAR
CLOB
BLOB
REF XMLTYPE
any SQL date or time data type

Note:

Unlike the SQL/XML standard, Oracle XML DB limits the use of XMLCast to cast XML to a SQL scalar data type. Oracle XML DB does not support casting XML to XML or from a scalar SQL type to XML.

The result of evaluating the first XMLCast argument is an XML value. It is converted to the target SQL data type by using the XQuery atomization process and then casting the XQuery atomic values to the target data type. If this conversion fails, then an error is raised. If conversion succeeds, the result returned is an instance of the target data type.

The query in Example 4-2 extracts the scalar value of node Reference.

Example 4-2 Extracting the Scalar Value of an XML Fragment using XMLCAST

SELECT XMLCast(XMLQuery('/PurchaseOrder/Reference' PASSING OBJECT_VALUE
                                                   RETURNING CONTENT)
               AS VARCHAR2(100)) "REFERENCE"
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
REFERENCE
----------------------------
AMCEWEN-20021009123336271PDT
SKING-20021009123336321PDT
AWALSH-20021009123337303PDT
JCHEN-20021009123337123PDT
AWALSH-20021009123336642PDT
SKING-20021009123336622PDT
SKING-20021009123336822PDT
AWALSH-20021009123336101PDT
WSMITH-20021009123336412PDT
AWALSH-20021009123337954PDT
SKING-20021009123338294PDT
WSMITH-20021009123338154PDT
TFOX-20021009123337463PDT
 
13 rows selected.

Note:

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function extractValue to do some of what can be done better using SQL/XML functions XMLQuery and XMLCast. Function extractValue is deprecated in Oracle Database 11g Release 2.
Function extractValue raises an error when its XPath expression argument matches multiple text nodes. XMLCast applied to an XMLQuery result returns the concatenation of the text nodes — it does not raise an error.

See Also:

Examples of Querying XML Data using SQL/XML Functions

The examples in this section illustrate ways you can use SQL to query XML data. Example 4-3 inserts two rows into table purchaseorder, then queries data in those rows using SQL/XML functions XMLCast, XMLQuery, and XMLExists.

Example 4-3 Querying XMLTYPE Data

INSERT INTO purchaseorder 
  VALUES (XMLType(bfilename('XMLDIR', 'SMCCAIN-2002091213000000PDT.xml'),
                  nls_charset_id('AL32UTF8')));
 
INSERT INTO purchaseorder
  VALUES (XMLType(bfilename('XMLDIR', 'VJONES-20020916140000000PDT.xml'),
                  nls_charset_id('AL32UTF8')));
 
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) reference,
       XMLCast(XMLQuery('$p/PurchaseOrder/*//User'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) userid,
       CASE
         WHEN XMLExists('$p/PurchaseOrder/Reject/Date'
                        PASSING po.OBJECT_VALUE AS "p")
           THEN 'Rejected'
           ELSE 'Accepted'
       END "STATUS",
       XMLCast(XMLQuery('$p//Date'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(12)) status_date
  FROM purchaseorder po
  WHERE XMLExists('$p//Date' PASSING po.OBJECT_VALUE AS "p")
  ORDER BY XMLCast(XMLQuery('$p//Date' PASSING po.OBJECT_VALUE AS "p"
                                       RETURNING CONTENT)
                   AS VARCHAR2(12));
 
REFERENCE                        USERID   STATUS   STATUS_DATE
-------------------------------- -------- -------- ------------
VJONES-20020916140000000PDT      SVOLLMAN Accepted 2002-10-11
SMCCAIN-2002091213000000PDT      SKING    Rejected 2002-10-12
 
2 rows selected.

Example 4-4 uses a PL/SQL cursor to query XML data. It uses a local XMLType instance to store transient data.

Example 4-4 Querying Transient XMLTYPE Data using a PL/SQL Cursor

DECLARE
  xNode      XMLType;
  vText      VARCHAR2(256);
  vReference VARCHAR2(32);
  CURSOR getPurchaseOrder(reference IN VARCHAR2) IS
           SELECT OBJECT_VALUE XML
             FROM purchaseorder
             WHERE XMLExists('$p/PurchaseOrder[Reference=$r]'
                             PASSING OBJECT_VALUE AS "p",
                                     reference    AS "r");
BEGIN
  vReference := 'EABEL-20021009123335791PDT';
  FOR c IN getPurchaseOrder(vReference) LOOP
    xNode := c.XML.extract('//Requestor');
    SELECT XMLSerialize(CONTENT
                        XMLQuery('//text()' PASSING xNode RETURNING CONTENT))
           INTO vText FROM DUAL;
    DBMS_OUTPUT.put_line('The Requestor for Reference '
                         || vReference || ' is '|| vText);
  END LOOP;
  vReference := 'PTUCKER-20021009123335430PDT';
  FOR c IN getPurchaseOrder(vReference) LOOP
    xNode := c.XML.extract('//LineItem[@ItemNumber="1"]/Description');
    SELECT XMLSerialize(CONTENT
                        XMLQuery('//text()' PASSING xNode RETURNING CONTENT))
           INTO vText FROM DUAL;
    DBMS_OUTPUT.put_line('The Description of LineItem[1] for Reference '
                         || vReference || ' is '|| vText);
  END LOOP;
END;
/
The Requestor for Reference EABEL-20021009123335791PDT is Ellen S. Abel
The Description of LineItem[1] for Reference PTUCKER-20021009123335430PDT is
 Picnic at
Hanging Rock
 
PL/SQL procedure successfully completed.

Example 4-5 uses SQL/XML function XMLTable to extract data from an XML purchase-order document, and then inserts that data into a relational table.

Example 4-5 Extracting XML Data using XMLTABLE, and Inserting It into a Database Table

CREATE TABLE purchaseorder_table (reference           VARCHAR2(28) PRIMARY KEY,
                                  requestor           VARCHAR2(48),
                                  actions             XMLType,
                                  userid              VARCHAR2(32),
                                  costcenter          VARCHAR2(3),
                                  shiptoname          VARCHAR2(48),
                                  address             VARCHAR2(512),
                                  phone               VARCHAR2(32),
                                  rejectedby          VARCHAR2(32),
                                  daterejected        DATE,
                                  comments            VARCHAR2(2048),
                                  specialinstructions VARCHAR2(2048));
 
CREATE TABLE purchaseorder_lineitem (reference,
                                     FOREIGN KEY ("REFERENCE")
                                       REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
                                     lineno      NUMBER(10), PRIMARY KEY ("REFERENCE", "LINENO"),
                                     upc         VARCHAR2(14),
                                     description VARCHAR2(128),
                                     quantity    NUMBER(10),
                                     unitprice   NUMBER(12,2));
 
INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
                                 phone, rejectedby, daterejected, comments, specialinstructions)
  SELECT t.reference, t.requestor, t.actions, t.userid, t.costcenter, t.shiptoname, t.address, 
          t.phone, t.rejectedby, t.daterejected, t.comments, t.specialinstructions
    FROM purchaseorder p,
         XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                  COLUMNS reference           VARCHAR2(28)   PATH 'Reference',
                          requestor           VARCHAR2(48)   PATH 'Requestor',
                          actions             XMLType        PATH 'Actions',
                          userid              VARCHAR2(32)   PATH 'User',
                          costcenter          VARCHAR2(3)    PATH 'CostCenter',
                          shiptoname          VARCHAR2(48)   PATH 'ShippingInstructions/name',
                          address             VARCHAR2(512)  PATH 'ShippingInstructions/address',
                          phone               VARCHAR2(32)   PATH 'ShippingInstructions/telephone',
                          rejectedby          VARCHAR2(32)   PATH 'Reject/User',
                          daterejected        DATE           PATH 'Reject/Date',
                          comments            VARCHAR2(2048) PATH 'Reject/Comments',
                          specialinstructions VARCHAR2(2048) PATH 'SpecialInstructions') t
    WHERE t.reference = 'EABEL-20021009123336251PDT';
 
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
  SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
    FROM purchaseorder p,
         XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                  COLUMNS reference VARCHAR2(28) PATH 'Reference',
                          lineitem XMLType PATH 'LineItems/LineItem') t,
         XMLTable('LineItem' PASSING t.lineitem
                  COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                          upc         VARCHAR2(14)  PATH 'Part/@Id',
                          description VARCHAR2(128) PATH 'Description',
                          quantity    NUMBER(10)    PATH 'Part/@Quantity',
                          unitprice   NUMBER(12,2)  PATH 'Part/@UnitPrice') li
    WHERE t.reference = 'EABEL-20021009123336251PDT';
 
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table;
 
REFERENCE                        USERID   SHIPTONAME                                       SPECIALINSTRUCTIONS
-------------------------------- -------- ------------------------------------------------ -------------------
EABEL-20021009123336251PDT       EABEL    Ellen S. Abel                                    Counter to Counter
 
SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem;
 
REFERENCE                            LINENO UPC            DESCRIPTION                          QUANTITY
-------------------------------- ---------- -------------- ---------------------------------- ----------
EABEL-20021009123336251PDT                1 37429125526    Samurai 2: Duel at Ichijoji Temple          3
EABEL-20021009123336251PDT                2 37429128220    The Red Shoes                               4
EABEL-20021009123336251PDT                3 715515009058   A Night to Remember                         1

Example 4-6 defines and uses a PL/SQL procedure to extract data from an XML purchase-order document and insert it into a relational table.

Example 4-6 Extracting XML Data and Inserting It into a Table using a PL/SQL Procedure

CREATE OR REPLACE PROCEDURE insertPurchaseOrder(purchaseorder XMLType) AS reference VARCHAR2(28);
BEGIN
  INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
                                   phone, rejectedby, daterejected, comments, specialinstructions)
    SELECT * FROM XMLTable('$p/PurchaseOrder' PASSING purchaseorder AS "p"
                           COLUMNS reference           VARCHAR2(28)   PATH 'Reference',
                                   requestor           VARCHAR2(48)   PATH 'Requestor',
                                   actions             XMLType        PATH 'Actions',
                                   userid              VARCHAR2(32)   PATH 'User',
                                   costcenter          VARCHAR2(3)    PATH 'CostCenter',
                                   shiptoname          VARCHAR2(48)   PATH 'ShippingInstructions/name',
                                   address             VARCHAR2(512)  PATH 'ShippingInstructions/address',
                                   phone               VARCHAR2(32)   PATH 'ShippingInstructions/telephone',
                                   rejectedby          VARCHAR2(32)   PATH 'Reject/User',
                                   daterejected        DATE           PATH 'Reject/Date',
                                   comments            VARCHAR2(2048) PATH 'Reject/Comments',
                                   specialinstructions VARCHAR2(2048) PATH 'SpecialInstructions');
 
  INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
    SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
    FROM XMLTable('$p/PurchaseOrder' PASSING purchaseorder AS "p"
                  COLUMNS reference VARCHAR2(28) PATH 'Reference',
                          lineitem XMLType PATH 'LineItems/LineItem') t,
         XMLTable('LineItem' PASSING t.lineitem
                  COLUMNS lineno NUMBER(10)    PATH '@ItemNumber',
                          upc VARCHAR2(14)  PATH 'Part/@Id',
                          description VARCHAR2(128) PATH 'Description',
                          quantity NUMBER(10)    PATH 'Part/@Quantity',
                          unitprice NUMBER(12,2)  PATH 'Part/@UnitPrice') li;
END;
CALL insertPurchaseOrder(XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8')));
 
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table;
 
REFERENCE                        USERID   SHIPTONAME                                       SPECIALINSTRUCTIONS
-------------------------------- -------- ------------------------------------------------ -------------------
SBELL-2002100912333601PDT        SBELL    Sarah J. Bell                                    Air Mail

 
SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem;
 
REFERENCE                 LINENO UPC          DESCRIPTION                        QUANTITY
------------------------- ------ ------------ ---------------------------------- --------
SBELL-2002100912333601PDT      1 715515009058 A Night to Remember                       2
SBELL-2002100912333601PDT      2 37429140222  The Unbearable Lightness Of Being         2
SBELL-2002100912333601PDT      3 715515011020 Sisters                                   4

Example 4-7 extracts the purchase-order name from XML element PurchaseOrder for customers with "ll" (double L) in their names and the word "Shores" in the shipping instructions. It uses Oracle XQuery extension function ora:contains to perform full-text search.

Example 4-7 Searching XML Data using SQL/XML Functions

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                        PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS VARCHAR2(128)) name,
       count(*)
  FROM purchaseorder po
  WHERE
    XMLExists(
      'declare namespace ora="http://xmlns.oracle.com/xdb"; (: :)
       $p/PurchaseOrder/ShippingInstructions[ora:contains(address/text(), "Shores") > 0]'
      PASSING po.OBJECT_VALUE AS "p")
    AND XMLCast(XMLQuery('$p/PurchaseOrder/Requestor/text()'
                         PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
                AS VARCHAR2(128))
        LIKE '%ll%'
  GROUP BY XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
                            PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
                   AS VARCHAR2(128));

NAME                   COUNT(*)
-------------------- ----------
Allan D. McEwen               9
Ellen S. Abel                 4
Sarah J. Bell                13
William M. Smith              7

Example 4-8 uses SQL/XML function XMLQuery to extract nodes identified by an XPath expression. The XMLType instance returned by XMLQuery can be a set of nodes, a singleton node, or a text value. Example 4-8 uses XMLType method isFragment() to determine whether the result is a fragment.

Example 4-8 Extracting Fragments from an XMLTYPE Instance using XMLQUERY

SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p"
                                                     RETURNING CONTENT)
               AS VARCHAR2(30)) reference,
       count(*)
  FROM purchaseorder po, XMLTable('$p//LineItem[Part/@Id="37429148327"]' PASSING OBJECT_VALUE AS "p")
  WHERE XMLQuery('$p/PurchaseOrder/LineItems/LineItem[Part/@Id="37429148327"]'
                 PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT).isFragment() = 1
  GROUP BY XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
                   AS VARCHAR2(30))
  ORDER BY XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
                   AS VARCHAR2(30));
 
REFERENCE                          COUNT(*)
-------------------------------- ----------
TFOX-20021009123337784PDT                 3

Note:

You cannot insert fragments into XMLType columns. You can use SQL function sys_XMLGen to convert a fragment into a well-formed document by adding an enclosing tag. See "SYS_XMLGEN Oracle SQL Function".

Updating XML Data

This section covers updating XML data, both transient data and data stored in tables. It describes the use of the following SQL functions:

updateXML
insertChildXML
insertChildXMLbefore
insertChildXMLafter
insertXMLbefore
insertXMLafter
appendChildXML
deleteXML

Updating an Entire XML Document

For unstructured storage (CLOB), an update effectively replaces the entire document. To update an entire XML document, use a SQL UPDATE statement. The right side of the UPDATE statement SET clause must be an XMLType instance. This can be created in any of the following ways:

Use SQL functions or XML constructors that return an XML instance.
Use the PL/SQL DOM APIs for XMLType that change and bind an existing XML instance.
Use the Java DOM API that changes and binds an existing XML instance.

Updates for non-schema-based XML documents stored as CLOB values (unstructured storage) always update the entire XML document. Updates for non-schema-based documents stored as binary XML can be made in a piecewise manner. See "Updating XML Schema-Based and Non-Schema-Based XML Documents".

Example 4-9 updates an XMLType instance using a SQL UPDATE statement.

Example 4-9 Updating XMLType Data using a SQL UPDATE Statement

SELECT t.reference, li.lineno, li.description
  FROM purchaseorder po,
       XMLTable('$p/PurchaseOrder' PASSING po.OBJECT_VALUE AS "p"
                COLUMNS reference VARCHAR2(28) PATH 'Reference',
                        lineitem  XMLType      PATH 'LineItems/LineItem') t,
       XMLTable('$l/LineItem' PASSING t.lineitem AS "l"
                COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                        description VARCHAR2(128) PATH 'Description') li
  WHERE t.reference = 'DAUSTIN-20021009123335811PDT' AND ROWNUM < 6;
 
REFERENCE                         LINENO DESCRIPTION
-------------------------------- ------- -----------------
DAUSTIN-20021009123335811PDT           1 Nights of Cabiria
DAUSTIN-20021009123335811PDT           2 For All Mankind
DAUSTIN-20021009123335811PDT           3 Dead Ringers
DAUSTIN-20021009123335811PDT           4 Hearts and Minds
DAUSTIN-20021009123335811PDT           5 Rushmore

UPDATE purchaseorder po
  SET po.OBJECT_VALUE = XMLType(bfilename('XMLDIR','NEW-DAUSTIN-20021009123335811PDT.xml'),
                                nls_charset_id('AL32UTF8'))
  WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
                  PASSING po.OBJECT_VALUE AS "p");

SELECT t.reference, li.lineno, li.description
  FROM purchaseorder po,
       XMLTable('$p/PurchaseOrder' PASSING po.OBJECT_VALUE AS "p"
                COLUMNS reference VARCHAR2(28) PATH 'Reference',
                        lineitem  XMLType      PATH 'LineItems/LineItem') t,
       XMLTable('$l/LineItem' PASSING t.lineitem AS "l"
                COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                        description VARCHAR2(128) PATH 'Description') li
  WHERE t.reference = 'DAUSTIN-20021009123335811PDT';
 
REFERENCE                         LINENO DESCRIPTION
-------------------------------- ------- --------------------------------
DAUSTIN-20021009123335811PDT           1 Dead Ringers
DAUSTIN-20021009123335811PDT           2 Getrud
DAUSTIN-20021009123335811PDT           3 Branded to Kill

SQL Functions that Update XML Data

There are several Oracle SQL functions that you can use 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 Oracle SQL functions are described in the following sections:

updateXML – Replace XML nodes of any kind. See "UPDATEXML SQL Function".
insertChildXML – Insert XML element or attribute nodes as children of a given element node. See "INSERTCHILDXML SQL Function".
insertChildXMLbefore – Insert new collection elements immediately before a given collection element of the same type. See "INSERTCHILDXMLBEFORE SQL Function".
insertChildXMLafter – Insert new collection elements immediately after a given collection element of the same type. See "INSERTCHILDXMLAFTER SQL Function".
insertXMLbefore – Insert XML nodes of any kind immediately before a given node (other than an attribute node). See "INSERTXMLBEFORE SQL Function".
insertXMLafter – Insert XML nodes of any kind immediately after a given node (other than an attribute node). See "INSERTXMLAFTER SQL Function".
appendChildXML – Insert XML nodes of any kind as the last child nodes of a given element node. See "APPENDCHILDXML SQL Function".
deleteXML – Delete XML nodes of any kind. See "DELETEXML SQL Function".

Use functions insertChildXML, insertChildXMLbefore, insertChildXMLafter, insertXMLbefore, insertXMLafter, and appendChildXML to insert XML data. Use function deleteXML to delete XML data. Use function updateXML to replace 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:

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 updating Oracle SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but updating SQL functions can raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.

See Also:

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

Inserting XML Elements using SQL Functions

There are several 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.

UPDATEXML SQL Function

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.

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 4-3 illustrates the syntax.

Figure 4-3 UPDATEXML Syntax

Description of "Figure 4-3 UPDATEXML Syntax"

Example 4-10 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 4-10 Updating XMLTYPE using UPDATE and UPDATEXML

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 4-11 updates multiple nodes using Oracle SQL function updateXML.

Example 4-11 Updating Multiple Text Nodes and Attribute Values using UPDATEXML

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 4-12 uses SQL function updateXML to update selected nodes within a collection.

Example 4-12 Updating Selected Nodes within a Collection using UPDATEXML

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>

UPDATEXML and 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 4-13.
If you update an attribute value to NULL, the value appears as the empty string. See Example 4-13.
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 4-14.

Example 4-13 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 4-13 NULL Updates with UPDATEXML – 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 4-14 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.

Example 4-14 NULL Updates with UPDATEXML – 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"/>

Updating the Same XML Node More Than Once

You can update the same XML node more than once in an updateXML expression. 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. The order of updates is determined by the order of the XPath expressions in left-to-right order. Each successive XPath works on the result of the previous XPath update.

Preserving DOM Fidelity When using UPDATEXML

Here are some guidelines for preserving 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, such as in <myElem/>.When you update a text node inside an element to NULL, you remove that text node from the element.When you update an attribute node to NULL, you make the value of the attribute become the empty string, for example, myAttr="".

When DOM Fidelity is Not Preserved

When you update a complexType element to NULL, you make the element appear empty in its parent, for example, <myElem/>.When you update a SQL-inlined simpleType element to NULL, you make the element disappear from its parent.When you update a text node to NULL, you are doing the same thing as setting the parent simpleType element to NULL. Furthermore, text nodes can appear only 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.

Determining Whether DOM Fidelity is Preserved

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

See Also:

"Querying a Registered XML Schema to Obtain Annotations" for an example of querying a schema to retrieve DOM fidelity values
"DOM Fidelity"

Optimization of Oracle SQL Functions that Modify XML Data

In most cases, the 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 SQL UPDATE operations on XMLType tables and columns that are stored object-relationally or as binary XML.

For structured 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 SecureFile LOBs are used (the default behavior), then sliding inserts are used to update only the portions of the data that need changing.

See Also:

"Updating XML Schema-Based and Non-Schema-Based XML Documents" for more about piecewise updating
Chapter 3, "Using Oracle XML DB" and Chapter 8, "XPath Rewrite for Structured Storage" for information about the conditions for XPath rewrite
"Performance Tuning for XQuery"

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

Example 4-15 XPath Expressions in 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
  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 4-16 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

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.

Creating XML Views using Oracle SQL Functions that Modify XML Data

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

Example 4-17 creates a view of table purchaseorder using Oracle SQL function updateXML.

Example 4-17 Creating a View using UPDATEXML

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>

INSERTCHILDXML SQL Function

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 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 4-4 shows the syntax.

Figure 4-4 INSERTCHILDXML Syntax

Description of "Figure 4-4 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 4-18 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.

Example 4-18 Inserting a LineItem Element into a LineItems Element

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.

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 4-19 is the same as Example 4-18, 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.

Example 4-19 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");

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.

INSERTCHILDXMLBEFORE SQL Function

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 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-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 no insertion is done, and target-data is returned unchanged.

Figure 4-5 shows the syntax.

Figure 4-5 INSERTCHILDXMLBEFORE Syntax

Description of "Figure 4-5 INSERTCHILDXMLBEFORE Syntax"

INSERTCHILDXMLAFTER SQL Function

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 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-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 no insertion is done, and target-data is returned unchanged.

Figure 4-6 shows the syntax.

Figure 4-6 INSERTCHILDXMLAFTER Syntax

Description of "Figure 4-6 INSERTCHILDXMLAFTER Syntax"

INSERTXMLBEFORE SQL Function

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 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 4-7 shows the syntax.

Figure 4-7 INSERTXMLBEFORE Syntax

Description of "Figure 4-7 INSERTXMLBEFORE Syntax"

Example 4-20 Inserting 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>

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

INSERTXMLAFTER SQL Function

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 schema-based or non-schema-based. It 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 4-8 shows the syntax.

Figure 4-8 INSERTXMLAFTER Syntax

Description of "Figure 4-8 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".

APPENDCHILDXML SQL Function

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 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 4-9 shows the syntax.

Figure 4-9 APPENDCHILDXML Syntax

Description of "Figure 4-9 APPENDCHILDXML Syntax"

Example 4-21 Inserting 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>

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

DELETEXML SQL Function

Oracle SQL function deleteXML deletes XML nodes of any kind. The XML document that is the target of the deletion 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 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 4-10 shows the syntax.

Figure 4-10 DELETEXML Syntax

Description of "Figure 4-10 DELETEXML Syntax"

Example 4-22 Deleting LineItem Element Number 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.