Skip Headers
Oracle® XML DB Developer's Guide
10g Release 2 (10.2)

Part Number B14259-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 XPath Rewrite

This chapter explains the fundamentals of XPath rewrite in Oracle XML DB and how to use it for XML schema-based structured storage. It details the rewriting of XPath-expression arguments to these important SQL functions: existsNode, extract, extractValue, XMLSequence, updateXML, insertChildXMl, and deleteXML.

This chapter contains these topics:

See Also:

"XPath Rewrite on XMLType Views"

Overview of XPath Rewrite

When XMLType data is stored in structured storage (object-relationally) using an XML schema and queries using XPath are used, they can potentially be rewritten directly to the underlying object-relational columns. This rewrite of queries can also potentially happen when queries using XPath are issued on certain non-schema-based XMLType views. The optimization process of rewriting XPath expressions is called XPath rewrite.

This enables the use of B*Tree or other indexes, if present on the column, to be used in query evaluation by the Optimizer. This XPath rewrite mechanism is used for XPath-expression arguments to SQL functions such as existsNode, extract, extractValue, and updateXML. This enables the XPath expression to be evaluated against the XML document without constructing the XML document in memory.

The XPath expressions that are rewritten by Oracle XML DB are a proper subset of those that are supported by Oracle XML DB. Whenever you can do so without losing functionality, use XPath expressions that can be rewritten.

Example 6-1 XPath Rewrite

For example, a query such as the following tries to obtain the Company element and compare it with the literal 'Oracle':

SELECT OBJECT_VALUE FROM mypurchaseorders p
  WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle';

Because table mypurchaseorders was created with XML schema-based structured storage, extractValue is rewritten to the underlying relational column that stores the company information for the purchaseOrder. The query is rewritten to the following:

SELECT VALUE(p) FROM mypurchaseorders p WHERE p.xmldata.Company = 'Oracle';

Note:

XMLDATA is a pseudo-attribute of datatype XMLType that enables direct access to the underlying object column. See Chapter 4, "XMLType Operations".

If there is a regular index created on the Company column, such as the following, then the preceding query uses the index for its evaluation.

CREATE INDEX company_index 
  ON mypurchaseorders e (extractValue(OBJECT_VALUE, '/PurchaseOrder/Company'));

XPath rewrite happens for XML schema-based tables and both schema-based and non-schema-based views. In this chapter, we consider only examples related to schema-based tables.

Example 6-2 XPath Rewrite with UPDATEXML

The XPath argument to SQL function updateXML in this example is rewritten to the equivalent object relational SQL statement given in Example 6-3.

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 6-3 Rewritten Object Relational Equivalent of XPath Rewrite with UPDATEXML

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.

See Also:

Chapter 3, "Using Oracle XML DB", "Understanding and Optimizing XPath Rewrite", for additional examples of rewrite over schema-based and non-schema-based views

Where Does XPath Rewrite Occur?

XPath rewrite happens for the following SQL functions:

XPath rewrite can happen when these SQL functions are present in any expression in a query, DML, or DDL statement. For example, you can use function extractValue to create indexes on the underlying relational columns.

Example 6-4 SELECT Statement and XPath Rewrites

This example gets the existing purchase orders:

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Company')
  FROM mypurchaseorders x
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Item[1]/Part') = 1;

Here are some examples of statements that get rewritten to use underlying columns:

Example 6-5 DML Statement and XPath Rewrites

This example deletes all PurchaseOrders where the Company is not Oracle:

DELETE FROM mypurchaseorders x
  WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle Corp';

Example 6-6 CREATE INDEX Statement and XPath Rewrites

This example creates an index on the Company column, because this is stored object relationally and the XPath rewrite happens, a regular index on the underlying relational column will be created:

CREATE INDEX company_index
  ON mypurchaseorders e (extractValue(OBJECT_VALUE,'/PurchaseOrder/Company'));

In this case, if the rewrite of the SQL functions results in a simple relational column, then the index is turned into a B*Tree or a domain index on the column, rather than a function-based index.

Which XPath Expressions Are Rewritten?

An XPath expression can generally be rewritten if all of the following are true:

Table 6-1 lists the kinds of XPath expressions that can be translated into underlying SQL queries.

Table 6-1 Sample List of XPath Expressions for Translation to Underlying SQL constructs

XPath Expression for Translation Description

Simple XPath expressions:

/PurchaseOrder/@PurchaseDate

/PurchaseOrder/Company

Involves traversals over object type attributes only, where the attributes are simple scalar or object types themselves. The only axes supported are the child and the attribute axes.

Collection traversal expressions:

/PurchaseOrder/Item/Part

Involves traversal of collection expressions. The only axes supported are child and attribute axes. Collection traversal is not supported if the SQL function is used during a CREATE INDEX operation.

Predicates:

[Company="Oracle"]

Predicates in the XPath are rewritten into SQL predicates.

List index (positional predicate):

lineitem[1]

Indexes are rewritten to access the nth item in a collection. These are not rewritten for updateXML, insertChildXML, and deleteXML.

Wildcard traversals:

/PurchaseOrder/*/Part

If the wildcard can be translated to a unique XPath (for example, /PurchaseOrder/Item/Part), then it is rewritten, unless it is the last entry in the path expression.

Descendent axis:

/PurchaseOrder//Part

Similar to a wildcard expression. The descendent axis gets rewritten, if it can be mapped to a unique XPath expression and the subsequent element is not involved in a recursive type definition.

Oracle-provided extension functions and some XPath functions

not, floor, ceiling, substring, string-length, translate

ora:contains

Any function from the Oracle XML DB namespace (http://xmlns.oracle.com/xdb) gets rewritten into the underlying SQL function. Some XPath functions also get rewritten.

String bind variables inside predicates

'/PurchaseOrder[@Id="'|| :1 || '"]'

XPath expressions using SQL bind variables are rewritten if they occur between the concatenation (||) operators and are inside the double-quotes.

Unnest operations using XMLSequence

table(XMLSequence(extract(...)))

When used in a table function call, XMLSequence combined with extract is rewritten to use the underlying nested table structures.


Common XPath Constructs Supported in XPath Rewrite

The following are some of the XPath constructs that get rewritten. This is not an exhaustive list and only illustrates some of the common forms of XPath expressions that get rewritten.

  • Simple XPath traversals

  • Predicates and index accesses

  • Oracle-provided extension functions on scalar values

  • SQL Bind variables

  • Descendant axis (XML schema-based data only): Rewrites over the descendant axis (//) are supported if:

    • There is at least one XPath child or attribute access following the //

    • Only one descendant of the children can potentially match the XPath child or attribute name following the //. If the XML schema indicates that multiple descendants of the children potentially match, and there is no unique path that the // can be expanded to, then no rewrite is done.

    • None of the descendants have an element of type xsi:anyType

    • There is no substitution group that has the same element name at any descendant.

  • Wildcards (XML schema-based only). Rewrites over wildcard axis (/*) are supported if:

    • There is at least one XPath child or attribute access following the /*

    • Only one of the grandchildren can potentially match the XPath child or attribute name following the /*. If the XML schema indicates that multiple grandchildren potentially match, and there is no unique path that the /* can be expanded to, then no rewrite is done.

    • None of the children or grandchildren of the node before the /* have an element of type xsi:anyType

    • There is no substitution group that has the same element name for any child of the node before the /*.

Unsupported XPath Constructs in XPath Rewrite

The following XPath constructs are not rewritten:

  • XPath functions other than those listed earlier. The listed functions are rewritten only if the input is an element with scalar content.

  • XPath variable references.

  • All axes other than the child and attribute axes.

  • Recursive type definitions with descendent axes.

  • UNION operations.

Common XMLSchema Constructs Supported in XPath Rewrite

In addition to standard XML Schema constructs such as complexTypes and sequences, the following additional XML Schema constructs are also supported. This is not an exhaustive list and seeks to illustrate the common schema constructs that get rewritten.

  • Collections of scalar values where the scalar values are used in predicates.

  • Simple type extensions containing attributes.

  • Enumerated simple types.

  • Boolean simple type.

  • Inheritance of complex types.

  • Substitution groups.

Unsupported XML Schema Constructs in XPath Rewrite

The following XML Schema constructs are not supported. This means that if the XPath expression includes nodes with the following XML Schema construct then the entire expression will not get rewritten:

  • XPath expressions accessing children of elements containing open content, namely any content. When nodes contain any content, then the expression cannot be rewritten, except when the any targets a namespace other than the namespace specified in the XPath. The any attributes are handled in a similar way.

  • Datatype operations that cannot be coerced, such as addition of a Boolean value and a number.

Common Storage Constructs Supported in XPath Rewrite

All rewritable XPath expressions over object-relational storage get rewritten. In addition to that, the following storage constructs are also supported for rewrite.

  • Simple numeric types mapped to SQL RAW datatype.

  • Various date and time types mapped to the SQL TIMESTAMP_WITH_TZ datatype.

  • Collections stored inline, out-of-line, as OCTs, and as nested tables.

  • XML functions over schema-based and non-schema-based XMLType views and SQL/XML views also get rewritten.

Unsupported Storage Constructs in XPath Rewrite

The following XML Schema storage constructs are not supported. This means that if the XPath expression includes nodes with the following storage construct then the entire expression will not get rewritten:

  • CLOB storage: If the XML schema maps part of the element definitions to a SQL CLOB value, then XPath expressions traversing such elements are not supported

XPath Rewrite Can Change Comparison Semantics

For the most part, there is no difference between rewritten XPath queries and functionally evaluated ones. However, since XPath rewrite uses XML Schema information to turn XPath predicates into SQL predicates, comparison of nonnumeric entities is different.

In XPath 1.0, the comparison operators, >, <, >=, and <=, use only numeric comparison. The two operands are converted to numeric values before comparison. If either of them fails to be converted to a numeric value, the comparison returns false.

For instance, if I have an XML-schema element definition such as the following, then an XPath predicate such as [ShipDate < '2003-02-01'] will always evaluate to false with functional evaluation.

<element name="ShipDate" type="xs:date" xdb:SQLType="DATE"/>

This is because the string value '2003-02-01' cannot be converted to a numeric quantity. With XPath rewrite, however, this predicate gets translated to a SQL date comparison, and will evaluate to true or false, depending on the value of ShipDate.

Similarly if a collection value is compared with another collection value, the XPath 1.0 semantics dictate that the values must be converted to strings and then compared. With XPath rewrite, the comparison uses the rules for comparing SQL values.

To suppress this behavior, you can turn off rewrite either using query hints or session level events.

How Are XPath Expressions Rewritten?

This section uses the same purchase-order XML schema introduced earlier in this chapter.

Example 6-7 Creating XML Schema-Based Purchase-Order Data

DECLARE
  doc VARCHAR2(2000) := 
   '<schema  
     targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
     xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" 
     xmlns="http://www.w3.org/2001/XMLSchema"   
     elementFormDefault="qualified">
      <complexType name="PurchaseOrderType">
        <sequence>
          <element name="PONum" type="decimal"/>
          <element name="Company">
            <simpleType>
              <restriction base="string">
                <maxLength value="100"/>
              </restriction>
            </simpleType>
          </element>
          <element name="Item" maxOccurs="1000">
            <complexType>
              <sequence>
                <element name="Part">
                  <simpleType>
                    <restriction base="string">
                      <maxLength value="20"/>
                    </restriction>
                  </simpleType>
                </element>
                <element name="Price" type="float"/>
              </sequence>
            </complexType>
          </element>
        </sequence>
      </complexType>
      <element name="PurchaseOrder" type="po:PurchaseOrderType"/>
    </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', doc);
END;
/

The registration creates the internal types. We can now create a table to store the XML values and also create a nested table to store the items.

CREATE TABLE mypurchaseorders OF XMLType
  XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder"
  VARRAY xmldata."Item" STORE AS TABLE item_nested;

Table created

Now, we insert a purchase order into this table.

INSERT INTO mypurchaseorders
  VALUES(
   XMLType(
     '<PurchaseOrder
          xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation
             = "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd   
                http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd">
        <PONum>1001</PONum>
        <Company>Oracle Corp</Company>
        <Item> 
          <Part>9i Doc Set</Part>
          <Price>2550</Price>
        </Item>
        <Item>
          <Part>8i Doc Set</Part>
          <Price>350</Price>
        </Item>
      </PurchaseOrder>'));

Because the XML schema did not specify anything about maintaining the ordering, the default is to maintain the ordering and DOM fidelity. Hence the types have the SYS_XDBPD$ (PD) attribute to store the extra information needed to maintain the ordering of nodes and to capture extra items such as comments, processing instructions and so on.

The SYS_XDBPD$ attribute also maintains the existential information for the elements (that is, whether or not the element was present in the input document). This is needed for simpleType elements, because they map to simple relational columns. In this case, both empty and missing simpleType elements map to NULL values in the column, and the SYS_XDBPD$ attribute can be used to distinguish the two cases. The XPath rewrite mechanism takes into account the presence or absence of the SYS_XDBPD$ attribute, and rewrites queries appropriately.

This table has a hidden XMLDATA column of type purchaseorder_t that stores the actual data.

Rewriting XPath Expressions: Mapping Types and Path Expressions

XPath-expression mapping of types and path expressions is described in the following sections.

Schema-Based: Mapping for a Simple XPath

A rewrite for a simple XPath involves accessing the attribute corresponding to the XPath expression – see Table 6-2.

Table 6-2 Simple XPath Mapping for purchaseOrder XML Schema

XPath Expression Maps to

/PurchaseOrder

column XMLDATA

/PurchaseOrder/@PurchaseDate

column XMLDATA."PurchaseDate"

/PurchaseOrder/PONum

column XMLDATA."PONum"

/PurchaseOrder/Item

elements of the collection XMLDATA."Item"

/PurchaseOrder/Item/Part

attribute "Part" in the collection XMLDATA."Item"


Schema-Based: Mapping for simpleType Elements

An XPath expression can contain a text() node test, which targets the text node (content) of an element. When rewriting, this maps directly to the underlying relational columns. For example, the XPath expression "/PurchaseOrder/PONum/text()" maps directly to the SQL column XMLDATA."PONum".

A NULL in the PONum column implies that the text value is not available: either the text() node test is not present in the input document or the element itself is missing. If the column is NULL, there is no need to check for the existence of the element in the SYS_XBDPD$ attribute.

The XPath "/PurchaseOrder/PONum" also maps to the SQL attribute XMLDATA."PONum". However, in this case, XPath rewrite must check for the existence of the element itself, using attribute SYS_XDBPD$ in column XMLDATA.

Schema-Based: Mapping of Predicates

Predicates are mapped to SQL predicate expressions. Since the predicates are rewritten to SQL, the comparison rules of SQL are used instead of the XPath 1.0 semantics.

Example 6-8 Mapping Predicates

The predicate in the XPath expression:

/PurchaseOrder[PONum=1001 and Company = "Oracle Corp"]

maps to the SQL predicate:

(XMLDATA."PONum" = 20 AND XMLDATA."Company" = "Oracle Corp")

The following query is rewritten to the structured (object-relational) equivalent, and will not require functional evaluation of the XPath.

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item').getClobval()
  FROM mypurchaseorders p
  WHERE existsNode(OBJECT_VALUE, 
                   '/PurchaseOrder[PONum=1001 AND Company = "Oracle Corp"]') = 1;

Schema-Based: Mapping of Collection Predicates  XPath expressions can involve relational collection expressions. In Xpath 1.0, these are treated as existential checks: if at least one member of the collection satisfies the expression, then the expression is true.

Example 6-9 Mapping Collection Predicates

The collection predicate in this XPath expression involves the relational greater-than operator (>):

/PurchaseOrder[Items/Price > 200]

This maps to the following SQL collection expression:

exists(SELECT NULL FROM table(XMLDATA."Item") x WHERE x."Price" > 200)

In this example, a collection is related to a scalar value. More complicated rewrites occur with a relation between two collections. For example, in the following XPath expression, both LineItems and ShippedItems are collections.

/PurchaseOrder[LineItems = ShippedItems]
 

In this case, if any combination of nodes from these two collections satisfies the equality, then the predicate is considered satisfied.

Example 6-10 Mapping Collection Predicates, Using EXISTSNODE

Consider a fictitious XPath that checks if a Purchaseorder has Items whose Price and Part number are the same:

/PurchaseOrder[Items/Price = Items/Part]
-- maps to a SQL collection expression:
   exists(SELECT NULL 
            FROM table(XMLDATA."Item") x
            WHERE exists(SELECT NULL 
                            FROM table(XMLDATA."Item") y
                            WHERE  y."Part" = x."Price"))

The following query is rewritten to the structured equivalent:

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item').getClobval()
  FROM  mypurchaseorders p
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Item/Price = Item/Part]') = 1;

Schema-Based: Document Ordering with Collection Traversals

Most of the rewrite preserves the original document ordering. However, because SQL does not guarantee ordering on the results of subqueries when selecting elements from a collection using SQL function extract, the resultant nodes may not be in document order.

Example 6-11 Document Ordering with Collection Traversals

For example:

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item[Price>2100]/Part')
  FROM mypurchaseorders p;

This query is rewritten to use a subquery:

SELECT (SELECT XMLAgg(XMLForest(x."Part" AS "Part")) 
          FROM table(XMLDATA."Item") x WHERE  x."Price" > 2100)
  FROM mypurchaseorders p;

In most cases, the result of the aggregation is in the same order as the collection elements, but this is not guaranteed. So, the results may not be in document order.

Schema-Based: Collection Position

An XPath expression can also access an element at a particular position of a collection. For example, "/PurchaseOrder/Item[1]/Part" is rewritten to extract out the first Item element of the collection, and access the Part attribute within that.

If the collection is stored as a varray, then this operation retrieves the nodes in the same order as in the original document. If the collection is stored as a nested table, then the order is indeterminate.

Schema-Based: XPath Expressions That Cannot Be Satisfied

An XPath expression can contain references to nodes that cannot be present in the input document. Such parts of the expression map to SQL NULL values during rewrite. For example, the XPath expression /PurchaseOrder/ShipAddress cannot be satisfied by any instance document conforming to the purchaseorder.xsd XML schema, because the schema does not allow for ShipAddress elements under PurchaseOrder. Hence this expression would map to a SQL NULL literal.

Schema-Based: Namespace Handling

Namespaces are handled in the same way as function-based evaluation. For schema-based documents, if the function (such as existsNode or extract) does not specify any namespace parameter, then the target namespace of the schema is used as the default namespace for the XPath expression.

Example 6-12 Handling Namespaces

For example, the XPath expression /PurchaseOrder/PONum is treated as /a:PurchaseOrder/a:PONum with xmlns:a= "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" if the SQL function does not explicitly specify the namespace prefix and mapping. In other words:

SELECT * FROM mypurchaseorders p
  WHERE  existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1;

is equivalent to the query:

SELECT * 
  FROM mypurchaseorders p
  WHERE existsNode(
          OBJECT_VALUE,
          '/PurchaseOrder/PONum',
          'xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
        = 1;

When performing XPath rewrite, the namespace for a particular element is matched with that of the XML schema definition. If the XML schema contains elementFormDefault="qualified" then each node in the XPath expression must target a namespace (this can be done using a default namespace specification or by prefixing each node with a namespace prefix).

If the elementFormDefault is unqualified (which is the default), then only the node that defines the namespace should contain a prefix. For instance if the purchaseorder.xsd had the element form to be unqualified, then existsNode expression should be rewritten as follows:

existsNode(
  OBJECT_VALUE,
  '/a:PurchaseOrder/PONum',                   
  'xmlns:a="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd")
= 1;

Note:

For the case where elementFormDefault is unqualified, omitting the namespace parameter in the existsNode expression in the preceding example would cause each node to default to the target namespace. This would not match the XML schema definition and consequently would not return any result. This is true whether or not the function is rewritten.

Schema-Based: Date Format Conversions

Date datatypes such as DATE, gMONTH, and gDATE have different format in XML Schema and SQL. If an expression has a string value for columns of such datatypes, then the rewrite automatically provides the XML format string to convert the string value correctly. Thus, the string value specified for a DATE column must match the XML date format, not the SQL DATE format.

Example 6-13 Date Format Conversions

For example, the expression [@PurchaseDate="2002-02-01"] cannot be simply rewritten as XMLDATA."PurchaseDate"="2002-02-01", because the default date format for SQL is not YYYY-MM-DD. Hence during XPath rewrite, the XML format string is added to convert text values into date datatypes correctly. Thus the preceding predicate would be rewritten as:

XMLDATA."PurchaseDate" = TO_DATE("2002-02-01","SYYYY-MM-DD");

Similarly when converting these columns to text values (needed for functions such as extract), XML format strings are added to convert them to the same date format as XML.

Existential Checks for Attributes and Elements with Scalar Values

SQL function existsNode checks for the existence of a node addressed by an XPath; function extract returns a node addressed by an XPath. Oracle XML DB needs to perform special checks for simpleType elements and for attributes used in existsNode expressions. This is because the SQL column value alone cannot distinguish whether an attribute or a simpleType element is missing or is empty; a NULL SQL column can represent either. These special checks are not required for intermediate elements, because the value of the user-defined SQL datatype indicates the absence or emptiness of the element.

Consider, for example, this expression:

existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum/text()') = 1;

Because the query is only interested in the text value of the node, this is rewritten to:

(p.XMLDATA."PONum" IS NOT NULL)

Consider this expression, without the text() node test:

existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1;

In this case, Oracle XML DB must check the SYS_XDBPD$ attribute in the parent node to determine whether the element is empty or is missing. This check is done internally. It can be represented in pseudocode as follows:

node_exists(p.XMLDATA."SYS_XDBPD$", "PONum")

The pseudofunction node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the positional-descriptor (PD) column (SYS_XDBPD$), to determine whether or not its second argument (element or attribute) node exists. It returns true if so, and false if not.

In the case of extract expressions, this check needs to be done for both attributes and elements. An expression of the form extract(OBJECT_VALUE, '/PurchaseOrder/PONum') maps to pseudocode such as the following:

CASE WHEN node_exists(p.XMLDATA.SYS_XDBPD$", "PONum")
       THEN XMLElement("PONum", p.XMLDATA."PONum")
       ELSE NULL END;

Note:

Be aware of this overhead when writing existsNode and extract expressions. You can avoid this overhead by using a text() node test in the XPath expression; using extractValue to obtain only the node value; or by turning off DOM fidelity for the parent node. DOM fidelity can be turned off by setting the value of the attribute maintainDOM in the element definition to be false. When turned off, empty elements and attributes are treated as missing.

Diagnosing XPath Rewrite

To determine if your XPath expressions are getting rewritten, you can use one of the following techniques:

Using EXPLAIN PLAN with XPath Rewrite

This section shows how you can use EXPLAIN PLAN to examine the query plans after rewrite. See "Understanding and Optimizing XPath Rewrite" for examples on how to use EXPLAIN PLAN to optimize XPath rewrite.

With the explained plan, if the plan does not pick applicable indexes and shows the presence of the SQL function (such as existsNode or extract), then you know that the rewrite has not occurred. You can then use the events described later to understand why the rewrite did not happen.

For example, using table mypurchaseorders we can see the use of EXPLAIN PLAN. We create an index on the Company element of PurchaseOrder to show how the plans differ.

CREATE INDEX company_index ON mypurchaseorders
       (extractValue(OBJECT_VALUE,'/PurchaseOrder/Company'));

Index created.

EXPLAIN PLAN FOR 
  SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum')
    FROM mypurchaseorders
    WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company="Oracle"]') = 1;

Explained.

SELECT PLAN_TABLE_OUTPUT
  FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))
/

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      |       |      |
|   1 |  TABLE ACCESS BY INDEX ROWID | MYPURCHASEORDERS |      |       |      |
|*  2 |   INDEX RANGE SCAN           | COMPANY_INDEX    |      |       |      |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MYPURCHASEORDERS"."SYS_NC00010$"='Oracle')

In this explained plan, you can see that the predicate uses internal columns and picks up the index on the Company element. This shows clearly that the query has been rewritten to the underlying relational columns.

In the following query, we are trying to perform an arithmetic operation on the Company element which is a string type. This is not rewritten and hence the EXPLAIN PLAN shows that the predicate contains the original existsNode expression. Also, since the predicate is not rewritten, a full table scan instead of an index range scan is used.

EXPLAIN PLAN FOR
  SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum')
    FROM mypurchaseorders
    WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+PONum="Oracle"]') = 1;

Explained.

SELECT PLAN_TABLE_OUTPUT
  FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))/

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
| Id  | Operation          | Name  
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |
|*  1 |  FILTER            |
|   2 |   TABLE ACCESS FULL| MYPURCHASEORDERS
|*  3 |   TABLE ACCESS FULL| ITEM_NESTED
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXISTSNODE(SYS_MAKEXML('C6DB2B4A1A3B0
              6CDE034080020E5CF39',2300,"MYPURCHASEORDERS"."XMLEXTRA",
              "MYPURCHASEORDERS"."XMLDATA"),
      '/PurchaseOrder[Company+PONum="Oracle"]')=1)
   3 - filter("NESTED_TABLE_ID"=:B1)

Using Events with XPath Rewrite

Events can be set in the initialization file or can be set for each session using the ALTER SESSION statement. The XML events can be used to turn off functional evaluation, turn off the XPath rewrite mechanism and to print diagnostic traces.

Turning Off Functional Evaluation (Event 19021)

By turning on this event, you can raise an error whenever any of the XML functions is not rewritten and is instead evaluated functionally. The error ORA-19022 - XML XPath functions are disabled will be raised when such functions execute. This event can also be used to selectively turn off functional evaluation of functions. Table 6-3 lists the various levels and the corresponding behavior.

Table 6-3 Event Levels and Behaviors

Event Turn off functional evaluation of . . .

Level 0x1

all XML functions

Level 0x2

extract

Level 0x4

existsNode

Level 0x8

transform

Level 0x10

extractValue

Level 0x20

updateXML

Level 0x40

insertXMLbefore

Level 0x80

appendChildXMl

Level 0x100

deleteXML

Level 0x200

XMLSequence

Level 0x4000

insertChildXML

Level 0x8000

XMLQuery


For example,

ALTER SESSION SET EVENTS '19021 trace name context forever, level 1';

would turn off the functional evaluation of all the XML operators listed earlier. Hence when you perform the query shown earlier that does not get rewritten, you will get an error during the execution of the query.

SELECT OBJECT_VALUE FROM mypurchaseorders
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+PONum="Oracle"]')=1 ;

ERROR:
ORA-19022: XML XPath functions are disabled

Tracing Reasons that Rewrite Does Not Occur

Event 19027 with level 8192 (0x2000) can be used to dump traces that indicate the reason that a particular XML function is not rewritten. For example, to check why the query described earlier, did not rewrite, we can set the event and run an EXPLAIN PLAN:

ALTER SESSION SET EVENTS '19027 TRACE NAME CONTEXT FOREVER, LEVEL 8192';

Session altered.

EXPLAIN PLAN FOR
  SELECT OBJECT_VALUE FROM mypurchaseorders
    WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+100="Oracle"]') = 1;

Explained.

This writes the following the Oracle trace file explaining that the rewrite for the XPath did not occur since there are inputs to an arithmetic function that are not numeric.

NO REWRITE
        XPath ==> /PurchaseOrder[Company+PONum = "Oracle"]        Reason ==> non numeric inputs to arith{2}{4}

XPath Rewrite of SQL Functions

This section details XPath rewrite for SQL functions existsNode, extractValue, extract, XMLSequence, updateXML, insertChildXML, and deleteXML. It explains the overhead involved in certain types of operations using existsNode or extract, and how to avoid it.

An update using one of these SQL functions normally involves updating a copy of the XML document and then replacing the entire document with the newly modified document.

When XMLType data is stored in an object-relational manner using XML-schema mapping, updates are optimized to directly modify pieces of the document in place. For example, an update of the PONum element can be rewritten to directly update the XMLDATA.PONum column, instead of materializing the whole document in memory and then performing the update.

Each of the functions updateXML, insertChildXML, and deleteXML must satisfy different conditions for it to use such rewrite optimization during update. If all of the conditions are satisfied, then the functional expression is rewritten into a simple relational update. For example:

UPDATE purchaseorder_table
  SET OBJECT_VALUE =
      updateXML(OBJECT_VALUE,
                '/PurchaseOrder/@PurchaseDate', '2002-01-02',
                '/PurchaseOrder/PONum/text()', 2200);

This update operation is rewritten as something like the following:

UPDATE purchaseorder_table p
  SET p.XMLDATA."PurchaseDate" = TO_DATE('2002-01-02', 'SYYYY-MM-DD'),
      p.XMLDATA."PONum" = 2100;

XPath Rewrite for EXISTSNODE

SQL function existsNode returns one (1) if the XPath argument targets a nonempty sequence of nodes (text, element, or attribute); otherwise, it returns zero (0). The value is determined differently, depending on the kind of node targeted by the XPath argument:

  • If the XPath argument targets a text node (using node test text()) or a complexType element node, Oracle XML DB simply checks whether the database representation of the element content is NULL.

  • Otherwise, the XPath argument targets a simpleType element node or an attribute node. Oracle XML DB checks for the existence of the node using the positional-descriptor attribute SYS_XDBPD$. If SYS_XDBPD$ is absent, then the existence of the node is determined by checking whether or not the column is NULL.

EXISTSNODE Mapping with Document Order Preserved

Table 6-4 shows the mapping of various XPaths in the case of SQL function existsNode when document ordering is preserved; that is, when SYS_XDBPD$ exists and maintainDOM="true" is present in the schema document.

Table 6-4 XPath Mapping for EXISTSNODE with Document Ordering Preserved

XPath Expression Maps to
/PurchaseOrder
CASE WHEN XMLDATA IS NOT NULL THEN 1 ELSE 0 END
/PurchaseOrder/@PurchaseDate
CASE WHEN node_existsFoot 1 (XMLDATA.SYS_XDBPD$, 'PurchaseDate')
       THEN 1 ELSE 0 END
/PurchaseOrder/PONum
CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PONum') 
       THEN 1 ELSE 0 END
/PurchaseOrder[PONum = 2100]
CASE WHEN XMLDATA."PONum"=2100 THEN 1 ELSE 0
/PurchaseOrder[PONum = 2100]/@PurchaseDate
CASE WHEN XMLDATA."PONum"=2100 
      AND node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PurchaseDate')
       THEN 1 ELSE 0 END
/PurchaseOrder/PONum/text() 
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1 ELSE 0
/PurchaseOrder/Item
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
                   WHERE value(x) IS NOT NULL)
       THEN 1 ELSE 0 END
/PurchaseOrder/Item/Part
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
                   WHERE node_existsFootref 1(x.SYS_XDBPD$, 'Part'))
       THEN 1 ELSE 0 END
/PurchaseOrder/Item/Part/text()
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
                   WHERE x."Part" IS NOT NULL)
       THEN 1 ELSE 0 END

Footnote 1 Pseudofunction node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the PD column, to determine whether or not its second argument node exists. It returns true if so, and false if not.

Example 6-14 EXISTSNODE Mapping with Document Order Preserved

Using the preceding mapping, this query checks whether purchase order 1001 contains a part with price greater than 2000:

SELECT count(*)
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, 
                   '/PurchaseOrder[PONum=1001 and Item/Price > 2000]') = 1;

This is rewritten as something like the following:

SELECT count(*) 
  FROM  purchaseorder p
  WHERE CASE WHEN p.XMLDATA."PONum" = 1001
              AND exists(SELECT NULL FROM table(XMLDATA."Item") p
                           WHERE  p."Price" > 2000 ))
               THEN 1
               ELSE 0
        END = 1;

This CASE expression is further optimized due to the constant relational equality expressions. The query becomes:

SELECT count(*) 
  FROM purchaseorder p
  WHERE p.XMLDATA."PONum"=1001
    AND exists(SELECT NULL FROM table(p.XMLDATA."Item") x
                 WHERE  x."Price" > 2000);

This uses relational indexes for its evaluation, if present on the Part and PONum columns.

EXISTSNODE Mapping Without Document Order Preserved

If the positional-descriptor attribute SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false") then NULL scalar columns map to simpleType elements that do not exist. In that case, you do not need to check for node existence using attribute SYS_XDBPD$. Table 6-5 shows the mapping of existsNode in the absence of the SYS_XDBPD$ attribute.

Table 6-5 XPath Mapping for EXISTSNODE Without Document Ordering

XPath Expression Maps to
/PurchaseOrder
CASE WHEN XMLDATA IS NOT NULL THEN 1 ELSE 0 END
/PurchaseOrder/@PurchaseDate
CASE WHEN XMLDATA.'PurchaseDate' IS NOT NULL THEN 1 ELSE 0 END
/PurchaseOrder/PONum
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1   ELSE 0 END
/PurchaseOrder[PONum = 2100]
CASE WHEN XMLDATA."PONum" = 2100 THEN 1 ELSE 0 END
/PurchaseOrder[PONum = 2100]/@PurchaseOrderDate
CASE WHEN XMLDATA."PONum" = 2100 
      AND XMLDATA."PurchaseDate" NOT NULL 
       THEN 1 ELSE 0 END
/PurchaseOrder/PONum/text()
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1 ELSE 0 END
/PurchaseOrder/Item
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x 
                   WHERE value(x) IS NOT NULL) 
       THEN 1 ELSE 0 END
/PurchaseOrder/Item/Part
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x 
                   WHERE x."Part" IS NOT NULL) 
       THEN 1 ELSE 0 END
/PurchaseOrder/Item/Part/text()
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x 
                   WHERE x."Part" IS NOT NULL)
       THEN 1 ELSE 0 END

XPath Rewrite for EXTRACTVALUE

SQL function extractValue is a shortcut for extracting text nodes and attributes using function extract and then using method getStringVal() or getNumberVal() to obtain the scalar content. Function extractValue returns the values of attribute nodes or the text nodes of elements with scalar values. Function extractValue cannot handle XPath expressions that return multiple values or complexType elements.

Table 6-6 shows the mappings of various XPath expressions for function extractValue. If an XPath expression targets an element, then extractValue retrieves the text node of the element. For example, /PurchaseOrder/PONum and /PurchaseOrder/PONum/text() are handled identically by extractValue: both retrieve the scalar content of PONum.

Table 6-6 XPath Mapping for EXTRACTVALUE

XPath Expression Maps to
/PurchaseOrder

Not supported. Function extractValue can only retrieve values for scalar elements and attributes.

/PurchaseOrder/@PurchaseDate
XMLDATA."PurchaseDate"
/PurchaseOrder/PONum 
XMLDATA."PONum"
/PurchaseOrder[PONum = 2100]
(SELECT TO_XML(x.XMLDATA) FROM DUAL
   WHERE x."PONum" = 2100)
/PurchaseOrder[PONum = 
               2100]/@PurchaseDate
(SELECT x.XMLDATA."PurchaseDate") FROM DUAL 
   WHERE x."PONum" = 2100)
/PurchaseOrder/PONum/text() 
XMLDATA."PONum"
/PurchaseOrder/Item

Not supported. Function extractValue can only retrieve values for scalar elements and attributes.

/PurchaseOrder/Item/Part

Not supported. Function extractValue cannot retrieve multiple scalar values.

/PurchaseOrder/Item/Part/text()

Not supported. Function extractValue cannot retrieve multiple scalar values.


Example 6-15 Rewriting EXTRACTVALUE

Consider this SQL query:

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') FROM purchaseorder
  WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1001;

This query would be rewritten as something like the following:

SELECT p.XMLDATA."PONum" FROM purchaseorder p WHERE p.XMLDATA."PONum" = 1001;

Because it gets rewritten to simple scalar columns, any indexes on attribute PONum can be used to satisfy the query.

Creating Indexes with EXTRACTVALUE

Function extractValue can be used in index expressions. If the expression gets rewritten into scalar columns, then the index is turned into a B*Tree index instead of a function-based index.

Example 6-16 Creating Indexes with EXTRACTVALUE

CREATE INDEX my_po_index ON purchaseorder 
  (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference);

This would would get rewritten into something like the following:

CREATE INDEX my_po_index ON purchaseorder x (x.XMLDATA."Reference"); 

This produces a regular B*Tree index. Unlike a function-based index, the same index can now satisfy queries that target the column, such as the following:

existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;

XPath Rewrite for EXTRACT

SQL function extract retrieves XPath results as XML. For Xpath expressions involving text nodes, extract is rewritten similarly to extractValue.

EXTRACT Mapping with Document Order Maintained

Table 6-7 shows the mapping of various XPath expressions inside extract expressions when document order is preserved (that is, when SYS_XDBPD$ exists and maintainDOM="true" in the XML schema document).

Table 6-7 XPath Mapping for EXTRACT with Document Ordering Preserved

XPath Maps to
/PurchaseOrder
XMLForest(XMLDATA AS "PurchaseOrder")
/PurchaseOrder/@PurchaseDate
CASE WHEN node_existsFoot 1 (XMLDATA.SYS_XDBPD$, 'PurchaseDate')
       THEN XMLElement("", XMLDATA."PurchaseDate") ELSE NULL END;
/PurchaseOrder/PONum
CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PONum')
       THEN XMLElement("PONum", XMLDATA."PONum") ELSE NULL END
/PurchaseOrder[PONum = 2100]
SELECT XMLForest(XMLDATA as "PurchaseOrder") FROM DUAL
  WHERE XMLDATA."PONum" = 2100
/PurchaseOrder
[PONum = 2100]/@PurchaseDate
SELECT CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PurchaseDate')
              THEN XMLElement("", XMLDATA."PurchaseDate")
              ELSE NULL END
  FROM DUAL WHERE XMLDATA."PONum" = 2100
/PurchaseOrder/PONum/text()
XMLElement("", XMLDATA."PONum")
/PurchaseOrder/Item
SELECT XMLAgg(XMLForest(value(it) AS "Item"))
  FROM table(XMLDATA."Item") it
/PurchaseOrder/Item/Part
SELECT XMLAgg(CASE WHEN node_existsFootref 1(p.SYS_XDBPD$, 'Part')
                     THEN XMLForest(p."Part" AS "Part")
                     ELSE NULL END)
  FROM table(XMLDATA."Item") p
/PurchaseOrder/Item/Part/text()
SELECT XMLAgg(XMLElement("", p."Part")) 
  FROM table(XMLDATA."Item") p

Footnote 1 Pseudofunction node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the PD column, to determine whether or not its second argument node exists. It returns true if so, and false if not.

Example 6-17 XPath Mapping for EXTRACT with Document Ordering Preserved

Using the mapping in Table 6-7, consider this query that extracts the PONum element, where the purchase order contains a part with price greater than 2000:

SELECT extract(OBJECT_VALUE, '/PurchaseOrder[Item/Part > 2000]/PONum')
  FROM purchaseorder_table;

This query would become something like the following:

SELECT (SELECT CASE WHEN node_exists(p.XMLDATA.SYS_XDBPD$, 'PONum')
                      THEN XMLElement("PONum", p.XMLDATA."PONum") 
                      ELSE NULL END
          FROM DUAL
          WHERE exists(SELECT NULL FROM table(XMLDATA."Item") p 
                         WHERE  p."Part" > 2000))
  FROM purchaseorder_table p;

EXTRACT Mapping Without Maintaining Document Order

If attribute SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false"), then NULL scalar columns map to simpleType elements that do not exist. Hence you do not need to check for the node existence using attribute SYS_XDBPD$. Table 6-8 shows the mapping for function existsNode in the absence of SYS_XDBPD$.

Table 6-8 XPath Mapping for EXTRACT Without Document Ordering Preserved

XPath Equivalent to
/PurchaseOrder
XMLForest(XMLDATA AS "PurchaseOrder")
/PurchaseOrder/@PurchaseDate
XMLForest(XMLDATA."PurchaseDate" AS "PurchaseDate")
/PurchaseOrder/PONum
XMLForest(XMLDATA."PONum" AS "PONum")
/PurchaseOrder[PONum = 2100]
SELECT XMLForest(XMLDATA AS "PurchaseOrder")
  FROM DUAL WHERE XMLDATA."PONum" = 2100
/PurchaseOrder
  [PONum = 2100]/@PurchaseDate
SELECT XMLForest(XMLDATA."PurchaseDate" AS "PurchaseDate "")
  FROM DUAL WHERE XMLDATA."PONum" = 2100
/PurchaseOrder/PONum/text()
XMLForest(XMLDATA.PONum AS "")
/PurchaseOrder/Item
SELECT XMLAgg(XMLForest(value(p) AS "Item")
  FROM table(XMLDATA."Item") p
/PurchaseOrder/Item/Part
SELECT XMLAgg(XMLForest(p."Part" AS "Part")
  FROM table(XMLDATA."Item") p
/PurchaseOrder/Item/Part/text()
SELECT XMLAgg(XMLForest(p. "Part" AS "Part"))
  FROM table(XMLDATA."Item") p

XPath Rewrite for XMLSEQUENCE

You can use SQL function XMLSequence in conjunction with SQL functions extract and table to unnest XML collection values. When used with schema-based storage, these functions also get rewritten to access the underlying relational collection storage.

For example, this query obtains the price and part numbers of all items in a relational form:

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
       extractValue(value(it), '/Item/Part') AS part,
       extractValue(value(it), '/Item/Price') AS price
  FROM purchaseorder,
       table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it;

PONUM PART                 PRICE
----- -------------------- ---------
      1001 9i Doc Set           2550
      1001 8i Doc Set            350

In this example, SQL function extract returns a fragment containing the list of Item elements. Function XMLSequence converts the fragment into a collection of XMLType values one for each Item element. Function table converts the elements of the collection into rows of XMLType. The XML data returned from table is used to extract the Part and the Price elements.

The applications of functions extract and XMLSequence are rewritten to a simple SELECT operation from the item_nested nested table.

EXPLAIN PLAN
  FOR SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
             extractValue(value(it) , '/Item/Part') AS part,
             extractValue(value(it), '/Item/Price') AS price
        FROM purchaseorder,
             table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it;

Explained

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation                     | Name             |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|   1 |  NESTED LOOPS                 |                  |
|   2 |   TABLE ACCESS FULL           | ITEM_NESTED      |
|   3 |   TABLE ACCESS BY INDEX ROWID | PURCHASEORDER    |
|*  4 |    INDEX UNIQUE SCAN          | SYS_C002973      |
----------------------------------------------------------------------------

Predicate Information (identified by operation id)
--------------------------------------------------
   4 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001100012$")

The EXPLAIN PLAN output shows that the optimizer is able to use a simple nested-loops join between nested table item_nested and table purchaseorder. You can also query the Item values further and create appropriate indexes on the nested table to speed up such queries.

For example, to search on the price to get all the expensive items, we could create an index on the Price column of the nested table. The following EXPLAIN PLAN uses a price index to obtain the list of items and then joins with table purchaseorder to obtain the PONum value.

CREATE INDEX price_index ON item_nested ("Price");

Index created.

EXPLAIN PLAN FOR
  SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
         extractValue(value(it), '/Item/Part') AS part,
         extractValue(value(it), '/Item/Price') AS price
    FROM  purchaseorder,
          table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it
    WHERE extractValue(value(it), '/Item/Price') > 2000;

Explained.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation                     | Name             | 
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|   1 |  NESTED LOOPS                 |                  |
|   2 |   TABLE ACCESS BY INDEX ROWID | ITEM_NESTED      |
|*  3 |    INDEX RANGE SCAN           | PRICE_INDEX      |
|   4 |   TABLE ACCESS BY INDEX ROWID | PURCHASEORDER    |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C002973      |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ITEM_NESTED"."Price">2000)
   5 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001100012$")

XPath Rewrite for UPDATEXML

SQL function updateXML must satisfy the following conditions for it to use rewrite optimization:

  • The XMLType argument must be based on a registered XML schema.

  • The XMLType argument must also be the target of the UPDATE operation. For example:

    UPDATE purchaseorder_table SET OBJECT_VALUE = updateXML(OBJECT_VALUE,...);
    
    
  • XPath arguments must all be different (no duplicates).

  • XPath arguments must otherwise be rewritable, as described in "Which XPath Expressions Are Rewritten?".

  • XPath arguments must target only text nodes or attribute nodes.

  • XPath arguments cannot target nodes that have default values (as defined in the XML schema).

  • XPath arguments must not have a positional predicate (for example, foo[2]).

  • If an XPath argument has a predicate, the predicate must not come before a collection.

    For example, /PurchaseOrder/LineItems[@MyAtt="3"]/LineItem will not be rewritten, because the predicate occurs before the LineItem collection. (This assumes an XML schema where LineItems has an attribute MyAtt.)

  • If an XPath argument references a collection, the collection must be stored as a separate table (varray or nested table), not out of line (REF storage) or in line.

  • If an XPath argument references a collection, the collection must not be scalar (simpleType with maxOccurs > 1).

See Also:

Example 6-2, Example 6-3, Example 3-34, and Example 3-34 for examples of rewriting updateXML expressions

XPath Rewrite for INSERTCHILDXML and DELETEXML

SQL function deleteXML must satisfy the following conditions for it to use rewrite optimization:

  • The XMLType argument must be based on a registered XML schema.

  • The XMLType argument must also be the target of the UPDATE operation. For example:

    UPDATE purchaseorder_table SET OBJECT_VALUE = updateXML(OBJECT_VALUE,...);
    
    
  • XPath arguments must otherwise be rewritable, as described in "Which XPath Expressions Are Rewritten?".

  • The XPath argument must not have a positional predicate (for example, foo[2]).

  • If the XPath argument has a predicate, the predicate must not come before a collection.

    For example, /PurchaseOrder/LineItems[@MyAtt="3"]/LineItem will not be rewritten, because the predicate occurs before the LineItem collection. (This assumes an XML schema where LineItems has an attribute MyAtt.)

  • The XPath argument must target an unbounded collection (element with maxOccurs = "unbounded").

  • The XPath argument must not target a choice of collections, as defined in the XML schema.

  • The parent of the targeted collection must be defined in the XML schema with annotation maintainDOM = "false".

  • If an XPath argument references a collection, the collection must be stored as a separate table (varray or nested table), not out of line (REF storage) or in line.

  • If an XPath argument references a collection, the collection must not be scalar (simpleType with maxOccurs > 1).