Skip Headers
Oracle® XML DB Developer's Guide
11g Release 1 (11.1)

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

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

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

For XML schema-based data that is stored object-relationally (structured storage), when you query that data using XQuery expressions your queries can often be rewritten directly to the underlying object-relational columns. This rewrite of queries can also happen when you use queries with XQuery expressions on certain non-schema-based XMLType views. The optimization process of rewriting XQuery expressions is called XPath rewrite.

This optimization 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 XMLQuery, XMLTable, XMLExists, 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 7-1 XPath Rewrite

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

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

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

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

Note:

XMLDATA is a XMLType pseudocolumn that enables direct access to the underlying object column. See Chapter 4, "XMLType Operations".

If there is an index such as the following created on the Company column, then the preceding query uses that 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 XML schema-based and non-schema-based views. In this chapter, we consider only examples related to XML schema-based tables.

The XPath argument to SQL function updateXML in Example 7-2 is rewritten to the equivalent object relational SQL statement of Example 7-3.

Example 7-2 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
  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 7-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 XPath rewrite over XML schema-based and non-schema-based views.

Where Does XPath Rewrite Occur?

XPath rewrite occurs for the following SQL functions:

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

Example 7-4 SELECT Statement and XPath Rewrite

This example returns the existing purchase orders:

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

Example 7-5 and Example 7-6 show statements that are rewritten to use underlying columns. Example 7-5 deletes all PurchaseOrder elements where the Company is not Oracle.

Example 7-5 DML Statement and XPath Rewrite

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

Example 7-6 creates an index on the Company column.

Example 7-6 CREATE INDEX Statement and XPath Rewrite

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

Because the Company column is stored object-relationally and XPath rewrite occurs, an index is created on the underlying relational column. 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 7-1 lists some of the kinds of XPath expressions that can be translated into underlying SQL queries.

Table 7-1 Sample List of XPath Expressions for Rewrite 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.

Descendant axis (XML schema-based data only):

/PurchaseOrder//Part

Similar to a wildcard expression. The descendant axis is rewritten if it can be mapped to a unique XPath expression and the subsequent element is not involved in a recursive type definition. In some cases, rewriting is possible even if there is a recursive 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) is rewritten into the underlying SQL function. Some XPath functions are 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.

Un-nest operations using XMLSequence

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

When used in a table function call, XMLSequence combined with extract is rewritten to use the underlying ordered collection tables.Foot 1 


Footnote 1 A more readable alternative to using table with XMLSequence is using standard SQL/XML function XMLTable.

Common XML Schema Constructs Supported in XPath Rewrite

In addition to standard XML Schema constructs such as complexType elements and sequences, the following XML Schema constructs are also supported. This is not an exhaustive list.

  • 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 an XPath expression includes nodes with any of the following XML Schema constructs, then the expression is not rewritten:

  • XPath expressions accessing children of elements that contain any content. When nodes contain any content, 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.

  • Data-type operations that cannot be coerced, such as the sum of a Boolean value and a number.

Common Storage Constructs Supported in XPath Rewrite

The following storage constructs are supported for XPath rewrite:

  • Simple numeric types that are mapped to SQL data type RAW.

  • Various date and time types that are mapped to SQL data type TIMESTAMP_WITH_TZ.

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

  • XML functions over XML schema-based and non-schema-based XMLType views, and SQL/XML views.

Unsupported Storage Constructs in XPath Rewrite

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

  • If an XML schema maps part of an element definitions to a SQL CLOB instance, then XPath expressions that traverse such elements are cannot be rewritten

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, then the comparison returns false.

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

<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 is translated to a SQL date comparison, and this 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, however, the comparison uses the rules for comparing SQL values.

To suppress this difference in comparison 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 7-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 XML schema registration creates internal SQL data types. We can now create a table to store the XML values and an ordered collection 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 behavior is to maintain the ordering and DOM fidelity. Hence the data 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 and processing instructions.

Object attribute SYS_XDBPD$ also maintains 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. 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 attribute SYS_XDBPD$, and rewrites queries appropriately.

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

Rewriting XPath Expressions: Mapping Data Types and Path Expressions

This section describes the mapping of XPath expressions to SQL data types and path expressions.

Mapping for a Simple XPath Expression

A rewrite for a simple XPath expression involves accessing the SQL column corresponding to the expression, as shown in Table 7-2.

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


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 column XMLDATA."PONum". However, in this case, XPath rewrite must check for the existence of the element itself, using attribute SYS_XDBPD$ in column XMLDATA.

Mapping of Predicates

XPath predicates are mapped to SQL predicate expressions. The comparison rules of SQL are used instead of the XPath 1.0 semantics for comparison — see "XPath Rewrite Can Change Comparison Semantics".

For example, 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").

Example 7-8 Mapping Predicates

This query is rewritten to the structured (object-relational) equivalent:

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

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 7-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 7-10 Mapping Collection Predicates, Using EXISTSNODE

Consider an XPath that checks if a Purchaseorder has Items whose Price and Part number happen to be the same: /PurchaseOrder[Items/Price = Items/Part]. This maps to a SQL collection expression such as the following:

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 a structured equivalent, similar to this:

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

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 7-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 an unordered table, then the order is indeterminate.

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.

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

Date Format Conversions

Date data types 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 data types, 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 7-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 data types 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 data type 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

This section presents techniques to determine if your XPath expressions are in fact being rewritten.

Using EXPLAIN PLAN with XPath Rewrite

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

If a query evaluation 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 events to understand why XPath rewrite did not occur — see "Using Events with XPath Rewrite".

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 explain plan, you can see that the predicate uses internal columns and picks up the index on the Company element. This shows 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 the EXPLAIN PLAN shows that the predicate contains the original existsNode expression. Also, since the predicate is not rewritten, a full table scan is used instead of an index range scan.

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 7-3 lists the various levels and the corresponding behavior.

Table 7-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 Individual 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 column XMLDATA."PONum", 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 7-4 shows the mapping of various XPath expressions 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 7-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 7-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 7-5 shows the mapping of existsNode in the absence of the SYS_XDBPD$ attribute.

Table 7-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 7-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 7-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 7-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 7-16 Creating Indexes with EXTRACTVALUE

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

This 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 7-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 7-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 7-17 XPath Mapping for EXTRACT with Document Ordering Preserved

Using the mapping in Table 7-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 7-8 shows the mapping for function existsNode in the absence of SYS_XDBPD$.

Table 7-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 un-nest XML collection values.Foot 1  When used with XML 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 SQL functions extract and XMLSequence are rewritten to a simple SELECT operation on the ordered collection table (OCT) item_nested.

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 OCT item_nested and table purchaseorder. You can also query the Item values further and create appropriate indexes on the OCT, 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 OCT. 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 that target elements mapped by an XML schema with maxOccurs <= 1 are rewritten only if the schema annotation maintainDom = false is present.

  • 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-expression argument references a collection, the collection must be stored as a separate ordered collection table or out of line (REF storage); it must not be stored in line.

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

See Also:

Example 7-2, Example 7-3, Example 3-35, and Example 3-35 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 ordered collection 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).



Footnote Legend

Footnote 1: A more readable alternative to using function table with XMLSequence is using standard SQL/XML function XMLTable.