| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
This chapter describes XMLType operations and indexing for XML applications (schema-based and non-schema-based). It includes guidelines for creating, manipulating, updating, querying, and indexing XMLType columns and tables.
This chapter contains these topics:
|
Note:
|
You can query XML data from XMLType columns in the following ways:
Select XMLType columns in SQL, PL/SQL, or Java.
Query XMLType columns directly or using XMLType methods extract() and existsNode().
Use Oracle Text operators to query the XML content. See "Indexing XMLType Columns" and Chapter 10, "Full-Text Search Over XML".
Use the XQuery language. See "Using XQuery with XMLType Data"
Pretty-printing of results has a performance cost in result size and processing time, because it requires building a full DOM and retaining or generating appropriate whitespace formatting information. For this reason, it is not the default behavior.
If you need pretty-printed output, invoke XMLType method extract() on the results. Avoid doing this, however, when working with large documents.
The following rules govern pretty-printing of results:
SQL functions never pretty-print.
XMLType methods (member functions) extract() and transform() always pretty-print.
All other XMLType methods and all PL/SQL functions in packages DBMS_XMLDOM and DBMS_XSLPROCESSOR pretty-print if the data is stored object-relationally; otherwise (CLOB storage), they do not pretty-print.
|
Note: As mentioned in "Conventions", many examples in this book show results in pretty-printed form to promote readability, even when the results of the operation would not be pretty-printed in reality. |
The XPath language is a W3C Recommendation for navigating XML documents. 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 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 |
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 an index into a list. For example, |
|
Functions |
XPath supports 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 the XMLType methods getClobVal(), getStringVal(), getNumberVal(), and getBlobVal(csid) to retrieve XML data as a CLOB, VARCHAR, NUMBER, and BLOB value, respectively.
Example 4-1 Selecting XMLType Columns Using Method getClobVal()
This example shows how to select an XMLType column using method getClobVal():
CREATE TABLE xml_table OF XMLType;
Table created.
CREATE TABLE table_with_xml_column (filename VARCHAR2(64), xml_document XMLType);
Table created.
INSERT INTO xml_table
VALUES (XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'),
nls_charset_id('AL32UTF8')));
1 row created.
INSERT INTO table_with_xml_column (filename, xml_document)
VALUES ('purchaseOrder.xml',
XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'),
nls_charset_id('AL32UTF8')));
1 row created.
SELECT x.OBJECT_VALUE.getCLOBVal() FROM xml_table x;
X.OBJECT_VALUE.GETCLOBVAL()
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOr
der.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
...
1 row selected.
--
SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;
X.XML_DOCUMENT.GETCLOBVAL()
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOr
der.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
...
1 row selected.
You can query XMLType data and extract portions of it using SQL functions existsNode, extract, and extractValue. These functions use a subset of the W3C XPath recommendation to navigate the document.
Figure 4-1 describes the syntax for SQL function existsNode.
SQL function existsNode checks whether the given XPath path references at least one XML element node or text node. If so, the function returns 1; otherwise, it returns 0. Optional parameter namespace_string is used to map the namespace prefixes specified in parameter XPath_string to the corresponding namespaces.
An XPath expression such as /PurchaseOrder/Reference results in a single node. Therefore, existsNode will return 1 for that XPath. This is the same with /PurchaseOrder/Reference/text(), which results in a single text node.
If existsNode is called with an XPath expression that locates no nodes, the function returns 0.
Function existsNode can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.
|
Note: When using SQL functionexistsNode in a query, always use it in the WHERE clause, never in the SELECT list. |
Example 4-2 Using EXISTSNODE to Find a node
This example uses SQL function existsNode to select rows with SpecialInstructions set to Expedite.
SELECT OBJECT_VALUE
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Expedite"]')
= 1;
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.
SQL function extract is similar to existsNode. It accepts a VARCHAR2 XPath string that targets a node set and an optional namespace parameter. It returns an XMLType instance containing an XML fragment. The syntax is described in Figure 4-2:
extract(XMLType_instance IN XMLType, XPath_string IN VARCHAR2, namespace_string In VARCHAR2 := NULL) RETURN XMLType;
Applying extract to an XMLType value extracts the node or a set of nodes from the document identified by the XPath expression. The XPath argument must target a node set. So, for example, XPath expression /a/b/c[count('//d')=4] can be used, but count('//d') cannot, because it returns a scalar value (number).
The extracted nodes can be element, attribute, or text nodes. If multiple text nodes are referenced in the XPath expression, the text nodes are collapsed into a single text node value. Namespace can be used to supply namespace information for prefixes in the XPath expression.
The XMLType instance returned from extract need not be a well-formed XML document. It can contain a set of nodes or simple scalar data. You can use XMLType methods getStringVal() and getNumberVal() to extract the scalar data.
For example, the XPath expression /PurchaseOrder/Reference identifies the Reference element inside the XML document shown previously. The expression /PurchaseOrder/Reference/text(), on the other hand, refers to the text node of this Reference element.
|
Note: A text node is considered an instance ofXMLType. In other words, the following expression returns an XMLtype instance even though the instance may contain only text:
extract(OBJECT_VALUE, '/PurchaseOrder/Reference/text()') You can use method |
Use the text() node test to identify text nodes in elements before using the getStringVal() or getNumberVal() to convert them to SQL data. Not having the text() node test would produce an XML fragment.
For example:
XPath /PurchaseOrder/Reference identifies the fragment <Reference> ... </Reference>
XPath /PurchaseOrder/Reference/text() identifies the value of the text node of the Reference element.
You can use the index mechanism to identify individual elements in case of repeated elements in an XML document. If you have an XML document such as that in Example 4-3, then you can use:
XPath expression //LineItem[1] to identify the first LineItem element.
XPath expression //LineItem[2] to identify the second LineItem element.
Example 4-3 Purchase-Order XML Document
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<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>
</PurchaseOrder>
The result of SQL function extract is always an XMLType instance. If applying the XPath path produces an empty set, then extract returns a NULL value.
SQL function extract can be used in a number of ways. You can extract:
Numerical values on which function-based indexes can be created to speed up processing
Collection expressions for use in the FROM clause of SQL statements
Fragments for later aggregation to produce different documents
Example 4-4 Using EXTRACT to Extract the Value of a Node
This example uses SQL function extract to retrieve the Reference children of PurchaseOrder nodes whose SpecialInstructions attribute has value Expedite.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Expedite"]')
= 1;
REFERENCE
------------------------------------------------------------
<Reference>AMCEWEN-20021009123336271PDT</Reference>
<Reference>SKING-20021009123336321PDT</Reference>
<Reference>AWALSH-20021009123337303PDT</Reference>
<Reference>JCHEN-20021009123337123PDT</Reference>
<Reference>AWALSH-20021009123336642PDT</Reference>
<Reference>SKING-20021009123336622PDT</Reference>
<Reference>SKING-20021009123336822PDT</Reference>
<Reference>AWALSH-20021009123336101PDT</Reference>
<Reference>WSMITH-20021009123336412PDT</Reference>
<Reference>AWALSH-20021009123337954PDT</Reference>
<Reference>SKING-20021009123338294PDT</Reference>
<Reference>WSMITH-20021009123338154PDT</Reference>
<Reference>TFOX-20021009123337463PDT</Reference>
13 rows selected.
|
Note: SQL functionextractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact. |
SQL function extractValue takes as parameters an XMLType instance and an XPath expression that targets a node set. It returns a scalar value corresponding to the result of the XPath evaluation on the XMLType instance.
XML schema-based documents. For documents based on XML schema, if Oracle Database can infer the type of the return value, then a scalar value of the appropriate type is returned. Otherwise, the result is of type VARCHAR2.
Non-schema-based documents. If the query containing extractValue can be rewritten, such as when the query is over a SQL/XML view, then a scalar value of the appropriate type is returned. Otherwise, the result is of type VARCHAR2.
Figure 4-3 describes the extractValue syntax.
SQL function extractValue attempts to determine the proper return type from the XML schema associated with the document, or from other information such as the SQL/XML view. If the proper return type cannot be determined, then Oracle XML DB returns a VARCHAR2. With XML schema-based content, extractValue returns the underlying datatype in most cases. CLOB values are returned directly.
If a specific datatype is desired, a conversion function such as to_char or to_date can be applied to the result of extractValue or extract.getStringVal(). This can help maintain consistency between different queries regardless of whether the queries can be rewritten.
SQL function extractValue lets you extract the desired value more easily than extract; it is a convenience function. You can use it in place of extract().getStringVal() or extract().getnumberval().
For example, you can replace extract(x, 'path/text()').getStringVal() with extractValue(x, 'path/text()'). If the node at path has only one child and that child is a text node, then you can leave the text() test off of the XPath argument: extractValue(x, 'path'). If not, an error is raised if you leave off text().
SQL function extractValue has the same syntax as function extract.
SQL function extractValue has the following characteristics:
It returns only a scalar value (NUMBER, VARCHAR2, and so on). It cannot return XML nodes or mixed content. An error is raised if extractValue cannot return a scalar value.
By default, it returns a VARCHAR2 value. If the length is greater than 4K, a run-time error is raised.
If XML schema information is available at query compile time, then the datatype of the returned value is based on the XML schema information. For instance, if the XML schema information for the XPath /PurchaseOrder/LineItems/LineItem[1]/Part/@Quantity indicates a number, then extractValue returns a NUMBER.
If extractValue is applied to a SQL/XML view and the datatype of the column can be determined from the view definition at compile time, the appropriate type is returned.
If the XPath argument identifies a node, then the node must have exactly one text child (or an errror is raised). The text child is returned. For example, this expression extracts the text child of the Reference node:
extractValue(xmlinstance, '/PurchaseOrder/Reference')
The XPath argument must target a node set. So, for example, XPath expression /a/b/c[count('//d')=4] can be used, but count('//d') cannot, because it returns a scalar value (number).
Example 4-5 Extracting the Scalar Value of an XML Fragment Using extractValue
This query extracts the scalar value of the Reference node. This is in contrast to Example 4-4 where function extract is used to retrieve the <Reference> node itself.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Expedite"]')
= 1;
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: FunctionextractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact. |
The following examples illustrate ways you can query XML data with SQL.
Example 4-6 Querying XMLType Using EXTRACTVALUE and EXISTSNODE
This example inserts two rows into the purchaseorder table, then queries data in those rows using extractValue.
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'SMCCAIN-2002091213000000PDT.xml'),
nls_charset_id('AL32UTF8')));
1 row created.
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'VJONES-20020916140000000PDT.xml'),
nls_charset_id('AL32UTF8')));
1 row created.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,
extractValue(OBJECT_VALUE, '/PurchaseOrder/*//User') USERID,
CASE
WHEN existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject/Date') = 1
THEN 'Rejected'
ELSE 'Accepted'
END "STATUS",
extractValue(OBJECT_VALUE, '//Date') STATUS_DATE
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,'//Date') = 1
ORDER BY extractValue(OBJECT_VALUE,'//Date');
REFERENCE USERID STATUS STATUS_DATE
-------------------------------- -------- -------- ------------
VJONES-20020916140000000PDT SVOLLMAN Accepted 2002-10-11
SMCCAIN-2002091213000000PDT SKING Rejected 2002-10-12
2 rows selected.
Example 4-7 Querying Transient XMLType Data
This example uses a PL/SQL cursor to query XML data. A local XMLType instance is used to store transient data.
DECLARE
xNode XMLType;
vText VARCHAR2(256);
vReference VARCHAR2(32);
CURSOR getPurchaseOrder(reference IN VARCHAR2) IS
SELECT OBJECT_VALUE XML
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="'|| reference || '"]')
= 1;
BEGIN
vReference := 'EABEL-20021009123335791PDT';
FOR c IN getPurchaseOrder(vReference) LOOP
xNode := c.XML.extract('//Requestor');
vText := xNode.extract('//text()').getStringVal();
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');
vText := xNode.extract('//text()').getStringVal();
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-8 Extracting XML Data with EXTRACT, and Inserting It into a Table
This example extracts data from an XML purchase-order document, and inserts it into a SQL relational table using extract.
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));
Table created.
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));
Table created.
INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
phone, rejectedby, daterejected, comments, specialinstructions)
SELECT x.OBJECT_VALUE.extract('/PurchaseOrder/Reference/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/Actions'),
x.OBJECT_VALUE.extract('/PurchaseOrder/User/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/CostCenter/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/ShippingInstructions/name/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/ShippingInstructions/address/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/ShippingInstructions/telephone/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/Rejection/User/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/Rejection/Date/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/Rejection/Comments/text()').getStringVal(),
x.OBJECT_VALUE.extract('/PurchaseOrder/SpecialInstructions/text()').getStringVal()
FROM purchaseorder x
WHERE x.OBJECT_VALUE.existsNode('/PurchaseOrder[Reference="EABEL-20021009123336251PDT"]') = 1;
1 row created.
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
SELECT x.OBJECT_VALUE.extract('/PurchaseOrder/Reference/text()').getStringVal(),
value(li).extract('/LineItem/@ItemNumber').getNumberVal(),
value(li).extract('/LineItem/Part/@Id').getNumberVal(),
value(li).extract('/LineItem/Description/text()').getStringVal(),
value(li).extract('/LineItem/Part/@Quantity').getNumberVal(),
value(li).extract('/LineItem/Part/@UnitPrice').getNumberVal()
FROM purchaseorder x, table(XMLSequence(OBJECT_VALUE.extract('/PurchaseOrder/LineItems/LineItem'))) li
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="EABEL-20021009123336251PDT"]') = 1;
3 rows created.
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- EABEL-20021009123336251PDT EABEL Ellen S. Abel Counter to Counter 1 row selected. 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 3 rows selected.
Example 4-9 Extracting XML Data with EXTRACTVALUE, and Inserting It into a Table
This example extracts data from an XML purchase-order document, and inserts it into a relational table using SQL function extractValue.
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)
VALUES (extractValue(PurchaseOrder, '/PurchaseOrder/Reference'),
extractValue(PurchaseOrder, '/PurchaseOrder/Requestor'),
extract(PurchaseOrder, '/PurchaseOrder/Actions'),
extractValue(PurchaseOrder, '/PurchaseOrder/User'),
extractValue(PurchaseOrder, '/PurchaseOrder/CostCenter'),
extractValue(PurchaseOrder, '/PurchaseOrder/ShippingInstructions/name'),
extractValue(PurchaseOrder, '/PurchaseOrder/ShippingInstructions/address'),
extractValue(PurchaseOrder, '/PurchaseOrder/ShippingInstructions/telephone'),
extractValue(PurchaseOrder, '/PurchaseOrder/Rejection/User'),
extractValue(PurchaseOrder, '/PurchaseOrder/Rejection/Date'),
extractValue(PurchaseOrder, '/PurchaseOrder/Rejection/Comments'),
extractValue(PurchaseOrder, '/PurchaseOrder/SpecialInstructions'))
RETURNING reference INTO reference;
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
SELECT reference,
extractValue(value(li), '/LineItem/@ItemNumber'),
extractValue(value(li), '/LineItem/Part/@Id'),
extractValue(value(li), '/LineItem/Description'),
extractValue(value(li), '/LineItem/Part/@Quantity'),
extractValue(value(li), '/LineItem/Part/@UnitPrice')
FROM table(XMLSequence(extract(PurchaseOrder, '/PurchaseOrder/LineItems/LineItem'))) li;
END;/
Procedure created.
CALL insertPurchaseOrder(XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8')));
Call completed.
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- SBELL-2002100912333601PDT SBELL Sarah J. Bell Air Mail 1 row selected. 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 3 rows selected.
Example 4-10 Searching XML Data with XMLType Methods extract() and existsNode()
This example extracts the purchase-order name from the purchase-order element, PurchaseOrder, for customers with "ll" (double L) in their names and the word "Shores" in the shipping instructions. It uses XMLType methods extract() and existsNode() instead of SQL functions extract and existsNode.
SELECT p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal() NAME, count(*) FROM purchaseorder p WHERE p.OBJECT_VALUE.existsNode ('/PurchaseOrder/ShippingInstructions[ora:contains(address/text(),"Shores")>0]', 'xmlns:ora="http://xmlns.oracle.com/xdb') = 1 AND p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal() LIKE '%ll%' GROUP BY p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal(); NAME COUNT(*) -------------------- ---------- Allan D. McEwen 9 Ellen S. Abel 4 Sarah J. Bell 13 William M. Smith 7 4 rows selected.
Example 4-11 Searching XML Data with EXTRACTVALUE
This example shows the query of Example 4-10 rewritten to use SQL function extractValue.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME, count(*) FROM purchaseorder WHERE existsNode (OBJECT_VALUE, '/PurchaseOrder/ShippingInstructions[ora:contains(address/text(), "Shores")>0]', 'xmlns:ora="http://xmlns.oracle.com/xdb') = 1 AND extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor/text()') LIKE '%ll%' GROUP BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor'); NAME COUNT(*) -------------------- ---------- Allan D. McEwen 9 Ellen S. Abel 4 Sarah J. Bell 13 William M. Smith 7 4 rows selected.
Example 4-12 shows usage of SQL function extract to extract nodes identified by an XPath expression. An XMLType instance containing the XML fragment is returned by extract. The result may be a set of nodes, a singleton node, or a text value. You can determine whether the result is a fragment using the isFragment() method on the XMLType instance.
|
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". You can, however, query further on the fragment using the various XMLType functions. |
Example 4-12 Extracting Fragments From an XMLType Instance Using EXTRACT
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE, count(*) FROM purchaseorder, table(XMLSequence(extract(OBJECT_VALUE,'//LineItem[Part/@Id="37429148327"]'))) WHERE extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[Part/@Id="37429148327"]').isFragment() = 1 GROUP BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') ORDER BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'); REFERENCE COUNT(*) -------------------------------- ---------- AWALSH-20021009123337303PDT 1 AWALSH-20021009123337954PDT 1 DAUSTIN-20021009123337553PDT 1 DAUSTIN-20021009123337613PDT 1 LSMITH-2002100912333722PDT 1 LSMITH-20021009123337323PDT 1 PTUCKER-20021009123336291PDT 1 SBELL-20021009123335771PDT 1 SKING-20021009123335560PDT 1 SMCCAIN-20021009123336151PDT 1 SMCCAIN-20021009123336842PDT 1 SMCCAIN-2002100912333894PDT 1 TFOX-2002100912333681PDT 1 TFOX-20021009123337784PDT 3 WSMITH-20021009123335650PDT 1 WSMITH-20021009123336412PDT 1 16 rows selected.
This section covers updating transient XML instances and XML data stored in tables. It details use of SQL functions updateXML, insertChildXML, insertXMLbefore, appendChildXML, and deleteXML.
For CLOB-based storage, an update effectively replaces the entire document. To update an entire XML document, use the 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.
|
Note: Updates for non-schema-based XML documents always update the entire XML document. |
Example 4-13 Updating XMLType Using the UPDATE SQL Statement
This example updates an XMLType instance using a SQL UPDATE statement.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,
extractValue(value(li), '/LineItem/@ItemNumber') LINENO,
extractValue(value(li), '/LineItem/Description') DESCRIPTION
FROM purchaseorder, table(XMLSequence(extract(OBJECT_VALUE, '//LineItem'))) li
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1
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
5 rows selected.
UPDATE purchaseorder
SET OBJECT_VALUE = XMLType(bfilename('XMLDIR', 'NEW-DAUSTIN-20021009123335811PDT.xml'),
nls_charset_id('AL32UTF8'))
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,
extractValue(value(li), '/LineItem/@ItemNumber') LINENO,
extractValue(value(li), '/LineItem/Description') DESCRIPTION
FROM purchaseorder, table(XMLSequence(extract(OBJECT_VALUE, '//LineItem'))) li
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1;
REFERENCE LINENO DESCRIPTION
-------------------------------- ------- --------------------------------
DAUSTIN-20021009123335811PDT 1 Dead Ringers
DAUSTIN-20021009123335811PDT 2 Getrud
DAUSTIN-20021009123335811PDT 3 Branded to Kill
3 rows selected.
There are several 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 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".
insertXMLbefore – Insert XML nodes of any kind immediately before a given node (other than an attribute node). See "INSERTXMLBEFORE 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, insertXMLbefore, 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; this will work, but it is less efficient than using one of the other functions, which perform more localized updates.
These are all pure functions, without side effects. Each of these functions applies an XPath-expression argument to input XML data and returns a modified copy of the input XML data. You can then use that returned data with SQL operation UPDATE to modify database data.
Each of these functions can be used on XML documents that are either schema-based or non-schema-based. In the case of schema-based XML data, these 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 |
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 datatype of replacement must correspond to the data to be replaced. If xpath targets an element node for replacement, then the datatype must be XMLType; if xpath targets an attribute node or a text node, then it must be VARCHAR2. In the case of 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.
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-4 illustrates the syntax.
Example 4-14 Updating XMLType Using UPDATE and UPDATEXML
This example 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.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]') ACTION FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; ACTION -------------------------------- <Action> <User>SVOLLMAN</User> </Action> 1 row selected. UPDATE purchaseorder SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]/User/text()', 'SKING') WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; 1 row updated. SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]') ACTION FROM purchaseorder WHERE existsNode(OBJECT_VALUE,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; ACTION --------------------------------- <Action> <User>SKING</User> </Action> 1 row selected.
Example 4-15 Updating Multiple Text Nodes and Attribute Values Using UPDATEXML
This example updates multiple nodes using SQL function updateXML.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
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 existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
Example 4-16 Updating Selected Nodes Within a Collection Using UPDATEXML
This example uses SQL function updateXML to update selected nodes within a collection.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
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 existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
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-17.
If you update an attribute value to NULL, the value appears as the empty string. See Example 4-17.
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-18.
Example 4-17 NULL Updates With UPDATEXML – Element and Attribute
This example 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".
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
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 existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
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>
1 row selected.
Example 4-18 updates the text node of a Part element whose Description attribute has value "A Night to Remember" to NULL.
Example 4-18 NULL Updates With UPDATEXML – Text Node
The XML data for this example corresponds to a different, revised purchase-order XML schema – see "Revised Purchase-Order XML Schema". In that XML schema, Description is an attribute of the Part element, not a sibling element.
SELECT extractValue(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]') PART
FROM purchaseorder
WHERE existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;
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 existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;
SELECT extractValue(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]') PART
FROM purchaseorder
WHERE existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;
PART
----
<Part Description="A Night to Remember" UnitCost="39.95"/>
|
See Also: Example 6-2, Example 6-3, Example 3-34, and Example 3-34 for examples of rewritingupdateXML expressions |
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 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:
|
In most cases, the SQL functions that modify XML data (updateXML, insertChildXML, insertXMLbefore, appendChildXML, and deleteXML) materialize a copy of the entire input XML document in memory, then update the copy. However, functions updateXML, insertChildXML, and deleteXML are optimized for SQL UPDATE operations on XML schema-based, object-relationally stored XMLType tables and columns. If particular conditions are met, then the function call is rewritten to update the object-relational columns directly with the values.
|
See Also: Chapter 3, "Using Oracle XML DB" and Chapter 6, "XPath Rewrite" for information on the conditions for XPath rewrite. |
For example, the XPath argument to updateXML in Example 4-19 is processed by Oracle XML DB and rewritten into the equivalent object relational SQL statement shown in Example 4-20.
Example 4-19 XPath Expressions in UPDATEXML Expression
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SBELL 1 row selected. UPDATE purchaseorder SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/User/text()', 'SVOLLMAN') WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; 1 row updated. SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SVOLLMAN 1 row selected.
Example 4-20 Object Relational Equivalent of UPDATEXML Expression
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SBELL 1 row selected. UPDATE purchaseorder p SET p."XMLDATA"."USERID" = 'SVOLLMAN' WHERE p."XMLDATA"."REFERENCE" = 'SBELL-2002100912333601PDT'; 1 row updated. SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SVOLLMAN 1 row selected.
You can use the SQL functions that modify XML data (updateXML, insertChildXML, insertXMLbefore, appendChildXML, and deleteXML) to create new views of XML data.
Example 4-21 Creating Views Using UPDATEXML
This example creates a view of the purchaseorder table using SQL function 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;
View created.
SELECT OBJECT_VALUE FROM purchaseorder_summary
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1;
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>
1 row selected.
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 actual 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 datatype 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 datatype 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-5 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-22 Inserting a LineItem Element into a LineItems Element
SELECT extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/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 existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
1 row updated.
SELECT extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/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 will be raised because the inserted data does not conform to the XML schema. For example, if the data in Example 4-22 used the namespace films.xsd, then the UPDATE statement would need to be as shown in Example 4-23.
Example 4-23 Inserting an Element that Uses a Namespace
This example is the same as Example 4-22, except that the LineItem element to be inserted refers to a namespace. This assumes that the XML schema requires a namespace for this element.
Note that this use of namespaces is different from the use of a namespace argument to function insertChildXML – namespaces supplied in that optional argument apply only to the XPath argument, not to the content to be inserted.
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 existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
1 row updated.
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 thesuccessor-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 nonattribute 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-6 shows the syntax.
Example 4-24 Inserting a LineItem Element Before the First LineItem ELement
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]') = 1; EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[1]') ------------------------------------------------------------ <LineItem ItemNumber="1"> <Description>Salesman</Description> <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/> </LineItem> 1 row selected. 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 existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]') = 1; SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[position() <= 2]') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]') = 1; EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[POSITION()<=2]') ------------------------------------------------------------------------ <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> 1 row selected.
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-6 shows the syntax.
Example 4-25 Inserting a Date Element as the Last Child of an Action Element
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/ACTIONS/ACTION[1]')
--------------------------------------------------------
<Action>
<User>KPARTNER</User>
</Action>
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
appendChildXML(OBJECT_VALUE,
'/PurchaseOrder/Actions/Action[1]',
XMLType('<Date>2002-11-04</Date>'))
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/ACTIONS/ACTION[1]')
--------------------------------------------------------
<Action>
<User>KPARTNER</User>
<Date>2002-11-04</Date>
</Action>
1 row selected.
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-6 shows the syntax.
Example 4-26 Deleting LineItem Element Number 222
SELECT extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------
<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
deleteXML(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
SELECT extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------
1 row selected.
Chapter 3 provides a basic introduction to creating indexes on XML documents that have been stored using the structured storage option. It demonstrates how to use the extractValue() function to create indexes on XMLType documents stored in tables or columns that are based on the structured storage option.
This section discusses other indexing techniques, including the following:
XPath Rewrite for Indexes on Singleton Elements or Attributes
Creating Function-Based Indexes on XMLType Tables and Columns
When indexes are created on structured XMLType tables or columns, Oracle XML DB attempts to rewrite the XPath expressions provided to SQL function extractValue into CREATE INDEX statements that operate directly on the underlying objects.
For instance, given an index created as shown in Example 4-27, XPath rewrite will rewrite the index, resulting in the create index statement shown in Example 4-28 being executed. As can be seen, the rewritten index is created directly on the columns that manage the attributes of the underlying SQL objects. This technique works well when the element or attribute being indexed occurs only once in the XML Document.
You often need to create an index over a collection: nodes that occur more than once in the target document.
For instance, suppose that you want to create an index on the Id attribute of the LineItem element. A logical first attempt would be to create an index using the syntax shown in Example 4-29. However, when the element or attribute being indexed occurs multiple times in the document, the CREATE INDEX operation fails, because extractValue() is only allowed to return a single value for each row it processes.
Example 4-29 Using extractValue() to Create an Index on a Repeating Element or Attributes
CREATE INDEX ilineitem_upccode ON purchaseorder
(extractValue(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Part/@Id'));
(extractValue(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Part/@Id'))
*
ERROR at line 2:
ORA-19025: EXTRACTVALUE returns value of only one node
You can instead create an index by replacing extractValue with function extract and method getStringVal(), as shown in Example 4-30.
Example 4-30 Using getStringVal() to Create a Function-Based Index on an EXTRACT
CREATE INDEX ilineitem_upccode
ON purchaseorder
(extract(OBJECT_VALUE,'PurchaseOrder/LineItems/LineItem/Part/@Id').getStringVal());
Index created.
This allows the CREATE INDEX statement to succeed. However, the index that is created is not what you might expect. The index is created by invoking SQL function extract and XMLType method getStringVal() for each row in the table, and then indexing the result against the rowid of the row.
The problem with this technique is that function extract can only return multiple nodes. The result of function extract is a single XMLType XML fragment containing all the matching nodes. The result of invoking getStringVal() on an XMLType instance that contains a fragment is a concatenation of the nodes in the fragment:
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') XML,
extract(OBJECT_VALUE, 'PurchaseOrder/LineItems/LineItem/Part/@Id').getStringVal() INDEX_VALUE
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
XML INDEX_VALUE
-------------------------------------------------------------------- --------------
<LineItems> 71551500905837
<LineItem ItemNumber="1"> 42914022271551
<Description>A Night to Remember</Description> 5011020
<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>
1 row selected.
What is indexed for this row is the concatenation of the 3 UPC codes, not, as intended, each of the individual UPC codes. In general, care should be taken when creating an index using SQL function extract. It is unlikely that this index will be useful.
As is shown in Chapter 3 for schema-based XMLType values, the best way to resolve this issue is to adopt a storage structure that uses nested tables, to force each node that is indexed to be stored as a separate row. The index can then be created directly on the nested table using object-relational SQL similar to that generated by XPath rewrite.
The index created in Example 4-30 is an example of a function-based index. A function-based index is created by evaluating the specified functions for each row in the table. In the case of Example 4-30, the results of the functions were not useful, and consequently the index itself was not useful.
A function-based index can be useful when the XML content is not managed using structured storage. In this case, instead of the CREATE INDEX statement being rewritten, the index is created by invoking the function on the XML content and indexing the result.
Example 4-31 Creating a Function-Based Index on a CLOB-based XMLType()
The table created in this example uses CLOB storage rather than structured storage. The CREATE INDEX statement creates a function-based index on the value of the text node of the Reference element. This index enforces the uniqueness constraint on the text-node value.
CREATE TABLE purchaseorder_clob OF XMLType
XMLTYPE STORE AS CLOB
ELEMENT "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd#PurchaseOrder";
Table created.
INSERT INTO purchaseorder_clob SELECT OBJECT_VALUE FROM purchaseorder;
134 rows created.
CREATE UNIQUE INDEX ipurchaseorder_reference
ON purchaseorder_clob (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'));
Index created.
INSERT INTO purchaseorder_clob
VALUES (XMLType(bfilename('XMLDIR', 'EABEL-20021009123335791PDT.xml'),
nls_charset_id('AL32UTF8')));
INSERT INTO purchaseorder_clob*
ERROR at line 1:
ORA-00001: unique constraint (QUINE.IPURCHASEORDER_REFERENCE) violated
The optimizer only considers using the index when the function included in the WHERE clause is identical to the function used to create the index.
Consider the queries in Example 4-32, which find a PurchaseOrder-based value of the text node associated with the Reference element. The first query, which uses function existsNode to locate the document, does not use the index, while the second query, which uses function extractValue, does use the index. This is because the index was created using extractValue.
Example 4-32 Queries that use Function-Based Indexes
EXPLAIN PLAN FOR
SELECT OBJECT_VALUE FROM purchaseorder_clob
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference = "EABEL-20021009123335791PDT"') = 1;
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3761539978
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4004 | 3 (34)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PURCHASEORDER_CLOB | 2 | 4004 | 3 (34)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('3A7F7DBBEE5543A486567A908C71D65A',3664,"PU
RCHASEORDER_CLOB"."XMLDATA"),'/PurchaseOrder[Reference = "EABEL-20021009123335791P
DT"')=1)
15 rows selected.
EXPLAIN PLAN FOR
SELECT OBJECT_VALUE FROM purchaseorder_clob
WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') = 'EABEL-20021009123335791PDT';
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1408177405
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER_CLOB | 1 | 2002 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IPURCHASEORDER_REFERENCE | 1 | | | 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(EXTRACTVALUE(SYS_MAKEXML('3A7F7DBBEE5543A486567A908C71D65A',3664,"XMLDATA"),'/Purc
haseOrder/Reference')='EABEL-20021009123335791PDT')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Function-based indexes can be created on both structured and unstructured schema-based XMLType tables and columns as well as non-schema-based XMLType tables and columns. If XPath rewrite cannot process the XPath expression supplied as part of the CREATE INDEX statement, the statement will result in a function-based index being created.
An example of this would be creating an index based on SQL function existsNode. Function existsNode returns 1 or 0, depending on whether or not a document contains a node that matches the supplied XPath expression. This means that it is not possible for XPath rewrite to generate an equivalent object-relational CREATE INDEX statement. In general, since existsNode returns 0 or 1, it makes sense to use bitmap indexes when creating an index based on function existsNode.
In Example 4-33, an index is created that can be used to speed up a query that searches for instances of a rejected purchase order by looking for the presence of a text node of element /PurchaseOrder/Reject/User.
Since the index is function-based, it can be used with structured and unstructured schema-based XMLType tables and columns, and with non-schema-based XMLType tables and columns.
Example 4-33 Creating a Function-Based index on Schema-Based XMLType
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject/User/text()') = 1;
EXTRACTVALUE(OBJECT_VALUE,'/PU
------------------------------
SMCCAIN-2002091213000000PDT
1 row selected.
CREATE BITMAP INDEX ipurchaseorder_rejected
ON purchaseorder (existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject/User/text()'));
Index created.
CALL DBMS_STATS.gather_table_stats(USER, 'PURCHASEORDER');
Call completed.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject/User/text()') = 1;
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 841749721
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 419 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PURCHASEORDER | 1 | 419 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PURCHASEORDER"."SYS_NC00018$" IS NOT NULL)
13 rows selected.
The indexing techniques outlined earlier in this chapter require you to be aware in advance of the set of XPath expressions that will be used when searching XML content. Oracle XML DB also makes it possible to create a CTXXPATH index. This is a general-purpose XPath-based index that can be used to improve the performance of any search based on SQL function existsNode. A CTXXPath index has the following advantages:
You do not need prior knowledge of the XPath expressions that will be searched on.
You can use it with structured and unstructured schema-based XMLType tables and columns, and with non-schema-based XMLType tables and columns.
You can use it to improve the performance of searches that involve XPath expressions that target nodes that occur multiple times within a document.
The CTXXPATH index is based on Oracle Text technology and the functionality provided in the HASPATH and INPATH operators provided by the Oracle Text contains function. The HASPATH and INPATH operators allow high performance XPath-like searches to be performed over XML content. Unfortunately, they do not support true XPath-compliant syntax.
The CTXXPATH index is designed to rewrite the XPath expression supplied to SQL function existsNode into HASPATH and INPATH operators, which can use the underlying text index to quickly locate a superset of the documents that match the supplied XPath expression. Each document identified by the text index is then checked, using a DOM-based evaluation, to ensure that it is a true match for the supplied XPath expression. Due to the asynchronous nature of the underlying Oracle Text technology, the CTXXPATH index will also perform a DOM-based evaluation of all un-indexed documents, to see if they also should be included in the result set.
CTXXPATH indexing has the following characteristics:
It can be used only to speed up processing of function existsNode. It acts as a primary filter for function existsNode. In other words, it provides a superset of the results that existsNode provides.
It works only for queries where the XPath expressions that identify the required documents are supplied using an existsNode expression that appears in the WHERE clause of the SQL statement being executed.
It handles only a limited set of XPath expressions. See "Choosing the Right Plan: Using CTXXPATH Index in EXISTSNODE Processing" for the list of XPath expressions not supported by the index.
It supports only the STORAGE preference parameter. See "Creating CTXXPATH Storage Preferences With CTX_DDL. Statements".
It follows the transactional semantics of function existsNode, returning unindexed rows as part of its result set, in order to guarantee that it returns a superset of the valid results. This is despite the asynchronous nature of Data Manipulation Language (DML) operations such as updating and deleting. (You must use a special command to synchronize DML operations, in a fashion similar to that of the Oracle Text index.)
You create CTXXPATH indexes the same way you create Oracle Text indexes, using the following syntax:
CREATE INDEX [schema.]index ON [schema.]table(XMLType column) INDEXTYPE IS CTXSYS.ctxxpath [PARAMETERS(paramstring)];
where
paramstring = '[storage storage_pref] [memory memsize] [populate | nopopulate]'
Example 4-34 Using CTXXPATH Index and EXISTSNODE for XPath Searching
This example demonstrates how to create a CTXXPATH index for XPath searching. A CTXXPATH index is a global index – the EXPLAIN PLANs in this example show that the index is used for each of two very different queries.
CREATE INDEX purchaseorder_clob_xpath ON purchaseorder_clob (OBJECT_VALUE)
INDEXTYPE IS CTXSYS.ctxxpath;
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') FROM purchaseorder_clob
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Air Mail"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2191955729
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2031 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER_CLOB | 1 | 2031 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | PURCHASEORDER_CLOB_XPATH | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('E26E03C077B81004E0340003BA0BF841',3626,"PURCHASEORDER_CLOB
"."XMLDATA"),'/PurchaseOrder[SpecialInstructions="Air Mail"]')=1)
2 - access("CTXSYS"."XPCONTAINS"(SYS_MAKEXML('E26E03C077B81004E0340003BA0BF841',3626,"XMLDATA
"),'HASPATH(/2cc2504b[6b3fb29d="17a03105"]) ')>0)
Note
-----
- dynamic sampling used for this statement
21 rows selected.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') FROM purchaseorder_clob
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[Description="The Rock"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2191955729
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2031 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER_CLOB | 1 | 2031 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | PURCHASEORDER_CLOB_XPATH | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('E26E03C077B81004E0340003BA0BF841',3626,"PURCHASEORDER_CLOB
"."XMLDATA"),'/PurchaseOrder/LineItems/LineItem[Description="The Rock"]')=1)
2 - access("CTXSYS"."XPCONTAINS"(SYS_MAKEXML('E26E03C077B81004E0340003BA0BF841',3626,"XMLDATA
"),'HASPATH(/2cc2504b/52304c67/69182640[2ea8698d="0fb68600"]) ')>0)
Note
-----
- dynamic sampling used for this statement
21 rows selected.
The only preference allowed in CTXXPATH indexing is the STORAGE preference. Create the STORAGE preference the same way you would for an Oracle Text index, as shown in Example 4-35.
|
Note: You must be granted execute privileges on theCTXSYS.CTX_DLL package in order to create storage preferences. |
Example 4-35 Creating and Using Storage Preferences for CTXXPATH Indexes
BEGIN
CTX_DDL.create_preference('CLOB_XPATH_STORE', 'BASIC_STORAGE');
CTX_DDL.set_attribute('CLOB_XPATH_STORE', 'I_TABLE_CLAUSE', 'tablespace USERS storage (initial 1K)');
CTX_DDL.set_attribute('CLOB_XPATH_STORE', 'K_TABLE_CLAUSE', 'tablespace USERS storage (initial 1K)');
CTX_DDL.set_attribute('CLOB_XPATH_STORE', 'R_TABLE_CLAUSE', 'tablespace USERS storage (initial 1K)');
CTX_DDL.set_attribute('CLOB_XPATH_STORE', 'N_TABLE_CLAUSE', 'tablespace USERS storage (initial 1K)');
CTX_DDL.set_attribute('CLOB_XPATH_STORE', 'I_INDEX_CLAUSE', 'tablespace USERS storage (initial 1K)');
END;/
PL/SQL procedure successfully completed.
CREATE INDEX purchaseorder_clob_xpath ON purchaseorder_clob (OBJECT_VALUE)
INDEXTYPE IS CTXSYS.ctxxpath
PARAMETERS('storage CLOB_XPATH_STORE memory 120M');
Index created.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') FROM purchaseorder_clob
WHERE existsNode(OBJECT_VALUE, '//LineItem/Part[@Id="715515011624"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2191955729
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2031 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER_CLOB | 1 | 2031 | 4 (0)| 00:00:01 |
| 2 | DOMAIN INDEX | PURCHASEORDER_CLOB_XPATH | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('3A7F7DBBEE5543A486567A908C71D65A',3664,"PURCHASEORDER_CLOB
"."XMLDATA"),'//LineItem/Part[@Id="715515011624"]')=1)
Note
-----
- dynamic sampling used for this statement
19 rows selected.
Example 4-36 Synchronizing the CTXXPATH Index
This example shows how to synchronize DML operations using the sync_index procedure in the CTX_DDL package.
CALL CTX_DDL.sync_index('purchaseorder_clob_xpath');
Call completed.
Example 4-37 Optimizing the CTXXPATH Index
This example shows how to optimize the CTXXPATH index using the optimize_index procedure in the CTX_DDL package.
EXEC CTX_DDL.optimize_index('PURCHASEORDER_CLOB_XPATH', 'FAST');
PL/SQL procedure successfully completed.
EXEC CTX_DDL.optimize_index('PURCHASEORDER_CLOB_XPATH', 'FULL');
PL/SQL procedure successfully completed.
It is not guaranteed that a CTXXPATH index will always be used to speed up existsNode processing, for the following reasons:
Oracle Database cost-based optimizer may decide it is too costly to use CTXXPATH index as a primary filter
XPath expressions cannot all be handled by CTXXPATH index. The following XPath constructs cannot be handled by CTXXPATH index:
XPath functions
Numerical range operators
Numerical equality
Arithmetic operators
Union operator (|)
Parent and sibling axes
An attribute following an asterisk (*), double slashes (//), or double periods (..); for example, /A/*/@attr, /A//@attr, or /A//../@attr
A period (.) or an asterisk (*) at the end of a path expression
A predicate following a period (.) or an asterisk (*)
String literal equalities are supported with the following restrictions:
The left side must be a path – period (.) by itself is not allowed; for example, .="dog" is not allowed
The right side must be a literal
Anything not expressible by abbreviated syntax is unsupported
For the cost-based optimizer to better estimate the costs and selectivities for function existsNode, you must first gather statistics on your CTXXPATH indexing by using the ANALYZE command or DBMS_STATS package as follows:
ANALYZE INDEX myPathIndex COMPUTE STATISTICS;
or you can simply analyze the whole table:
ANALYZE TABLE XMLTab COMPUTE STATISTICS;
XPath queries on XML schema-based XMLType table are candidates for XPath rewrite. An existsNode expression in a query may be rewritten to a set of operators on the underlying object-relational columns of the schema-based table. In such a case, the CTXXPATH index can no longer be used by the query, since it can only be used to satisfy existsNode queries on the index expression, specified during index creation time.
In Example 4-38, a CTXXPATH index is created on table purchaseorder. The existsNode expression specified in the WHERE clause is rewritten into an expression that checks if the underlying object-relational column is not NULL. This is in accordance with XPath rewrite rules. The optimizer hint /*+ NO_XML_QUERY_REWRITE */ causes XPath rewrite to be turned off for the query, so the existsNode expression is left unchanged.
Example 4-38 Creating a CTXXPATH Index on a Schema-Based XMLType Table
CREATE INDEX purchaseorder_xpath ON purchaseorder (OBJECT_VALUE)
INDEXTYPE IS CTXSYS.CTXXPATH;
Index created.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[Description = "The Rock"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 122532357
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 65520 | 823 (1)| 00:00:10 |
|* 1 | HASH JOIN SEMI | | 13 | 65520 | 823 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL | PURCHASEORDER | 134 | 56146 | 4 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN| LINEITEM_TABLE_IOT | 13 | 60073 | 818 (0)| 00:00:10 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
3 - filter("DESCRIPTION"='The Rock')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
EXPLAIN PLAN FOR
SELECT /*+ NO_XML_QUERY_REWRITE */ extractValue(OBJECT_VALUE,'/PurchaseOrder/Reference')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[Description = "The Rock"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3192700042
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 419 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 1 | 419 | 4 (0)| 00:00:01 |
| 2 | DOMAIN INDEX | PURCHASEORDER_XPATH | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('3A7F7DBBEE5543A486567A908C71D65A',3664,"PURCHASEORDER
"."XMLEXTRA","PURCHASEORDER"."XMLDATA"),'/PurchaseOrder/LineItems/LineItem[Description="The
Rock"]')=1)
16 rows selected.
Use tracing to determine whether or not an index is being used.
The choice of whether to use CTXXPATH indexes depends on the storage options used, the size of the documents being indexed, and the query mix involved.
CTXXPATH indexes can be used for queries with existsNode expressions on non-schema-based XMLType tables and columns when the data is stored as a CLOB value. CTXXPATH indexes are also useful when CLOB portions of schema-based documents are queried. The term CLOB-based storage is used to apply to these cases. CTXXPATH indexes can also be used for existsNode queries on schema-based XMLType columns, tables and views, as well as non-schema-based views. The term object-relational storage is used to apply to these cases.
If the storage is CLOB-based:
Check the query mix to see if a significant fraction involves the same set of XPath expressions. If so, then create function-based indexes for those expressions.
Check the query mix to see if a significant fraction involves existsNode queries. CTXXPATH indexes are particularly useful if there are a large number of small documents and for existsNode queries with low selectivity, that is, with relatively fewer number of hits. Under such scenarios, build CTXXPATH indexes.
As a general rule, the use of indexes is recommended for Online Transaction Processing (OLTP) environments with few updates.
If the storage is object-relational:
Check the query mix to see if a significant fraction involves XPath expressions that can be rewritten. Chapter 6, "XPath Rewrite" describes the XPath expressions that can potentially get rewritten. The set of XPath expressions that are actually rewritten depends on the type of XPath expression as well as the registered XML schema. B*tree, bitmap and other relational and domain indexes can further be built to improve performance. XPath rewrite offers significant performance advantages. Use it in general. It is enabled by default.
Check the query mix to see if a significant fraction involves the same set of XPath expressions. If so, then Oracle recommends that you create function-based indexes for these expressions. In the presence of XPath rewrite, the XPath expressions are sometimes better evaluated using function-based indexes when:
The queries involve traversing through collections. For example, in extractValue(/PurchaseOrder/Lineitems/Lineitem/Addresses/Address), multiple collections are traversed under XPath rewrite.
The queries involve returning a scalar element of a collection. For example, in extractValue(/PurchaseOrder/PONOList/PONO[1]), a single scalar item needs to be returned, and function-based indexes are more efficient for this. In such a case, you can turn off XPath rewrite using query-level or session-level hints, and use the function-based index
Of the queries that are not rewritten, check the query mix to see if a significant fraction involves existsNode queries. If so, then you should build CTXXPATH indexes. CTXXPATH indexes are particularly useful if there are a large number of small documents, and for existsNode queries with low selectivity, that is, with relatively fewer number of hits.
|
Note: Use indexes for OLTP environments that are seldom updated. MaintainingCTXXPATH and function-based indexes when there are frequent updates adds additional overhead. Take this into account when deciding whether function-based indexes, CTXXPATH indexes, or both should be built and maintained. When both types of indexes are built, Oracle Database makes a cost-based decision which index to use. Try to first determine statistics on the CTXXPATH indexing in order to assist the optimizer in choosing the CTXXPATH index when appropriate. |
You can create an Oracle Text index on an XMLType column. An Oracle Text index enables the contains SQL function for full-text search over XML.
To create an Oracle Text index, use CREATE INDEX, specifying the INDEXTYPE.
Example 4-39 Creating an Oracle Text Index
CREATE INDEX ipurchaseordertextindex ON purchaseorder (OBJECT_VALUE) INDEXTYPE IS CTXSYS.CONTEXT; Index created.
You can also perform Oracle Text operations such as contains and score on XMLType columns.
Example 4-40 Searching XML Data Using CONTAINS
This example shows an Oracle Text search using contains.
SELECT DISTINCT
extractValue(OBJECT_VALUE,
'/PurchaseOrder/ShippingInstructions/address') "Address"
FROM purchaseorder
WHERE
contains(OBJECT_VALUE,
'$(Fortieth) INPATH(PurchaseOrder/ShippingInstructions/address)')
> 0;
Address
------------------------------
1200 East Forty Seventh Avenue
New York
NY
10024
USA
1 row selected.
|
See Also: Chapter 10, "Full-Text Search Over XML" for more information on using Oracle Text operations with Oracle XML DB. |
