| Oracle® XML DB Developer's Guide 11g Release 2 (11.2) Part Number E10492-02 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 2 (11.2) Part Number E10492-02 |
|
|
View PDF |
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:
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
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".
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.
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, |
|
|
Also used as a path separator to identify the children node of any given node. For example, |
|
|
Used to identify all descendants of the current node. For example, |
|
|
Used as a wildcard to match any child node. For example, |
|
|
Used to denote predicate expressions. XPath supports a rich list of binary operators such as Brackets are also used to denote a position (index). For example, |
|
Functions |
XPath and XQuery support a set of built-in functions such as |
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.
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.
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.
XML_passing_clause ::=
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 functionexistsNode 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; 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.
Figure 4-2 describes the syntax for SQL/XML standard function XMLCast.
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; it 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.
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 intoXMLType columns. You can use SQL function sys_XMLGen to convert a fragment into a well-formed document by adding an enclosing tag. See "Generating XML using SQL Function SYS_XMLGEN".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
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
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 deleteXML to delete XML data; use 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:
Oracle SQL functions andXMLType 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.
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 schemaThere 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, it 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.
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 use that 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; 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 only perform insertions and deletions with updateXML by using it to replace the entire node that is 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.
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>
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 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"/>
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.
Here are some guidelines for preserving DOM fidelity when using Oracle SQL function updateXML:
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 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.
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
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, 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
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 ofXMLDATA 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.
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>
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 use that 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; child-data is inserted under each parent element.
If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and 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; the result is returned.
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; 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.
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 the last child elements named child-name.
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 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.
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 use that 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; child-data is inserted under each parent element.
If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and 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; the result is returned.
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; target-data is returned unchanged.
Figure 4-5 shows the syntax.
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 use that 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; child-data is inserted under each parent element.
If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and 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; the result is returned.
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; target-data is returned unchanged.
Figure 4-6 shows the syntax.
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 use that 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; that is, 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; target-data is returned unchanged (and 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; the result is returned.
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; target-data is returned unchanged.
Figure 4-7 shows the 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 functioninsertXMLbefore are not optimized. For this reason, Oracle recommends that you use function insertChildXML, insertChildXMLbefore, or insertChildXMLafter instead. See "Performance Tuning for XQuery".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 use that 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; that is, 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; target-data is returned unchanged (and 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; the result is returned.
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; target-data is returned unchanged.
Figure 4-8 shows the 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".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 use that 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; 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; target-data is returned unchanged (and 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; the result is returned.
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; target-data is returned unchanged.
Figure 4-9 shows the 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 functionappendChildXML are not optimized. For this reason, Oracle recommends that you use function insertChildXML, insertChildXMLbefore, or insertChildXMLafter instead. See "Performance Tuning for XQuery".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 use that 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; target-data is returned unchanged (and 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; the result is returned. Function deleteXML returns NULL if target-data or xpath is NULL.
Figure 4-10 shows the 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.
|
 Copyright © 2002, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|