Skip Headers
Oracle® XML DB Developer's Guide
12c Release 1 (12.1)

E17603-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 XMLType Operations

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

This chapter contains these topics:

See Also:

Selecting and Querying XML Data

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

Querying XML Data Using SQL/XML

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

Example 5-1 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 5-2 uses a PL/SQL cursor to query XML data. It uses a local XMLType instance to store transient data.

Example 5-2 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 5-3 and Example 5-4 both use SQL/XML function XMLTable to extract data from an XML purchase-order document. They then insert that data into a relational table. Example 5-3 uses SQL; Example 5-4 uses PL/SQL.

Example 5-3 Extracting XML Data and Inserting It into a Relational Table Using SQL

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 5-4 defines and uses a PL/SQL procedure to extract data from an XML purchase-order document and insert it into a relational table.

Example 5-4 Extracting XML Data and Inserting It into a Table Using PL/SQL

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 5-5 tabulates the purchase orders whose shipping address contains the string "Shores" and which were requested by customers whose names contain the string "ll" (double L). These purchase orders are grouped by customer and counted. The example uses XQuery Full Text to perform full-text search.

Example 5-5 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[address/text() contains text "Shores"]'
      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 5-6 extracts the fragments of a document that are 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 5-6 uses XMLType method isFragment() to determine whether the result is a fragment.

Example 5-6 Extracting Fragments 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/XML function XMLQuery to convert a fragment into a well-formed document.

Updating XML Data

This section covers updating XML data, both transient data and data stored in tables.

Updating an Entire XML 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 documents stored as binary XML can be made in a piecewise manner. See "Updating XML Schema-Based and Non-Schema-Based XML Documents".

Example 5-7 updates an XMLType instance using a SQL UPDATE statement.

Example 5-7 Updating XMLType Data Using SQL UPDATE

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

Replacing XML Nodes

Example 5-8 uses XQuery Update on the right side of a SQL UPDATE statement to update an existing XML document instead of creating a new document. The entire document is updated, not just the part of it that is selected.

Example 5-8 Updating XMLTYPE Data Using SQL UPDATE and XQuery Update

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 =
    XMLQuery('copy $i := $p1 modify
              (for $j in $i/PurchaseOrder/Actions/Action[1]/User
               return replace value of node $j with $p2)
             RETURN $i' PASSING po.OBJECT_VALUE AS "p1",
                                'SKING' AS "p2" RETURNING CONTENT)
  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>

Note that in Example 5-8 we pass the SQL string literal 'SKING' to the XQuery expression as a variable ($p2). In this simple example, since the value is a string literal, we could have simply used replace value of node $j with "SKING". That is, you can just use a literal XQuery string here, instead of passing a literal string from SQL to XQuery. In real-world examples you will typically pass a value that is available only at runtime; Example 5-8 shows how to do that. This is also true of other examples.

Example 5-9 updates multiple text nodes and attribute nodes.

Example 5-9 Updating Multiple Text Nodes and Attribute Nodes

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 =
    XMLQuery('copy $i := $p1 modify
                ((for $j in $i/PurchaseOrder/Requestor
                  return replace value of node $j with $p2),
                 (for $j in $i/PurchaseOrder/LineItems/LineItem[1]/Part/@Id
                  return replace value of node $j with $p3),
                 (for $j in $i/PurchaseOrder/LineItems/LineItem[1]/Description
                  return replace value of node $j with $p4),
                 (for $j in $i/PurchaseOrder/LineItems/LineItem[3]
                  return replace node $j with $p5))
                return $i'
             PASSING OBJECT_VALUE AS "p1",
                     'Stephen G. King' AS "p2",
                     '786936150421' AS "p3",
                     'The Rock' AS "p4",
                     XMLType('<LineItem ItemNumber="99">
                                <Description>Dead Ringers</Description>
                                <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
                              </LineItem>') AS "p5"
             RETURNING CONTENT)
  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 5-10 updates selected nodes within a collection.

Example 5-10 Updating Selected Nodes within a Collection

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 =
      XMLQuery(
        'copy $i := $p1 modify
           ((for $j in $i/PurchaseOrder/Requestor
             return replace value of node $j with $p2),
            (for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
             return replace value of node $j with $p3),
            (for $j in $i/PurchaseOrder/LineItems/LineItem
                         [Description/text()="The Unbearable Lightness Of Being"]
             return replace node $j with $p4))
           return $i'
        PASSING OBJECT_VALUE AS "p1",
                'Stephen G. King' AS "p2",
                25 AS "p3",
                XMLType('<LineItem ItemNumber="99">
                           <Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
                           <Description>The Rock</Description>
                         </LineItem>') AS "p4"
        RETURNING CONTENT)
      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>

Updating XML Data to NULL Values

The following considerations apply to updating XML data to 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 5-11.

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

  • 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 5-12.

Example 5-11 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 5-11 NULL Updates – 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 = 
      XMLQuery(
        'copy $i := $p1 modify
           ((for $j in $i/PurchaseOrder/LineItems/LineItem[Part/@Id="715515009058"]/Description
             return replace value of node $j with ()) ,
            (for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
             return replace value of node $j with ()) ,
            (for $j in $i/PurchaseOrder/LineItems/LineItem
                         [Description/text()= "The Unbearable Lightness Of Being"]
             return replace node $j with $p2)) 
         return $i'
        PASSING OBJECT_VALUE AS "p1", NULL AS "p2"
        RETURNING CONTENT)
      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>

Note that Example 5-11 shows two different but equivalent ways to remove the value of a node. For element Description and attribute Quantity, a literal XQuery empty sequence, (), replaces the existing value directly. For element LineItem, SQL NULL is passed into the XQuery expression to provide the empty node value. Since the value used is literal, it is simpler not to pass it from SQL to XQuery. But in real-world examples you will often pass a value that is available only at runtime. Example 5-11 shows how to do this for an empty XQuery sequence: pass a SQL NULL value.

Example 5-12 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 5-12 NULL Updates – 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 =
      XMLQuery(
        'copy $i := $p1 modify
           (for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]
            return replace value of node $j with $p2)
         return $i
        PASSING OBJECT_VALUE AS "p1", NULL AS "p2" RETURNING CONTENT)
  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"/>

Creating XML Views of Modified XML Data

You can use XQuery Update to create new views of XML data.

Example 5-13 creates a view of table purchaseorder.

Example 5-13 Creating a View Using Updated XML Data

CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
  SELECT XMLQuery('copy $i := $p1 modify
                     ((for $j in $i/PurchaseOrder/Actions
                       return replace value of node $j with ()),
                      (for $j in $i/PurchaseOrder/ShippingInstructions
                       return replace value of node $j with ()),
                      (for $j in $i/PurchaseOrder/LineItems
                       return replace value of node $j with ()))
                   return $i'
                  PASSING OBJECT_VALUE AS "p1" RETURNING CONTENT)
    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>

Inserting Child XML Nodes

This section shows how to use XQuery Update to insert new children (either a single attribute or one or more elements of the same type) under parent XML elements. The XML document that is the target of the insertion can be schema-based or non-schema-based.

Example 5-14 inserts a new LineItem element as a child of element LineItems. Note that it uses the Oracle XQuery pragma ora:child-element-name to specify the name of the inserted child element as LineItem.

Example 5-14 Inserting an Element into a Collection

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 = 
      XMLQuery('copy $i := $p1 modify
                  (for $j in $i/PurchaseOrder/LineItems
                   return (# ora:child-element-name LineItem #)
                          {insert node $p2 into $j})
                return $i'
               PASSING OBJECT_VALUE AS "p1",
                       XMLType('<LineItem ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
                                </LineItem>') AS "p2"
               RETURNING CONTENT)
      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 5-15 is the same as Example 5-14, 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 5-15 Inserting an Element that Uses a Namespace

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      XMLQuery('declare namespace e = "films.xsd"; (: :)
                copy $i := $p1 modify
                  (for $j in $i/PurchaseOrder/LineItems
                   return (# ora:child-element-name e:LineItem #)
                          {insert node $p2 into $j})
                return $i'
               PASSING OBJECT_VALUE AS "p1",
                       XMLType('<e:LineItem ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
                                </e:LineItem>') AS "p2"
               RETURNING CONTENT)
      WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
                      PASSING OBJECT_VALUE AS "p");

Example 5-16 inserts a LineItem element before the first LineItem element.

Example 5-16 Inserting an Element Before an 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 = 
      XMLQuery('copy $i := $p1 modify
                  (for $j in $i/PurchaseOrder/LineItems/LineItem[1]
                   return insert node $p2 before $j)
                return $i'
               PASSING OBJECT_VALUE AS "p1",
                       XMLType('<LineItem ItemNumber="314">
                                  <Description>Brazil</Description>
                                  <Part Id="314159265359" UnitPrice="69.95" 
                                        Quantity="2"/>
                                </LineItem>') AS "p2"
               RETURNING CONTENT)
      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>

Example 5-17 inserts a Date element as the last child of an Action element.

Example 5-17 Inserting an Element as the Last Child 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 = 
      XMLQuery('copy $i := $p1 modify
                  (for $j in $i/PurchaseOrder/Actions/Action[1]
                   return insert nodes $p2 as last into $j)
                return $i'
               PASSING OBJECT_VALUE AS "p1",
                       XMLType('<Date>2002-11-04</Date>') AS "p2"
               RETURNING CONTENT)
      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>

Deleting XML Nodes

Example 5-18 deletes the LineItem element whose ItemNumber attribute has value 222.

Example 5-18 Deleting an 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]'PASSINGPO
------------------------------------------------------------------------
<LineItem ItemNumber="222">
  <Description>The Harder They Come</Description>
  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      XMLQuery('copy $i := $p modify
                  delete nodes $i/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]
                return $i'
               PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
      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.