| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
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"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.XPath rewrite occurs for the following SQL functions:
deleteXML
existsNode
extract
extractValue
insertChildXML
updateXML
XMLExists
XMLQuery
XMLSequence
XMLTable
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 PurchaseOrders 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.
An XPath expression can generally be rewritten if all of the following are true:
The SQL function or XMLType method can be rewritten.
SQL functions XMLQuery, XMLTable, XMLExists, extract, existsNode, extractValue, updateXML, insertChildXML, deleteXML, and XMLSequence can be rewritten. Except methods existsNode() and extract(), none of the corresponding XMLType methods can be rewritten.
The XPath expression uses only the descendant axis.
Expressions involving axes (such as parent and sibling) other than descendant cannot be rewritten. Expressions that select attributes, elements, or text nodes can be rewritten. XPath predicates can be rewritten to SQL predicates.
The XML Schema constructs for the XPath expression can be rewritten.
XML Schema constructs such as complex types, enumerated values, lists, inherited (derived) types, and substitution groups can be rewritten.
The storage structure chosen during XML schema registration can be rewritten.
XML data stored object-relationally (structured storage) can be rewritten. Storage of complex types using CLOB instances (hybrid storage) cannot be rewritten.
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:
|
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:
|
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 |
|
Predicates:
|
Predicates in the XPath are rewritten into SQL predicates. |
|
List index (positional predicate):
|
Indexes are rewritten to access the nth item in a collection. These are not rewritten for |
|
Wildcard traversals:
|
If the wildcard can be translated to a unique XPath (for example, |
|
Descendant axis (XML schema-based data only):
|
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
|
Any function from the Oracle XML DB namespace ( |
|
String bind variables inside predicates
|
XPath expressions using SQL bind variables are rewritten if they occur between the concatenation ( |
|
Un-nest operations using
|
When used in a |
Footnote 1 A more readable alternative to using table with XMLSequence is using standard SQL/XML function XMLTable.
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.
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.
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.
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
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.
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.
This section describes the mapping of XPath expressions to SQL data types and path expressions.
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 |
|---|---|
|
|
column |
|
|
column |
|
|
column |
|
|
elements of the collection |
|
|
attribute " |
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.
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;
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.
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.
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.
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 whereelementFormDefault 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 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.
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 writingexistsNode 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.This section presents techniques to determine if your XPath expressions are in fact being rewritten.
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)
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.
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 |
|
|
Level 0x4 |
|
|
Level 0x8 |
|
|
Level 0x10 |
|
|
Level 0x20 |
|
|
Level 0x40 |
|
|
Level 0x80 |
|
|
Level 0x100 |
|
|
Level 0x200 |
|
|
Level 0x4000 |
|
|
Level 0x8000 |
|
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
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}
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;
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.
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.
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
|
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 |
/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 |
/PurchaseOrder/Item/Part |
Not supported. Function |
/PurchaseOrder/Item/Part/text() |
Not supported. Function |
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;
SQL function extract retrieves XPath results as XML. For Xpath expressions involving text nodes, extract is rewritten similarly to extractValue.
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;
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 |
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$")
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 rewritingupdateXML expressionsSQL 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 functiontable with XMLSequence is using standard SQL/XML function XMLTable.