Skip Headers
Oracle® XML DB Developer's Guide
12c Release 1 (12.1)

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

4 XQuery and Oracle XML DB

This chapter describes how to use the XQuery language with Oracle XML DB. It covers Oracle XML DB support for the language, including the SQL*Plus XQUERY command and SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast.

This chapter contains these topics:

Overview of the XQuery Language

XQuery is the W3C language designed for querying and updating XML data. Oracle XML DB supports the following W3C XQuery standards:

  • XQuery 1.0 Recommendation

  • XQuery Update Facility 1.0 Recommendation

  • XQuery and XPath Full Text 1.0 Recommendation

This section presents an overview of the XQuery language. For more information, consult a recent book on the language or refer to the standards documents that define it, all of which are available at http://www.w3c.org/.

XPath Expressions Are XQuery Expressions

The XPath language is a W3C Recommendation for navigating XML documents. It is a subset of the XQuery language: an XPath expression is also an XQuery expression.

XPath models an XML document as a tree of nodes. It provides a set of operations that walk this tree and apply predicates and node-test functions. Applying an XPath expression to an XML document results in a set of nodes. For example, the expression /PO/PONO selects all PONO child elements under the PO root element of a document.

Table 4-1 lists some common constructs used in XPath.

Table 4-1 Common XPath Constructs

XPath Construct Description

/

Denotes the root of the tree in an XPath expression. For example, /PO refers to the child of the root node whose name is PO.

/

Used as a path separator to identify the child element nodes of a given element node. For example, /PurchaseOrder/Reference identifies Reference elements that are children of PurchaseOrder elements that are children of the root element.

//

Used to identify all descendants of the current node. For example, PurchaseOrder//ShippingInstructions matches any ShippingInstructions element under the PurchaseOrder element.

*

Used as a wildcard to match any child node. For example, /PO/*/STREET matches any street element that is a grandchild of the PO element.

[ ]

Used to denote predicate expressions. XPath supports a rich list of binary operators such as or, and, and not. For example, /PO[PONO = 20 and PNAME = "PO_2"]/SHIPADDR selects the shipping address element of all purchase orders whose purchase-order number is 20 and whose purchase-order name is PO_2.

Brackets are also used to denote a position (index). For example, /PO/PONO[2] identifies the second purchase-order number element under the PO root element.

Functions

XPath and XQuery support a set of built-in functions such as substring, round, and not. In addition, these languages provide for extension functions through the use of namespaces. Oracle XQuery extension functions use the namespace prefix ora, for namespace http://xmlns.oracle.com/xdb. See "Oracle XQuery Extension Functions".


An XPath expression must identify a single node or a set of element, text, or attribute nodes. The result of evaluating an XPath expression is never a Boolean expression.

You can select XMLType data using PL/SQL, C, or Java. You can also use XMLType method getNumberVal() to retrieve XML data as a NUMBER value.

Note:

Oracle SQL functions and XMLType methods respect the W3C XPath recommendation, which states that if an XPath expression targets no nodes when applied to XML data, then an empty sequence must be returned. An error must not be raised in this case.

XQuery: A Functional Language Based on Sequences

XQuery is similar to SQL in many ways, but just as SQL is designed for querying structured, relational data, XQuery is designed especially for querying semi-structured, XML data from a variety of data sources. You can use XQuery to query XML data wherever it is found, whether it is stored in database tables, available through Web Services, or otherwise created on the fly. In addition to querying XML data, XQuery can be used to construct XML data. In this regard, XQuery can serve as an alternative or a complement to both XSLT and the other SQL/XML publishing functions, such as XMLElement.

XQuery builds on the Post-Schema-Validation Infoset (PSVI) data model, which unites the XML Information Set (Infoset) data model and the XML Schema type system. XQuery defines a new data model, the XQuery Data Model (XDM), which is based on sequences. Another name for an XQuery sequence is an XDM instance.

XQuery Is About Sequences

XQuery is all about manipulating sequences. This makes XQuery similar to a set-manipulation language, except that sequences are ordered and can contain duplicate items. XQuery sequences differ from the sequences in some other languages in that nested XQuery sequences are always flattened in their effect.

In many cases, sequences can be treated as unordered, to maximize optimization – where this is available, it is under your control. This unordered mode can be applied to join order in the treatment of nested iterations (for), and it can be applied to the treatment of XPath expressions (for example, in /a/b, the matching b elements can be processed without regard to document order).

An XQuery sequence consists of zero or more items, which can be either atomic (scalar) values or XML nodes. Items are typed using a rich type system that is based upon the types of XML Schema. This type system is a major change from that of XPath 1.0, which is limited to simple scalar types such as Boolean, number, and string.

XQuery Is Referentially Transparent

XQuery is a functional language. As such, it consists of a set of possible expressions that are evaluated and whose evaluation returns values (results). The result of evaluating an XQuery expression has two parts, at least one of which is empty: (a) a sequence (an XDM instance) and (b) a pending update list. Informally, the sequence is sometimes spoken of as the expression value, especially when the pending update list is empty, meaning that no data updates are involved.

As a functional language, XQuery is also referentially transparent. This means that the same expression evaluated in the same context returns the same value.

Exceptions to this desirable mathematical property include the following:

  • XQuery expressions that derive their value from interaction with the external environment. For example, an expression such as fn:current-time(...) or fn:doc(...) does not necessarily always return the same value, since it depends on external conditions that can change (the time changes; the content of the target document might change).

    In some cases, like that of fn:doc, XQuery is defined to be referentially transparent within the execution of a single query: within a query, each invocation of fn:doc with the same argument results in the same document.

  • XQuery expressions that are defined to be dependent on the particular XQuery language implementation. The result of evaluating such expressions might vary between implementations. Function fn:doc is an example of a function that is essentially implementation-defined.

Note that XQuery Update is not in the list; it does not present an exception to referential transparency. See "XQuery Update Has Side Effects on Your Data".

Referential transparency applies also to XQuery variables: the same variable in the same context has the same value. Functional languages are like mathematics formalisms in this respect and unlike procedural, or imperative, programming languages. A variable in a procedural language is really a name for a memory location; it has a current value, or state, as represented by its content at any time. A variable in a declarative language such as XQuery is really a name for a static value.

XQuery Update Has Side Effects on Your Data

Referential transparency applies to the evaluation of XQuery expressions. It does not imply that this evaluation never has a side effect on your data. In particular, you use XQuery Update to modify your data. That modification is a side effect of evaluating an XQuery updating expression.

The side effect is one thing; the expression value is another. The value returned from evaluation includes the pending update list that describes the updates to carry out. For a given XQuery expression, this description is the same regardless of the context in which evaluation occurs (with the above-mentioned exceptions).

The XQuery Update standard defines how the XDM instances of your data are updated. How those updates are propagated to persistent data stores (for example XMLType tables and columns) is implementation-dependent.

XQuery Update Snapshots

An XQuery expression (query) can call for more than one update operation. For example. XQuery Update performs all such operations for the same query as an atomic operation: either they all succeed or none of them do (if an error is raised).

The unit of change is thus an entire XQuery query. To effect this atomic update behavior, before evaluating your query XQuery Update takes a snapshot of the data (XDM instances) whose modification is called for by the query. It also adds the update operations called for by the query to the pending update list. The snapshot is an evaluation context for an XDM instance that is the update target.

As the last step of XQuery expression evaluation, the pending update list is processed, applying the indicated update operations in an atomic fashion, and terminating the snapshot.

Note that the atomic nature of snapshot semantics means that a set of update operations used in a given query are not necessarily applied in the order written. In fact, the order of applying update operations is fixed and specified by the XQuery Update Feature standard.

This means that an update operation does not see the result of any other update operation for the same query. There is no notion of an intermediate or interim update state – all updates for a query are applied together, atomically.

Oracle XML Update Functions (Deprecated) Do Not Use Snapshot Semantics

The deprecated Oracle SQL functions for updating XML data (updateXML and so on) do not use snapshot semantics. This means that if an expression has multiple such function calls they are processed in applicative order (innermost first), and the result of applying one such function is seen by the updating functions applied after it.

This is an important behavior difference between the Oracle updating functions and XQuery Update functions. Besides the semantic difference, there is also a performance difference: in general, the atomic updating of XQuery Update performs better than the incremental updating of the Oracle-specific functions.

XQuery Full Text Provides Full-Text Search

The XQuery and XPath Full Text 1.0 Recommendation (XQuery Full Text) defines XQuery support for full-text searches in queries. It defines full-text selection operators that perform the search and return instances of the AllMatches model, which complements the XQuery Data Model (XDM). An AllMatches instance describes all possible solutions to a full-text query for a given search context item. Each solution is described by a Match instance, which contains the search-context tokens (StringInclude instances) that must be included and those (StringExclude instances) that must be excluded.

In short, XQuery Full Text adds a full-text contains expression to the XQuery language. You use such an expression in your query to search the text of element nodes and their descendent elements (you can also search the text of attribute nodes).

XQuery Expressions

XQuery expressions are case-sensitive. An XQuery expression is either a simple expression or an updating expression, the latter being an expression that represents data modification. More precisely, these are the possible XQuery expressions:

  • Basic updating expression – an insert, delete, replace, or rename expression, or a call to an updating function (see the XQuery Update Facility 1.0 Recommendation).

  • Updating expression – a basic updating expression or an expression (other than a transform expression) that contains another updating expression (this is a recursive definition).

  • Simple expression – An XQuery 1.0 expression. It does not call for any updating.

The pending update list that results from evaluating a simple expression is empty. The sequence value that results from evaluating an updating expression is empty.

Simple expressions include the following:

  • Primary expression – literal, variable, or function application. A variable name starts with a dollar-sign ($) – for example, $foo. Literals include numerals, strings, and character or entity references.

  • XPath expression – Any XPath expression. The XPath 2.0 standard is a subset of XQuery.

  • FLWOR expression – The most important XQuery expression, composed of the following, in order, from which FLWOR takes its name: for, let, where, order by, return.

  • XQuery sequence – The comma (,) constructor creates sequences. Sequence-manipulating functions such as union and intersect are also available. All XQuery sequences are effectively flat: a nested sequence is treated as its flattened equivalent. Thus, for instance, (1, 2, (3, 4, (5), 6), 7) is treated as (1, 2, 3, 4, 5, 6, 7). A singleton sequence, such as (42), acts the same in most XQuery contexts as does its single item, 42. Remember that the result of any XQuery expression is a sequence.

  • Direct (literal) constructions – XML element and attribute syntax automatically constructs elements and attributes: what you see is what you get. For example, the XQuery expression <a>33</a> constructs the XML element <a>33</a>.

  • Computed (dynamic) constructions – You can construct XML data at run time using computed values. For example, the following XQuery expression constructs this XML data: <foo toto="5"><bar>tata titi</bar> why? </foo>.

    <foo>attribute toto {2+3},
         element bar {"tata", "titi"},
         text {" why? "}</foo>
    

    In this example, element foo is a direct construction; the other constructions are computed. In practice, the arguments to computed constructors are not literals (such as toto and "tata"), but expressions to be evaluated (such as 2+3). Both the name and the value arguments of an element or attribute constructor can be computed. Braces ({, }) are used to mark off an XQuery expression to be evaluated.

  • Conditional expression – As usual, but remember that each part of the expression is itself an arbitrary expression. For instance, in this conditional expression, each of these subexpressions can be any XQuery expression: something, somethingElse, expression1, and expression2.

     if (something < somethingElse) then expression1 else expression2
    
  • Arithmetic, relational expression – As usual, but remember that each relational expression returns a (BooleanFoot 1 ) value. Examples:

    2 + 3
    42 < $a + 5
    (1, 4) = (1, 2)
    5 > 3 eq true()
    
  • Quantifier expression – Universal (every) and existential (some) quantifier functions provide shortcuts to using a FLWOR expression in some cases. Examples:

    every $foo in doc("bar.xml")//Whatever satisfies $foo/@bar > 42
    some $toto in (42, 5), $titi in (123, 29, 5) satisfies $toto = $titi
    
  • Regular expression – XQuery regular expressions are based on XML Schema 1.0 and Perl. (See Support for XQuery Functions and Operators.)

  • Type expression – An XQuery expression that represents an XQuery type. Examples: item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string.Foot 2 

    Type expressions can have occurrence indicators: ? (optional: zero or one), * (zero or more), + (one or more). Examples: document-node(element())*, item()+, attribute()?.

    XQuery also provides operators for working with types. These include cast as, castable as, treat as, instance of, typeswitch, and validate. For example, "42" cast as xs:integer is an expression whose value is the integer 2. (It is not, strictly speaking, a type expression, because its value does not represent a type.)

  • Full-text contains expression – An XQuery expression that represents a full-text search. This expression is provided by the XQuery and XPath Full Text 1.0 Recommendation. A full-text contains expression (FTContainsExpr) supported by Oracle has these parts: a search context that specifies the items to search, and a full-text selection that filters those items, selecting matches.

    The selection part is itself composed of the following:

    • Tokens and phrases used for matching.

    • Optional match options, such as the use of stemming.

    • Optional Boolean operators for combining full-text selections.

    • Optional constraint operators, such as positional filters (e.g. ordered window).

    See "Support for XQuery Full Text".

FLWOR Expressions

Just as for XQuery in general, there is a lot to learn about FLWOR expressions in particular. This section provides a brief overview.

FLWOR is the most general expression syntax in XQuery. FLWOR (pronounced "flower") stands for for, let, where, order by, and return. A FLWOR expression has at least one for or let clause and a return clause; single where and order by clauses are optional. Only the return clause can contain an updating expression; the other clauses cannot.

  • for – Bind one or more variables each to any number of values, in turn. That is, for each variable, iterate, binding the variable to a different value for each iteration.

    At each iteration, the variables are bound in the order they appear, so that the value of a variable $earlier that is listed before a variable $later in the for list, can be used in the binding of variable $later. For example, during its second iteration, this expression binds $i to 4 and $j to 6 (2+4):

     for $i in (3, 4), $j in ($i, 2+$i)
    
  • let – Bind one or more variables.

    Just as with for, a variable can be bound by let to a value computed using another variable that is listed previously in the binding list of the let (or an enclosing for or let). For example, this expression binds $j to 5 (3+2):

    let $i := 3, $j := $i + 2
    
  • where – Filter the for and let variable bindings according to some condition. This is similar to a SQL WHERE clause.

  • order by – Sort the result of where filtering.

  • return – Construct a result from the ordered, filtered values. This is the result of the FLWOR expression as a whole. It is a flattened sequence.

    If the return clause contains an updating expression then that expression is evaluated for each tuple generated by the other clauses. The pending update lists from these evaluations are then merged as the result of the FLWOR expression.

Expressions for and let act similarly to a SQL FROM clause. Expression where acts like a SQL WHERE clause Expression order by is similar to ORDER BY in SQL. Expression return is like SELECT in SQL. Except for the two keywords whose names are the same in both languages (where, order by), FLWOR clause order is more or less opposite to the SQL clause order, but the meanings of the corresponding clauses are quite similar.

Note that using a FLWOR expression (with order by) is the only way to construct an XQuery sequence in any order other than document order.

Overview of XQuery in Oracle XML DB

Oracle XML DB support for the XQuery language is provided through a native implementation of SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast. As a convenience, SQL*Plus command XQUERY is also provided, which lets you enter XQuery expressions directly — in effect, this command turns SQL*Plus into an XQuery command-line interpreter.

Oracle XML DB compiles XQuery expressions that are passed as arguments to SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast. This compilation produces SQL query blocks and operator trees that use SQL/XML functions and XPath functions. A SQL statement that includes XMLQuery, XMLTable, XMLExists, or XMLCast is compiled and optimized as a whole, leveraging both relational database and XQuery-specific optimization technologies. Depending on the XML storage and indexing methods used, XPath functions can be further optimized. The resulting optimized operator tree is executed in a streaming fashion.

Note:

Oracle XML Developer's Kit (XDK) supports XQuery on the mid-tier. You do not need access to Oracle Database to use XQuery. XDK lets you evaluate XQuery expressions using XQuery API for Java (XQJ).

See Also:

When To Use XQuery

You can use XQuery to do many of the same things that you might do using the SQL/XML generation functions or XSLT; there is a great deal of overlap. The decision to use one or the other tool to accomplish a given task can be based on many considerations, most of which are not specific to Oracle Database. Please consult external documentation on this general question.

A general pattern of use is that XQuery is often used when the focus is the world of XML data, and the SQL/XML generation functions (XMLElement, XMLAgg, and so on) are often used when the focus is the world of relational data.

Other things being equal, if a query constructs an XML document from fragments extracted from existing XML documents, then it is likely that an XQuery FLOWR expression is simpler (simplifying code maintenance) than extracting scalar values from relational data and constructing appropriate XML data using SQL/XML generation functions. If, instead, a query constructs an XML document from existing relational data, the SQL/XML generation functions can often be more suitable.

With respect to Oracle XML DB, you can expect the same general level of performance using the SQL/XML generation functions as with XMLQuery and XMLTable — all are subject to rewrite optimizations.

Predefined XQuery Namespaces and Prefixes

The following namespaces and prefixes are predefined for use with XQuery in Oracle XML DB:

Table 4-2 Predefined Namespaces and Prefixes

Prefix Namespace Description

ora

http://xmlns.oracle.com/xdb

Oracle XML DB namespace

local

http://www.w3.org/2003/11/xpath-local-functions

XPath local function declaration namespace

fn

http://www.w3.org/2003/11/xpath-functions

XPath function namespace

xml

http://www.w3.org/XML/1998/namespace

XML namespace

xs

http://www.w3.org/2001/XMLSchema

XML Schema namespace

xsi

http://www.w3.org/2001/XMLSchema-instance

XML Schema instance namespace


You can use these prefixes in XQuery expressions without first declaring them in the XQuery-expression prolog. You can redefine any of them except xml in the prolog. All of these prefixes except ora are predefined in the XQuery standard.

SQL/XML Functions XMLQUERY, XMLTABLE, XMLExists, and XMLCast

SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast are defined by the SQL/XML standard as a general interface between the SQL and XQuery languages. As is the case for the other SQL/XML functions, these functions let you take advantage of the power and flexibility of both SQL and XML. Using these functions, you can construct XML data using relational data, query relational data as if it were XML, and construct relational data from XML data.

SQL functions XMLExists and XMLCast are documented elsewhere in this manual. This section presents functions XMLQuery and XMLTable, but many of the examples in this chapter use also XMLExists, and XMLCast. In terms of typical use:

  • XMLQuery and XMLCast are typically used in a SELECT list.

  • XMLTable is typically used in a SQL FROM clause.

  • XMLExists is typically used in a SQL WHERE clause.

Both XMLQuery and XMLTable evaluate an XQuery expression. In the XQuery language, an expression always returns a sequence of items. Function XMLQuery aggregates the items in this sequence to return a single XML document or fragment. Function XMLTable returns a SQL table whose rows each contain one item from the XQuery sequence.

See Also:

XMLQUERY SQL/XML Function in Oracle XML DB

You use SQL/XML function XMLQuery to construct or query XML data. This function takes as arguments an XQuery expression, as a string literal, and an optional XQuery context item, as a SQL expression. The context item establishes the XPath context in which the XQuery expression is evaluated. Additionally, XMLQuery accepts as arguments any number of SQL expressions whose values are bound to XQuery variables during the XQuery expression evaluation. The function returns the result of evaluating the XQuery expression, as an XMLType instance.

Figure 4-1 XMLQUERY Syntax

Description of Figure 4-1 follows
Description of "Figure 4-1 XMLQUERY Syntax"

XML_passing_clause ::= 

Description of xml_passing_clause.gif follows
Description of the illustration xml_passing_clause.gif

  • XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string.

  • The XML_passing_clause is the keyword PASSING followed by one or more SQL expressions (expr) that each return an XMLType instance or an instance of a SQL scalar data type (that is, not an object or collection data type). Each expression (expr) can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. All but possibly one of the expressions must each be followed by the keyword AS and an XQuery identifier. The result of evaluating each expr is bound to the corresponding identifier for the evaluation of XQuery_string. If there is an expr that is not followed by an AS clause, then the result of evaluating that expr is used as the context item for evaluating XQuery_string. Oracle XML DB supports only passing BY VALUE, not passing BY REFERENCE, so the clause BY VALUE is implicit and can be omitted.

  • RETURNING CONTENT indicates that the value returned by an application of XMLQuery is an instance of parameterized XML type XML(CONTENT), not parameterized type XML(SEQUENCE). It is a document fragment that conforms to the extended Infoset data model. As such, it is a single document node with any number of children. The children can each be of any XML node type; in particular, they can be text nodes.

    Oracle XML DB supports only the RETURNING CONTENT clause of SQL/XML function XMLQuery; it does not support the RETURNING SEQUENCE clause.

You can pass an XMLType column, table, or view as the context-item argument to function XMLQuery — see, for example, Example 4-10.

To query a relational table or view as if it were XML data, without having to first create a SQL/XML view on top of it, use XQuery function fn:collection within an XQuery expression, passing as argument a URI that uses the URI-scheme name oradb together with the database location of the data. See "URI Scheme oradb: Querying Table or View Data with XQuery".

Note:

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL functions extract and extractValue to do some of what can be done better using SQL/XML functions XMLQuery and XMLCast. SQL functions extract and extractValue are deprecated in Oracle Database 11g Release 2.

See Also:

Oracle Database SQL Language Reference for reference information about SQL/XML function XMLQuery in Oracle Database

XMLTABLE SQL/XML Function in Oracle XML DB

You use SQL/XML function XMLTable to decompose the result of an XQuery-expression evaluation into the relational rows and columns of a new, virtual table. You can then insert the virtual table into a pre-existing database table, or you can query it using SQL — in a join expression, for example (see Example 4-11). You use XMLTable in a SQL FROM clause.

Figure 4-2 XMLTABLE Syntax

Description of Figure 4-2 follows
Description of "Figure 4-2 XMLTABLE Syntax"

XML_namespaces_clause ::= 

Description of xml_namespaces_clause.gif follows
Description of the illustration xml_namespaces_clause.gif

XMLTABLE_options ::= 

Description of xmltable_options.gif follows
Description of the illustration xmltable_options.gif

XML_passing_clause ::= 

Description of xml_passing_clause.gif follows
Description of the illustration xml_passing_clause.gif

XML_table_column ::= 

Description of xml_table_column.gif follows
Description of the illustration xml_table_column.gif

  • XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string. The value of the expression serves as input to the XMLTable function; it is this XQuery result that is decomposed and stored as relational data.

  • The optional XMLNAMESPACES clause contains XML namespace declarations that are referenced by XQuery_string and by the XPath expression in the PATH clause of XML_table_column.

  • The XML_passing_clause is the keyword PASSING followed by one or more SQL expressions (expr) that each return an XMLType instance or an instance of a SQL scalar data type (that is, not an object or collection data type). Each expression (expr) can be a table or view column value, a PL/SQL variable, or a bind variables with proper casting. All but possibly one of the expressions must each be followed by the keyword AS and an XQuery identifier. The result of evaluating each expr is bound to the corresponding identifier for the evaluation of XQuery_string. If there is an expr that is not followed by an AS clause, then the result of evaluating that expr is used as the context item for evaluating XQuery_string. Oracle XML DB supports only passing BY VALUE, not passing BY REFERENCE, so the clause BY VALUE is implicit and can be omitted.

  • The optional COLUMNS clause defines the columns of the virtual table to be created by XMLTable.

    • If you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudo-column, named COLUMN_VALUE.

    • FOR ORDINALITY specifies that column is to be a column of generated row numbers (SQL data type NUMBER). There must be at most one FOR ORDINALITY clause.

    • For each resulting column except the FOR ORDINALITY column, you must specify the column data type, which can be XMLType or any other SQL data type (called datatype in the syntax description).

    • For data type XMLType, if you also include the specification (SEQUENCE) BY REF then a reference to the source data targeted by the PATH expression (string) is returned as the column content. Otherwise, column contains a copy of that targeted data.

      Returning the XMLType data by reference lets you specify other columns whose paths target nodes in the source data that are outside those targeted by the PATH expression for column. See Example 4-15.

    • The optional PATH clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content. You can use multiple PATH clauses to split the XQuery result into different virtual-table columns.

      If you omit PATH, then the XQuery expression column is assumed. For example, these two expressions are equivalent:

      XMLTable(... COLUMNS foo)
      XMLTable(... COLUMNS foo PATH 'FOO')
      

      The XQuery expression string must represent a relative path; it is relative to the path XQuery_string.

    • The optional DEFAULT clause specifies the value to use when the PATH expression results in an empty sequence (or NULL). Its expr is an XQuery expression that is evaluated to produce the default value.

See Also:

Oracle Database SQL Language Reference for reference information about SQL/XML function XMLTable in Oracle Database

Note:

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function XMLSequence within a SQL TABLE collection expression, that is, TABLE (XMLSequence(...)), to do some of what can be done better using SQL/XML function XMLTable. Function XMLSequence is deprecated in Oracle Database 11g Release 2.

See Oracle Database SQL Language Reference for information about the SQL TABLE collection expression.

XMLEXISTS SQL/XML Function in Oracle XML DB

Figure 4-3 describes the syntax for SQL/XML standard function XMLExists. This function checks whether a given XQuery expression returns a non-empty XQuery sequence. If so, the function returns TRUE. Otherwise, it returns FALSE.

Figure 4-3 XMLExists Syntax

Description of Figure 4-3 follows
Description of "Figure 4-3 XMLExists Syntax"

XML_passing_clause ::=

Description of xml_passing_clause.gif follows
Description of the illustration xml_passing_clause.gif

  • XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string. It can contain XQuery variables that you bind using the XQuery PASSING clause (XML_passing_clause in the syntax diagram). The predefined namespace prefixes recognized for SQL/XML function XMLQuery are also recognized in XQuery_string — see "Predefined XQuery Namespaces and Prefixes".

  • The XML_passing_clause is the keyword PASSING followed by one or more SQL expressions (expr) that each return an XMLType instance or an instance of a SQL scalar data type. All but possibly one of the expressions must each be followed by the keyword AS and an XQuery identifier. The result of evaluating each expr is bound to the corresponding identifier for the evaluation of XQuery_string. If there is an expr that is not followed by an AS clause, then the result of evaluating that expr is used as the context item for evaluating XQuery_string. Oracle XML DB supports only passing BY VALUE, not passing BY REFERENCE, so the clause BY VALUE is implicit and can be omitted.

If an XQuery expression such as /PurchaseOrder/Reference or /PurchaseOrder/Reference/text() targets a single node, then XMLExists returns true for that expression. If XMLExists is called with an XQuery expression that locates no nodes, then XMLExists returns false.

Function XMLExists can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.

Note:

Oracle XML DB limits the use of XMLExists to a SQL WHERE clause or CASE expression. If you need to use XMLExists in a SELECT list, then wrap it in a CASE expression:
CASE WHEN XMLExists(...) THEN 'TRUE' ELSE 'FALSE' END

Example 4-1 uses SQL/XML standard function XMLExists to select rows with SpecialInstructions set to Expedite.

Example 4-1 Finding a Node Using SQL/XML Function XMLExists

SELECT OBJECT_VALUE
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
OBJECT_VALUE
--------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 
13 rows selected.

You can create function-based indexes using SQL/XML function XMLExists to speed up the execution. You can also create an XMLIndex index to help speed up arbitrary XQuery searching.

Note:

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function existsNode to do some of what can be done better using SQL/XML function XMLExists. Function existsNode is deprecated in Oracle Database 11g Release 2. The two functions differ in these important ways:
  • Function existsNode returns 0 or 1. Function XMLExists returns a Boolean value, TRUE or FALSE.

  • You can use existsNode in a query SELECT list. You cannot use XMLExists directly in a SELECT list, but you can use XMLExists within a CASE expression in a SELECT list.

XMLCAST SQL/XML Function in Oracle XML DB

Figure 4-4 describes the syntax for SQL/XML standard function XMLCast.

Figure 4-4 XMLCast Syntax

Description of Figure 4-4 follows
Description of "Figure 4-4 XMLCast Syntax"

SQL/XML standard function XMLCast casts its first argument to the scalar SQL data type specified by its second argument. The first argument is a SQL expression that is evaluated. Any of the following SQL data types can be used as the second argument:

  • NUMBER

  • VARCHAR2

  • CHAR

  • CLOB

  • BLOB

  • REF XMLTYPE

  • any SQL date or time data type

Note:

Unlike the SQL/XML standard, Oracle XML DB limits the use of XMLCast to cast XML to a SQL scalar data type. Oracle XML DB does not support casting XML to XML or from a scalar SQL type to XML.

The result of evaluating the first XMLCast argument is an XML value. It is converted to the target SQL data type by using the XQuery atomization process and then casting the XQuery atomic values to the target data type. If this conversion fails, then an error is raised. If conversion succeeds, the result returned is an instance of the target data type.

The query in Example 4-2 extracts the scalar value of node Reference.

Example 4-2 Extracting the Scalar Value of an XML Fragment Using XMLCAST

SELECT XMLCast(XMLQuery('/PurchaseOrder/Reference' PASSING OBJECT_VALUE
                                                   RETURNING CONTENT)
               AS VARCHAR2(100)) "REFERENCE"
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
REFERENCE
----------------------------
AMCEWEN-20021009123336271PDT
SKING-20021009123336321PDT
AWALSH-20021009123337303PDT
JCHEN-20021009123337123PDT
AWALSH-20021009123336642PDT
SKING-20021009123336622PDT
SKING-20021009123336822PDT
AWALSH-20021009123336101PDT
WSMITH-20021009123336412PDT
AWALSH-20021009123337954PDT
SKING-20021009123338294PDT
WSMITH-20021009123338154PDT
TFOX-20021009123337463PDT
 
13 rows selected.

Note:

  • Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function extractValue to do some of what can be done better using SQL/XML functions XMLQuery and XMLCast. Function extractValue is deprecated in Oracle Database 11g Release 2.

  • Function extractValue raises an error when its XPath expression argument matches multiple text nodes. XMLCast applied to an XMLQuery result returns the concatenation of the text nodes — it does not raise an error.

URI Scheme oradb: Querying Table or View Data with XQuery

You can use XQuery functions fn:doc and fn:collection to query resources in Oracle XML DB Repository — see"Querying XML Data in Oracle XML DB Repository Using XQuery". This section is about using XQuery function fn:collection to query data in database tables and views.

To do this, you pass function fn:collection a URI argument that specifies the table or view to query. The Oracle URI scheme oradb identifies this usage: without it, the argument is treated as a repository location.

The table or view that is queried can be relational or of type XMLType. If relational, its data is converted on the fly and treated as XML. The result returned by fn:collection is always an XQuery sequence.

  • For an XMLType table, the root element of each XML document returned by fn:collection is the same as the root element of an XML document in the table.

  • For a relational table, the root element of each XML document returned by fn:collection is ROW. The children of the ROW element are elements with the same names (uppercase) as columns of the table. The content of a child element corresponds to the column data. That content is an XML element if the column is of type XMLType; otherwise (the column is a scalar type), the content is of type xs:string.

The format of the URI argument passed to fn:collection is as follows:

  • For an XMLType or relational table or view, TABLE, in database schema DB-SCHEMA:

    oradb:/DB-SCHEMA/TABLE/
    

    You can use PUBLIC for DB-SCHEMA if TABLE is a public synonym or TABLE is a table or view that is accessible to the database user currently logged in.

  • For an XMLType column in a relational table or view:

    oradb:/DB-SCHEMA/REL-TABLE/ROWPRED/X-COL
    

    REL-TABLE is a relational table or view; PRED is an XPath predicate that does not involve any XMLType columns; and X-COL is an XMLType column in REL-TABLE. PRED is optional; DB-SCHEMA, REL-TABLE, and X-COL are required.

Optional XPath predicate PRED must satisfy the following conditions:

  • It does not involve any XMLType columns.

  • It involves only conjunctions (and) and disjunctions (or) of general equality and inequality comparisons (=, !=, >, <, >=, and <=).

  • For each comparison operation: Either both sides name (non-XML) columns in REL-TABLE or one side names such a column and the other is a value of the proper type, as specified in Table 4-3. Use of any other type raises an error.

Table 4-3 oradb Expressions: Column Types for Comparisons

Relational Column Type XQuery Value Type

VARCHAR2, CHAR

xs:string or string literal

NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE

xs:decimal, xs:float, xs:double, or numeric literal

DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE

xs:date, xs:time, or xs:dateTime

INTERVAL YEAR TO MONTH

xs:yearMonthDuration

INTERVAL DAY TO SECOND

xs:dayTimeDuration

RAW

xs:hexBinary

ROWID

xs:string or string literal


For example, this XQuery expression represents all XML documents in XMLType column warehouse_spec of table oe.warehouses, for the rows where column warehouse_id has a value less than 6:

fn:collection('oradb:/OE/WAREHOUSES/ROW[WAREHOUSE_ID < 6]/WAREHOUSE_SPEC')

Oracle XQuery Extension Functions

Oracle XML DB adds some XQuery functions to those provided in the W3C standard. These additional functions are in the Oracle XML DB namespace, http://xmlns.oracle.com/xdb, which uses the predefined prefix ora. This section describes these Oracle extension functions.

Note:

Prior to Oracle Database 12c Release 1, standard XQuery functions fn:matches and fn:replace were not supported, and Oracle XML DB provided these Oracle XQuery functions to use in their stead: ora:matches and ora:replace. These Oracle XQuery functions are deprecated in Oracle Database 12c Release 1 – use the standard XQuery functions (namespace prefix fn) instead.

ora:contains XQuery Function

ora:contains Syntax

ora:contains (input_text, text_query [, policy_name] [, policy_owner])

Oracle XQuery and XPath function ora:contains can be used in an XQuery expression in a call to SQL/XML function XMLQuery, XMLTable, or XMLExists. It is used to restrict a structural search with a full-text predicate. Function ora:contains returns a positive integer when the input_text matches text_query (the higher the number, the more relevant the match), and zero otherwise. When used in an XQuery expression (that is not also an XPath expression), the XQuery return type is xs:integer(); when used in an XPath expression outside of an XQuery expression, the XPath return type is number.

Argument input_text must evaluate to a single text node or an attribute. The syntax and semantics of text_query in ora:contains are the same as text_query in contains, with a few restrictions.

ora:sqrt XQuery Function

ora:sqrt Syntax

ora:sqrt (number)

Oracle XQuery function ora:sqrt returns the square root of its numeric argument, which can be of XQuery type xs:decimal, xs:float, or xs:double. The returned value is of the same XQuery type as the argument.

ora:tokenize XQuery Function

ora:tokenize Syntax

ora:tokenize (target_string, match_pattern [, match_parameter])

Oracle XQuery function ora:tokenize lets you use a regular expression to split the input string target_string into a sequence of strings. It treats each substring that matches the regular-expression match_pattern as a separator indicating where to split.

It returns the sequence of tokens as an XQuery value of type xs:string* (a sequence of xs:string values). If target_string is the empty sequence, it is returned. Optional argument match_parameter is a code that qualifies matching: case-sensitivity and so on.

The argument types are as follows:

  • target_stringxs:string?Foot 3 

  • match_patternxs:string

  • match_parameterxs:string

ora:matches XQuery Function (Deprecated)

This Oracle XQuery function is deprecated in Oracle Database 12c Release 1 – use standard XQuery function fn:matches instead.

ora:matches Syntax

ora:matches (target_string, match_pattern [, match_parameter])

Oracle XQuery function ora:matches lets you use a regular expression to match text in a string. It returns true() if its target_string argument matches its regular-expression match_pattern argument and false() otherwise. If target_string is the empty sequence, false() is returned. Optional argument match_parameter is a code that qualifies matching: case-sensitivity and so on.

The behavior of XQuery function ora:matches is the same as that of SQL condition REGEXP_LIKE, but the types of its arguments are XQuery types instead of SQL data types. The argument types are as follows:

  • target_stringxs:string?Foot 4 

  • match_patternxs:string

  • match_parameterxs:string

See Also:

Oracle Database SQL Language Reference for information about SQL condition REGEXP_LIKE

ora:replace XQuery Function (Deprecated)

This Oracle XQuery function is deprecated in Oracle Database 12c Release 1 – use standard XQuery function fn:replace instead.

ora:replace Syntax

ora:replace (target_string, match_pattern, replace_string [, match_parameter])

Oracle XQuery function ora:replace lets you use a regular expression to replace matching text in a string. Each occurrence in target_string that matches regular-expression match_pattern is replaced by replace_string. It returns the new string that results from the replacement. If target_string is the empty sequence, then the empty string ("") is returned. Optional argument match_parameter is a code that qualifies matching: case-sensitivity and so on.

The behavior of XQuery function ora:replace is the same as that of SQL function regexp_replace, but the types of its arguments are XQuery types instead of SQL data types. The argument types are as follows:

  • target_stringxs:string?Foot 5 

  • match_patternxs:string

  • replace_stringxs:string

  • match_parameterxs:string

In addition, ora:replace requires argument replace_string (it is optional in regexp_replace) and it does not use arguments for position and number of occurrences – search starts with the first character and all occurrences are replaced.

See Also:

Oracle Database SQL Language Reference for information about SQL function regexp_replace

Oracle XQuery Extension-Expression Pragmas

The W3C XQuery specification lets an implementation provide implementation-defined extension expressions. An XQuery extension expression is an XQuery expression that is enclosed in braces ({}) and prefixed by an implementation-defined pragma.

The Oracle implementation provides the pragmas described in this section. No other pragmas are recognized than those listed here. Use of any other pragma, or use of any of these pragmas with incorrect pragma content (for example, (#ora:view_on_null something_else #)), raises an error.

In the ora:view_on_null examples here, assume that table null_test has columns a and b of type VARCHAR2(10), and that column b (but not a) is empty.

  • (#ora:child-element-name name #) – Specify the name to use for a child element that is inserted. This applies to XMLType data stored either object-relationally or as binary XML. In general, without this pragma the name of the element to be inserted is unknown at compile time. Specifying the name allows for compile-time optimization, to improve runtime performance.

    As an example, the following SQL statement specifies LineItem as the name of the element node that is inserted as a child of element LineItems. The element data to be inserted is the value of XQuery variable p2, which comes from bind variable :1.

    UPDATE oe.purchaseorder p SET p.OBJECT_VALUE =
      XMLQuery(
        'copy $i :=
           $p1 modify (for $j in $i/PurchaseOrder/LineItems
                         return (#ora:child-element-name LineItem #)
                                {insert node $p2 into $j)
                      return $i'
        PASSING p.OBJECT_VALUE AS "p1", :1 AS "p2" RETURNING CONTENT)
      WHERE XMLQuery(
              '/PurchaseOrder/Reference/text()'
              PASSING p.OBJECT_VALUE RETURNING CONTENT).getStringVal() =
                'EMPTY_LINES';
    
  • (#ora:defaultTable #) – Specify the default table used to store repository data. Use this to improve the performance of repository queries that use Query function fn:doc or fn:collection. See "Using Oracle XQuery Pragma ora:defaultTable".

  • (#ora:invalid_path empty #) – Treat an invalid XPath expression as if its targeted nodes do not exist. For example:

    SELECT XMLQuery('(#ora:invalid_path empty #)
                     {exists($p/PurchaseOrder//NotInTheSchema)}'
                    PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
      FROM oe.purchaseorder p;
    

    The XML schema for table oe.purchaseorder does not allow any such node NotInTheSchema as a descendant of node PurchaseOrder. Without the pragma, the use of this invalid XPath expression would raise an error. But with the pragma, the calling context acts just as if the XPath expression had targeted no nodes. That calling context in this example is XQuery function exists, which returns XQuery Boolean value false when passed an empty node sequence. (XQuery function exists is used in this example only to illustrate the behavior; the pragma is not especially related to function exists.)

  • (#ora:view_on_null empty #) – XQuery function fn:collection returns an empty XML element for each NULL column. For example, the following query returns <ROW><A>x</A><B></B></ROW>:

    SELECT XMLQuery('(#ora:view_on_null empty #)
                     {for $i in fn:collection("oradb:/PUBLIC/NULL_TEST")/ROW 
                      return $i}'
                    RETURNING CONTENT)
      FROM DUAL;
    
  • (#ora:view_on_null null #) – XQuery function fn:collection returns no element for a NULL column. For example, the following query returns <ROW><A>x</A></ROW>:

    SELECT XMLQuery('(#ora:view_on_null null #)
                     {for $i in fn:collection("oradb:/PUBLIC/NULL_TEST")/ROW 
                      return $i}'
                    RETURNING CONTENT)
      FROM DUAL;
    
  • (#ora:no_xmlquery_rewrite #)Foot 6  – Do not optimize XQuery procedure calls in the XQuery expression that follows the pragma; use functional evaluation instead.

    This has the same effect as the SQL hint /*+ NO_XML_QUERY_REWRITE */, but the scope of the pragma is only the XQuery expression that follows it (not an entire SQL statement).

    See Also:

    "Turning Off Use of XMLIndex" for information about optimizer hint NO_XML_QUERY_REWRITE
  • (#ora:xmlquery_rewrite #)Footref 6 – Try to optimize the XQuery expression that follows the pragma. That is, if possible, do not evaluate it functionally.

    As an example of using both ora:no_xmlquery_rewrite and ora:xmlquery_rewrite, in the following query the XQuery expression argument to XMLQuery will in general be evaluated functionally, but the fn:collection subexpressions that are preceded by pragma ora:xmlquery_rewrite will be optimized, if possible.

    SELECT XMLQuery('(#ora:no_xmlquery_rewrite#) (: Do not optimize expression :)
                     {for $i in (#ora:xmlquery_rewrite#) (: Optimize subexp. :)
                                {fn:collection("oradb:/HR/REGIONS")},
                          $j in (#ora:xmlquery_rewrite#) (: Optimize subexpr. :)
                                {fn:collection("oradb:/HR/COUNTRIES")}
                      where $i/ROW/REGION_ID = $j/ROW/REGION_ID
                        and $i/ROW/REGION_NAME = $regionname
                      return $j}'
             PASSING CAST('&REGION' AS VARCHAR2(40)) AS "regionname"
               RETURNING CONTENT)
      AS asian_countries FROM DUAL;
    
  • (#ora:no_schema #) – Do not raise an error if an XQuery Full Text expression is used with XML Schema-based XMLType data. Instead, implicitly cast the data to non XML-Schema-based data. In particular, this means ignore XML Schema data types.

  • (#ora:use_xmltext_idx #) – Use an XML full-text index, if available, to evaluate the query. Do not use an XMLIndex index or streaming evaluation.

Examples: XMLQUERY, XMLTABLE, XMLExists, and XMLCast

XQuery is a very general and expressive language, and SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast combine that power of expression and computation with the strengths of SQL. This section illustrates some of what you can do with these SQL/XML functions.

You typically use XQuery with Oracle XML DB in the following ways. The examples here are organized to reflect these different uses.

Example 4-3 creates Oracle XML DB Repository resources that are used in some of the other examples in this chapter.

Example 4-3 Creating Resources for Examples

DECLARE
  res BOOLEAN;
  empsxmlstring VARCHAR2(300):= 
    '<?xml version="1.0"?>
     <emps>
       <emp empno="1" deptno="10" ename="John" salary="21000"/>
       <emp empno="2" deptno="10" ename="Jack" salary="310000"/>
       <emp empno="3" deptno="20" ename="Jill" salary="100001"/>
     </emps>';
  empsxmlnsstring VARCHAR2(300):=
    '<?xml version="1.0"?>
     <emps xmlns="http://example.com">
       <emp empno="1" deptno="10" ename="John" salary="21000"/>
       <emp empno="2" deptno="10" ename="Jack" salary="310000"/>
       <emp empno="3" deptno="20" ename="Jill" salary="100001"/>
     </emps>';
  deptsxmlstring VARCHAR2(300):=
    '<?xml version="1.0"?>
     <depts>
       <dept deptno="10" dname="Administration"/>
       <dept deptno="20" dname="Marketing"/>
       <dept deptno="30" dname="Purchasing"/>
     </depts>';
BEGIN
  res := DBMS_XDB_REPOS.createResource('/public/emps.xml',   empsxmlstring);
  res := DBMS_XDB_REPOS.createResource('/public/empsns.xml', empsxmlnsstring);
  res := DBMS_XDB_REPOS.createResource('/public/depts.xml',  deptsxmlstring);
END;
/

XQuery Is About Sequences

It is important to keep in mind that XQuery is a general sequence-manipulation language. Its expressions and their results are not necessarily XML data. An XQuery sequence can contain items of any XQuery type, which includes numbers, strings, Boolean values, dates, and various types of XML node (document-node(), element(), attribute(), text(), namespace(), and so on). Example 4-4 provides a sampling.

Example 4-4 XMLQuery Applied to a Sequence of Items of Different Types

SELECT XMLQuery('(1, 2 + 3, "a", 100 to 102, <A>33</A>)'
                RETURNING CONTENT) AS output
  FROM DUAL;

OUTPUT
--------------------------
1 5 a 100 101 102<A>33</A>
 
1 row selected.

Example 4-4 applies SQL/XML function XMLQuery to an XQuery sequence that contains items of several different kinds:

  • an integer literal: 1

  • a arithmetic expression: 2 + 3

  • a string literal: "a"

  • a sequence of integers: 100 to 102

  • a constructed XML element node: <A>33</A>

Example 4-4 also shows construction of a sequence using the comma operator (,) and parentheses ((, )) for grouping.

The sequence expression 100 to 102 evaluates to the sequence (100, 101, 102), so the argument to XMLQuery here is a sequence that contains a nested sequence. The sequence argument is automatically flattened, as is always the case for XQuery sequences. The argument is, in effect, (1, 5, "a", 100, 101, 102, <A>33</A>).

Querying XML Data in Oracle XML DB Repository Using XQuery

This section presents examples of using XQuery with XML data in Oracle XML DB Repository. You use XQuery functions fn:doc and fn:collection to query file and folder resources in the repository, respectively. The examples in this section use XQuery function fn:doc to obtain a repository file that contains XML data, and then bind XQuery variables to parts of that data using for and let FLWOR-expression clauses.

Example 4-5 queries two XML-document resources in Oracle XML DB Repository: /public/emps.xml and /public/depts.xml. It illustrates the use of fn:doc and each of the possible FLWOR-expression clauses.

Example 4-5 FLOWR Expression Using for, let, order by, where, and return

SELECT XMLQuery('for $e in doc("/public/emps.xml")/emps/emp
                 let $d :=
                   doc("/public/depts.xml")//dept[@deptno = $e/@deptno]/@dname
                 where $e/@salary > 100000
                 order by $e/@empno
                 return <emp ename="{$e/@ename}" dept="{$d}"/>'
                RETURNING CONTENT) FROM DUAL;

XMLQUERY('FOR$EINDOC("/PUBLIC/EMPS.XML")/EMPS/EMPLET$D:=DOC("/PUBLIC/DEPTS.XML")
--------------------------------------------------------------------------------
<emp ename="Jack" dept="Administration"></emp><emp ename="Jill" dept="Marketing"
></emp>
 
1 row selected.

In Example 4-5, the various FLWOR clauses perform these operations:

  • for iterates over the emp elements in /public/emps.xml, binding variable $e to the value of each such element, in turn. That is, it iterates over a general list of employees, binding $e to each employee.

  • let binds variable $d to a sequence consisting of all of the values of dname attributes of those dept elements in /public/emps.xml whose deptno attributes have the same value as the deptno attribute of element $e (this is a join operation). That is, it binds $d to the names of all of the departments that have the same department number as the department of employee $e. (It so happens that the dname value is unique for each deptno value in depts.xml.) Note that, unlike for, let never iterates over values; $d is bound only once in this example.

  • Together, for and let produce a stream of tuples ($e, $d), where $e represents an employee and $d represents the names of all of the departments to which that employee belongs —in this case, the unique name of the employee's unique department.

  • where filters this tuple stream, keeping only tuples with employees whose salary is greater than 100,000.

  • order by sorts the filtered tuple stream by employee number, empno (in ascending order, by default).

  • return constructs emp elements, one for each tuple. Attributes ename and dept of these elements are constructed using attribute ename from the input and $d, respectively. Note that the element and attribute names emp and ename in the output have no necessary connection with the same names in the input document emps.xml.

Example 4-6 also uses each of the FLWOR-expression clauses. It shows the use of XQuery functions doc, count, avg, and integer, which are in the namespace for built-in XQuery functions, http://www.w3.org/2003/11/xpath-functions. This namespace is bound to the prefix fn.

Example 4-6 FLOWR Expression Using Built-In Functions

SELECT XMLQuery('for $d in fn:doc("/public/depts.xml")/depts/dept/@deptno
                 let $e := fn:doc("/public/emps.xml")/emps/emp[@deptno = $d]
                 where fn:count($e) > 1
                 order by fn:avg($e/@salary) descending
                 return
                   <big-dept>{$d,
                              <headcount>{fn:count($e)}</headcount>,
                              <avgsal>{xs:integer(fn:avg($e/@salary))}</avgsal>}
                   </big-dept>'
                RETURNING CONTENT) FROM DUAL;

XMLQUERY('FOR$DINFN:DOC("/PUBLIC/DEPTS.XML")/DEPTS/DEPT/@DEPTNOLET$E:=FN:DOC("/P
--------------------------------------------------------------------------------
<big-dept deptno="10"><headcount>2</headcount><avgsal>165500</avgsal></big-dept>
 
1 row selected.

In Example 4-6, the various FLWOR clauses perform these operations:

  • for iterates over deptno attributes in input document /public/depts.xml, binding variable $d to the value of each such attribute, in turn.

  • let binds variable $e to a sequence consisting of all of the emp elements in input document /public/emps.xml whose deptno attributes have value $d (this is a join operation).

  • Together, for and let produce a stream of tuples ($d, $e), where $d represents a department number and $e represents the set of employees in that department.

  • where filters this tuple stream, keeping only tuples with more than one employee.

  • order by sorts the filtered tuple stream by average salary in descending order. The average is computed by applying XQuery function avg (in namespace fn) to the values of attribute salary, which is attached to the emp elements of $e.

  • return constructs big-dept elements, one for each tuple produced by order by. The text() node of big-dept contains the department number, bound to $d. A headcount child element contains the number of employees, bound to $e, as determined by XQuery function count. An avgsal child element contains the computed average salary.

Querying Relational Data Using XQuery and URI Scheme oradb

This section presents examples of using XQuery to query relational table or view data as if it were XML data. The examples use XQuery function fn:collection, passing as argument a URI that uses the URI-scheme name oradb together with the database location of the data.

Example 4-7 uses Oracle XQuery function fn:collection in a FLWOR expression to query two relational tables, regions and countries. Both tables belong to sample database schema HR. The example also passes scalar SQL value Asia to XQuery variable $regionname. Any SQL expression can be evaluated to produce a value passed to XQuery using PASSING. In this case, the value comes from a SQL*Plus variable, REGION. You must cast the value to the scalar SQL data type expected, in this case, VARCHAR2(40).

Example 4-7 Querying Relational Data as XML Using XMLQuery

DEFINE REGION = 'Asia'
SELECT XMLQuery('for $i in fn:collection("oradb:/HR/REGIONS"),
                     $j in fn:collection("oradb:/HR/COUNTRIES")
                   where $i/ROW/REGION_ID = $j/ROW/REGION_ID
                     and $i/ROW/REGION_NAME = $regionname
                   return $j'
                PASSING CAST('&REGION' AS VARCHAR2(40)) AS "regionname"
                RETURNING CONTENT) AS asian_countries
  FROM DUAL;

This produces the following result. (The result is shown here pretty-printed, for clarity.)

ASIAN_COUNTRIES
-----------------------------------------
<ROW>
  <COUNTRY_ID>AU</COUNTRY_ID>
  <COUNTRY_NAME>Australia</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
<ROW>
  <COUNTRY_ID>CN</COUNTRY_ID>
  <COUNTRY_NAME>China</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
<ROW>
  <COUNTRY_ID>HK</COUNTRY_ID>
  <COUNTRY_NAME>HongKong</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
<ROW>
  <COUNTRY_ID>IN</COUNTRY_ID>
  <COUNTRY_NAME>India</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
<ROW>
  <COUNTRY_ID>JP</COUNTRY_ID>
  <COUNTRY_NAME>Japan</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
<ROW>
  <COUNTRY_ID>SG</COUNTRY_ID>
  <COUNTRY_NAME>Singapore</COUNTRY_NAME>
  <REGION_ID>3</REGION_ID>
</ROW>
 
1 row selected.

In Example 4-7, the various FLWOR clauses perform these operations:

  • for iterates over sequences of XML elements returned by calls to fn:collection. In the first call, each element corresponds to a row of relational table hr.regions and is bound to variable $i. Similarly, in the second call to fn:collection, $j is bound to successive rows of table hr.countries. Since regions and countries are not XMLType tables, the top-level element corresponding to a row in each table is ROW (a wrapper element). Iteration over the row elements is unordered.

  • where filters the rows from both tables, keeping only those pairs of rows whose region_id is the same for each table (it performs a join on region_id) and whose region_name is Asia.

  • return returns the filtered rows from table hr.countries as an XML document containing XML fragments with ROW as their top-level element.

Example 4-8 uses fn:collection within nested FLWOR expressions to query relational data.

Example 4-8 Querying Relational Data as XML Using a Nested FLWOR Expression

CONNECT hr
Enter password: password

Connected.

GRANT SELECT ON LOCATIONS TO OE
/
CONNECT oe
Enter password: password

Connected.

SELECT XMLQuery(
         'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
          return <Warehouse id="{$i/WAREHOUSE_ID}">
                   <Location>
                     {for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
                      where $j/LOCATION_ID eq $i/LOCATION_ID 
                      return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
                   </Location>    
                 </Warehouse>'
         RETURNING CONTENT) FROM DUAL;

This query is an example of using nested FLWOR expressions. It accesses relational table warehouses, which is in sample database schema oe, and relational table locations, which is in sample database schema HR. To run this example as user oe, you must first connect as user hr and grant permission to user oe to perform SELECT operations on table locations.

This produces the following result. (The result is shown here pretty-printed, for clarity.)

XMLQUERY('FOR$IINFN:COLLECTION("ORADB:/OE/WAREHOUSES")/ROWRETURN<WAREHOUSEID="{$
--------------------------------------------------------------------------------
<Warehouse id="1">
  <Location>
    <STREET_ADDRESS>2014 Jabberwocky Rd</STREET_ADDRESS>
    <CITY>Southlake</CITY>
    <STATE_PROVINCE>Texas</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="2">
  <Location>
    <STREET_ADDRESS>2011 Interiors Blvd</STREET_ADDRESS>
    <CITY>South San Francisco</CITY>
    <STATE_PROVINCE>California</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="3">
  <Location>
    <STREET_ADDRESS>2007 Zagora St</STREET_ADDRESS>
    <CITY>South Brunswick</CITY>
    <STATE_PROVINCE>New Jersey</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="4">
  <Location>
    <STREET_ADDRESS>2004 Charade Rd</STREET_ADDRESS>
    <CITY>Seattle</CITY>
    <STATE_PROVINCE>Washington</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="5">
  <Location>
    <STREET_ADDRESS>147 Spadina Ave</STREET_ADDRESS>
    <CITY>Toronto</CITY>
    <STATE_PROVINCE>Ontario</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="6">
  <Location>
    <STREET_ADDRESS>12-98 Victoria Street</STREET_ADDRESS>
    <CITY>Sydney</CITY>
    <STATE_PROVINCE>New South Wales</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="7">
  <Location>
    <STREET_ADDRESS>Mariano Escobedo 9991</STREET_ADDRESS>
    <CITY>Mexico City</CITY>
    <STATE_PROVINCE>Distrito Federal,</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="8">
  <Location>
    <STREET_ADDRESS>40-5-12 Laogianggen</STREET_ADDRESS>
    <CITY>Beijing</CITY>
  </Location>
</Warehouse>
<Warehouse id="9">
  <Location>
    <STREET_ADDRESS>1298 Vileparle (E)</STREET_ADDRESS>
    <CITY>Bombay</CITY>
    <STATE_PROVINCE>Maharashtra</STATE_PROVINCE>
  </Location>
</Warehouse>
 
1 row selected.

In Example 4-8, the various FLWOR clauses perform these operations:

  • The outer for iterates over the sequence of XML elements returned by fn:collection: each element corresponds to a row of relational table oe.warehouses and is bound to variable $i. Since warehouses is not an XMLType table, the top-level element corresponding to a row is ROW. The iteration over the row elements is unordered.

  • The inner for iterates, similarly, over a sequence of XML elements returned by fn:collection: each element corresponds to a row of relational table hr.locations and is bound to variable $j.

  • where filters the tuples ($i, $j), keeping only those whose location_id child is the same for $i and $j (it performs a join on location_id).

  • The inner return constructs an XQuery sequence of elements STREET_ADDRESS, CITY, and STATE_PROVINCE, all of which are children of locations-table ROW element $j; that is, they are the values of the locations-table columns of the same name.

  • The outer return wraps the result of the inner return in a Location element, and wraps that in a Warehouse element. It provides the Warehouse element with an id attribute whose value comes from the warehouse_id column of table warehouses.

See Also:

Example 4-19 for the execution plan of Example 4-8

Example 4-9 uses SQL/XML function XMLTable to decompose the result of an XQuery query to produce virtual relational data. The XQuery expression used in this example is identical to the one used in Example 4-8; the result of evaluating the XQuery expression is a sequence of Warehouse elements. Function XMLTable produces a virtual relational table whose rows are those Warehouse elements. More precisely, in this example the value of pseudocolumn COLUMN_VALUE for each virtual-table row is an XML fragment (of type XMLType) with a single Warehouse element.

Example 4-9 Querying Relational Data as XML Using XMLTable

SELECT * 
  FROM XMLTable(
         'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
          return <Warehouse id="{$i/WAREHOUSE_ID}">
                   <Location>
                     {for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
                      where $j/LOCATION_ID eq $i/LOCATION_ID 
                      return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
                   </Location>
                 </Warehouse>');

This produces the same result as Example 4-8, except that each Warehouse element is output as a separate row, instead of all Warehouse elements being output together in a single row.

COLUMN_VALUE
--------------------------------------------------------
<Warehouse id="1">
  <Location>
    <STREET_ADDRESS>2014 Jabberwocky Rd</STREET_ADDRESS>
    <CITY>Southlake</CITY>
    <STATE_PROVINCE>Texas</STATE_PROVINCE>
  </Location>
</Warehouse>
<Warehouse id="2">
  <Location>
    <STREET_ADDRESS>2011 Interiors Blvd</STREET_ADDRESS>
    <CITY>South San Francisco</CITY>
    <STATE_PROVINCE>California</STATE_PROVINCE>
  </Location>
</Warehouse>
. . .
 
9 rows selected.

See Also:

Example 4-20 for the execution plan of Example 4-9

Querying XMLType Data Using XQuery

This section presents examples of using XQuery to query XMLType data.

The query in Example 4-10 passes an XMLType column, warehouse_spec, as context item to XQuery, using function XMLQuery with the PASSING clause. It constructs a Details element for each of the warehouses whose area is greater than 80,000: /Warehouse/Area > 80000.

Example 4-10 Querying an XMLType Column Using XMLQuery PASSING Clause

SELECT warehouse_name, 
       XMLQuery(
         'for $i in /Warehouse 
          where  $i/Area > 80000 
          return <Details>
                   <Docks num="{$i/Docks}"/>
                   <Rail>{if ($i/RailAccess = "Y") then "true" else "false"}
                   </Rail>
                 </Details>'
         PASSING warehouse_spec RETURNING CONTENT) big_warehouses
  FROM oe.warehouses;

This produces the following output:

WAREHOUSE_NAME
--------------
BIG_WAREHOUSES
--------------
Southlake, Texas
 
 
San Francisco
 
 
New Jersey
<Details><Docks num=""></Docks><Rail>false</Rail></Details>
 
Seattle, Washington
<Details><Docks num="3"></Docks><Rail>true</Rail></Details>
 
Toronto
 
 
Sydney
 
 
Mexico City
 
 
Beijing
 
 
Bombay
 
 
9 rows selected.

In Example 4-10, function XMLQuery is applied to the warehouse_spec column in each row of table warehouses. The various FLWOR clauses perform these operations:

  • for iterates over the Warehouse elements in each row of column warehouse_spec (the passed context item): each such element is bound to variable $i, in turn. The iteration is unordered.

  • where filters the Warehouse elements, keeping only those whose Area child has a value greater than 80,000.

  • return constructs an XQuery sequence of Details elements, each of which contains a Docks and a Rail child elements. The num attribute of the constructed Docks element is set to the text() value of the Docks child of Warehouse. The text() content of Rail is set to true or false, depending on the value of the RailAccess attribute of element Warehouse.

The SELECT statement in Example 4-10 applies to each row in table warehouses. The XMLQuery expression returns the empty sequence for those rows that do not match the XQuery expression. Only the warehouses in New Jersey and Seattle satisfy the XQuery query, so they are the only warehouses for which <Details>...</Details> is returned.

Example 4-11 uses SQL/XML function XMLTable to query an XMLType table, oe.purchaseorder, which contains XML Schema-based data. It uses the PASSING clause to provide the purchaseorder table as the context item for the XQuery-expression argument to XMLTable. Pseudocolumn COLUMN_VALUE of the resulting virtual table holds a constructed element, A10po, which contains the Reference information for those purchase orders whose CostCenter element has value A10 and whose User element has value SMCCAIN. The query performs a join between the virtual table and database table purchaseorder.

Example 4-11 Using XMLTABLE with XML Schema-Based Data

SELECT xtab.COLUMN_VALUE
  FROM purchaseorder, XMLTable('for $i in /PurchaseOrder
                                where $i/CostCenter eq "A10"
                                  and $i/User eq "SMCCAIN"
                                return <A10po pono="{$i/Reference}"/>'
                               PASSING OBJECT_VALUE) xtab;
 
COLUMN_VALUE
---------------------------------------------------
<A10po pono="SMCCAIN-20021009123336151PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336341PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337173PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335681PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335470PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336972PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336842PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336512PDT"></A10po>
<A10po pono="SMCCAIN-2002100912333894PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337403PDT"></A10po>
 
10 rows selected.

The PASSING clause of function XMLTable passes the OBJECT_VALUE of XMLType table purchaseorder, to serve as the XPath context. The XMLTable expression thus depends on the purchaseorder table. Because of this, table purchaseorder must appear before the XMLTable expression in the FROM list. This is a general requirement in any situation involving data dependence.

Note:

Whenever a PASSING clause refers to a column of an XMLType table in a query, that table must appear before the XMLTable expression in the query FROM list. This is because the XMLTable expression depends on the XMLType table — a left lateral (correlated) join is needed, to ensure a one-to-many (1:N) relationship between the XMLType table row accessed and the rows generated from it by XMLTable.

Example 4-12 is similar to Example 4-11 in its effect. It uses XMLQuery, instead of XMLTable, to query oe.purchaseorder. These two examples differ in their treatment of the empty sequences returned by the XQuery expression. In Example 4-11, these empty sequences are not joined with the purchaseorder table, so the overall SQL-query result set has only ten rows. In Example 4-12, these empty sequences are part of the overall result set of the SQL query, which contains 132 rows, one for each of the rows in table purchaseorder. All but ten of those rows are empty, and show up in the output as empty lines. To save space here, those empty lines have been removed.

Example 4-12 Using XMLQUERY with XML Schema-Based Data

SELECT XMLQuery('for $i in /PurchaseOrder
                 where $i/CostCenter eq "A10"
                   and $i/User eq "SMCCAIN"
                 return <A10po pono="{$i/Reference}"/>'
                PASSING OBJECT_VALUE
                RETURNING CONTENT)
  FROM purchaseorder;
 
XMLQUERY('FOR$IIN/PURCHASEORDERWHERE$I/COSTCENTEREQ"A10"AND$I/USEREQ"SMCCAIN"RET
--------------------------------------------------------------------------------
<A10po pono="SMCCAIN-20021009123336151PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336341PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337173PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335681PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335470PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336972PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336842PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336512PDT"></A10po>
<A10po pono="SMCCAIN-2002100912333894PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337403PDT"></A10po>
 
132 rows selected.

See Also:

Example 4-21 for the execution plan of Example 4-12

Example 4-13 uses XMLTable clauses PASSING and COLUMNS. The XQuery expression iterates over top-level PurchaseOrder elements, constructing a PO element for each purchase order with cost center A10. The resulting PO elements are then passed to XMLTable for processing.

Example 4-13 Using XMLTABLE with PASSING and COLUMNS Clauses

SELECT xtab.poref, xtab.priority, xtab.contact
  FROM purchaseorder,
       XMLTable('for $i in /PurchaseOrder
                 let $spl := $i/SpecialInstructions
                 where $i/CostCenter eq "A10"
                 return <PO>
                          <Ref>{$i/Reference}</Ref>
                          {if ($spl eq "Next Day Air" or $spl eq "Expedite") then
                             <Type>Fastest</Type>
                           else if ($spl eq "Air Mail") then
                             <Type>Fast</Type>
                           else ()}
                          <Name>{$i/Requestor}</Name>
                        </PO>'
                PASSING OBJECT_VALUE
                COLUMNS poref    VARCHAR2(20) PATH 'Ref',
                        priority VARCHAR2(8)  PATH 'Type' DEFAULT 'Regular',
                        contact  VARCHAR2(20) PATH 'Name') xtab;
 
POREF                PRIORITY CONTACT
-------------------- -------- --------------------
SKING-20021009123336 Fastest  Steven A. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
SMCCAIN-200210091233 Fastest  Samuel B. McCain
JCHEN-20021009123337 Fastest  John Z. Chen
JCHEN-20021009123337 Regular  John Z. Chen
SKING-20021009123337 Regular  Steven A. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
JCHEN-20021009123338 Regular  John Z. Chen
SMCCAIN-200210091233 Regular  Samuel B. McCain
SKING-20021009123335 Regular  Steven X. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
SKING-20021009123336 Regular  Steven A. King
SMCCAIN-200210091233 Fast     Samuel B. McCain
SKING-20021009123336 Fastest  Steven A. King
SKING-20021009123336 Fastest  Steven A. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
JCHEN-20021009123335 Regular  John Z. Chen
SKING-20021009123336 Regular  Steven A. King
JCHEN-20021009123336 Regular  John Z. Chen
SKING-20021009123336 Regular  Steven A. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
SKING-20021009123337 Regular  Steven A. King
SKING-20021009123338 Fastest  Steven A. King
SMCCAIN-200210091233 Regular  Samuel B. McCain
JCHEN-20021009123337 Regular  John Z. Chen
JCHEN-20021009123337 Regular  John Z. Chen
JCHEN-20021009123337 Regular  John Z. Chen
SKING-20021009123337 Regular  Steven A. King
JCHEN-20021009123337 Regular  John Z. Chen
SKING-20021009123337 Regular  Steven A. King
SKING-20021009123337 Regular  Steven A. King
SMCCAIN-200210091233 Fast     Samuel B. McCain
 
32 rows selected.

In Example 4-13, data from the children of PurchaseOrder is used to construct the children of PO, which are Ref, Type, and Name. The content of Type is taken from the content of /PurchaseOrder/SpecialInstructions, but the classes of SpecialInstructions are divided up differently for Type.

Function XMLTable breaks up the result of XQuery evaluation, returning it as three VARCHAR2 columns of a virtual table: poref, priority, and contact. The DEFAULT clause is used to supply a default priority of Regular.

Example 4-13 does not use the clause RETURNING SEQUENCE BY REF, which means that the XQuery sequence returned and then used by the COLUMNS clause is passed by value, not by reference. That is, a copy of the targeted nodes is returned, not a reference to the actual nodes.

When the returned sequence is passed by value, the columns specified in a COLUMNS clause cannot refer to any data that is not in that returned copy. In particular, they cannot refer to data that precedes the targeted nodes in the source data.

To be able to refer to an arbitrary part of the source data from column specifications in a COLUMNS clause, you need to use the clause RETURNING SEQUENCE BY REF, which causes the sequence resulting from the XQuery expression to be returned by reference.

Example 4-14 shows the use of clause RETURNING SEQUENCE BY REF, which allows column reference to refer to a node that is outside the nodes targeted by the XQuery expression. Because the sequence of LineItem nodes is returned by reference, the code has access to the complete tree of nodes, so it can navigate upward and then back down to node Reference.

Example 4-14 Using XMLTABLE with RETURNING SEQUENCE BY REF

SELECT t.*
  FROM purchaseorder,
       XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE
                RETURNING SEQUENCE BY REF
                COLUMNS reference   VARCHAR2(30) PATH '../../Reference',
                        item        VARCHAR2(4)  PATH '@ItemNumber',
                        description VARCHAR2(45) PATH 'Description') t
  WHERE item = 5;
 
REFERENCE                      ITEM DESCRIPTION
------------------------------ ---- ------------------------------------
AMCEWEN-20021009123336171PDT   5    Coup De Torchon (Clean Slate)
AMCEWEN-20021009123336271PDT   5    The Unbearable Lightness Of Being
PTUCKER-20021009123336191PDT   5    The Scarlet Empress
PTUCKER-20021009123336291PDT   5    The Unbearable Lightness Of Being
SBELL-20021009123336231PDT     5    Black Narcissus
SBELL-20021009123336331PDT     5    Fishing With John 1 -3
SKING-20021009123336321PDT     5    The Red Shoes
SMCCAIN-20021009123336151PDT   5    Wages of Fear
SMCCAIN-20021009123336341PDT   5    The Most Dangerous Game
VJONES-20021009123336301PDT    5    Le Trou
 
10 rows selected.

Clause RETURNING SEQUENCE BY REF lets you specify that the result of evaluating the top-level XQuery expression used to generate rows for XMLTable be returned by reference. The same kind of choice is available for the result of evaluating a PATH expression in a COLUMNS clause. To specify that such a result be returned by reference you use XMLType (SEQUENCE) BY REF as the column data type.

Example 4-15 illustrates this. It chains together two XMLTable tables, t1 and t2, returning XML data from the source document by reference:

  • For column reference of the top-level table, t1, because it corresponds to a node outside element LineItem (just as in Example 4-14)

  • For column part of table t1, because it is passed to table t2, whose column item targets data outside node Part

Example 4-15 Using Chained XMLTABLE with Access by Reference

SELECT t1.reference, t2.id, t2.item
  FROM purchaseorder,
       XMLTable('/PurchaseOrder/LineItems' PASSING OBJECT_VALUE
                RETURNING SEQUENCE BY REF
                COLUMNS part XMLType (SEQUENCE) BY REF PATH 'LineItem/Part',
                        reference VARCHAR2(30)         PATH '../Reference') t1,
       XMLTable('.' PASSING t1.part
                RETURNING SEQUENCE BY REF
                COLUMNS id   VARCHAR2(12) PATH '@Id',
                        item NUMBER       PATH '../@ItemNumber') t2;

In table t1, the type used for column part is XMLType (SEQUENCE) BY REF, so that the part data is a reference to the source data targeted by its PATH expression, LineItem/Part. This is needed because the PATH expression for column item in table t2 targets attribute ItemNumber of the parent of element Part, LineItem. Without specifying that part is a reference, it would be a copy of just the Part element, so that using PATH expression ../@ItemNumber would raise an error.

Example 4-16 uses SQL/XML function XMLTable to break up the XML data in an XMLType collection element, LineItem, into separate columns of a virtual table.

Example 4-16 Using XMLTABLE to Decompose XML Collection Elements into Relational Data

SELECT lines.lineitem, lines.description, lines.partid,
       lines.unitprice, lines.quantity
  FROM purchaseorder,
       XMLTable('for $i in /PurchaseOrder/LineItems/LineItem
                 where $i/@ItemNumber >= 8
                  and $i/Part/@UnitPrice > 50
                  and $i/Part/@Quantity > 2
                 return $i'
                PASSING OBJECT_VALUE
                COLUMNS lineitem    NUMBER       PATH '@ItemNumber',
                        description VARCHAR2(30) PATH 'Description',
                        partid      NUMBER       PATH 'Part/@Id',
                        unitprice   NUMBER       PATH 'Part/@UnitPrice',
                        quantity    NUMBER       PATH 'Part/@Quantity') lines; 

LINEITEM DESCRIPTION                           PARTID UNITPRICE QUANTITY
-------- ------------------------------ ------------- --------- --------
      11 Orphic Trilogy                   37429148327        80        3
      22 Dreyer Box Set                   37429158425        80        4
      11 Dreyer Box Set                   37429158425        80        3
      16 Dreyer Box Set                   37429158425        80        3
       8 Dreyer Box Set                   37429158425        80        3
      12 Brazil                           37429138526        60        3
      18 Eisenstein: The Sound Years      37429149126        80        4
      24 Dreyer Box Set                   37429158425        80        3
      14 Dreyer Box Set                   37429158425        80        4
      10 Brazil                           37429138526        60        3
      17 Eisenstein: The Sound Years      37429149126        80        3
      16 Orphic Trilogy                   37429148327        80        4
      13 Orphic Trilogy                   37429148327        80        4
      10 Brazil                           37429138526        60        4
      12 Eisenstein: The Sound Years      37429149126        80        3
      12 Dreyer Box Set                   37429158425        80        4
      13 Dreyer Box Set                   37429158425        80        4
 
17 rows selected.

See Also:

Using Namespaces with XQuery

You can use the XQuery declare namespace declaration in the prolog of an XQuery expression to define a namespace prefix. You can use declare default namespace to establish the namespace as the default namespace for the expression.

Be aware of the following pitfall, if you use SQL*Plus: If the semicolon (;) at the end of a namespace declaration terminates a line, SQL*Plus interprets it as a SQL terminator. To avoid this, you can do one of the following:

  • Place the text that follows the semicolon on the same line.

  • Place a comment, such as (: :), after the semicolon, on the same line.

  • Turn off the recognition of the SQL terminator with SQL*Plus command SET SQLTERMINATOR.

Example 4-17 illustrates use of a namespace declaration in an XQuery expression.

Example 4-17 Using XMLQUERY with a Namespace Declaration

SELECT XMLQuery('declare namespace e = "http://example.com";
ERROR:
ORA-01756: quoted string not properly terminated
 
                 for $i in doc("/public/empsns.xml")/e:emps/e:emp
SP2-0734: unknown command beginning "for $i in ..." - rest of line ignored.
...

-- This works - do not end the line with ";".
SELECT XMLQuery('declare namespace e = "http://example.com"; for
                     $i in doc("/public/empsns.xml")/e:emps/e:emp
                 let $d := 
                   doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
                 where $i/@salary > 100000
                 order by $i/@empno
                 return <emp ename="{$i/@ename}" dept="{$d}"/>'
                RETURNING CONTENT) FROM DUAL;
 
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";FOR$IINDOC("/PUBLIC/EMPSNS.XML"
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>

-- This works too - add a comment after the ";".
SELECT XMLQuery('declare namespace e = "http://example.com";  (: :)
                 for $i in doc("/public/empsns.xml")/e:emps/e:emp
                 let $d := doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
                 where $i/@salary > 100000
                 order by $i/@empno
                 return <emp ename="{$i/@ename}" dept="{$d}"/>'
                RETURNING CONTENT) FROM DUAL;
 
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";(::)FOR$IINDOC("/PUBLIC/EMPSNS.
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>
 
1 row selected.

-- This works too - tell SQL*Plus to ignore the ";".

SET SQLTERMINATOR OFF
 
SELECT XMLQuery('declare namespace e = "http://example.com";
                 for $i in doc("/public/empsns.xml")/e:emps/e:emp
                 let $d :=
                   doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
                 where $i/@salary > 100000
                 order by $i/@empno
                 return <emp ename="{$i/@ename}" dept="{$d}"/>'
                RETURNING CONTENT) FROM DUAL
/
 
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";FOR$IINDOC("/PUBLIC/EMPSNS.XML"
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>

An XQuery namespace declaration has no effect outside of its XQuery expression. To declare a namespace prefix for use in an XMLTable expression outside of the XQuery expression, use the XMLNAMESPACES clause. This clause also covers the XQuery expression argument to XMLTable, eliminating the need for a separate declaration in the XQuery prolog.

In Example 4-18, XMLNAMESPACES is used to define the prefix e for the namespace http://example.com. This namespace is used in the COLUMNS clause and the XQuery expression of the XMLTable expression.

Example 4-18 Using XMLTABLE with the XMLNAMESPACES Clause

SELECT * FROM XMLTable(XMLNAMESPACES ('http://example.com' AS "e"),
                       'for $i in doc("/public/empsns.xml")
                        return $i/e:emps/e:emp'
                       COLUMNS name VARCHAR2(6) PATH '@ename',
                               id   NUMBER      PATH '@empno');

This produces the following result:

NAME           ID
------ ----------
John            1
Jack            2
Jill            3
 
3 rows selected.

It is the presence of qualified names e:ename and e:empno in the COLUMNS clause that necessitates using the XMLNAMESPACES clause. Otherwise, a prolog namespace declaration (declare namespace e = "http://example.com") would suffice for the XQuery expression itself.

Because the same namespace is used throughout the XMLTable expression, a default namespace could be used: XMLNAMESPACES (DEFAULT 'http://example.com'). The qualified name $i/e:emps/e:emp could then be written without an explicit prefix: $i/emps/emp.

Performance Tuning for XQuery

A SQL query that involves XQuery expressions can often be rewritten (optimized) in one or more ways. This optimization is referred to as XML query rewrite or optimization. XPath expressions are a proper subset of XQuery expressions.

XPath rewrite is a subset of XML query rewrite that involves rewriting queries that involve XPath expressions. XPath rewrite includes XMLIndex optimizations, streaming evaluation of binary XML, and rewrite to underlying object-relational or relational structures in the case of object-relational XMLType storage or XMLType views over relational data.

Just as query tuning can improve SQL performance, so it can improve XQuery performance. You tune XQuery performance by choosing appropriate XML storage models and indexes.

As with database queries generally, you determine whether tuning is required by examining the execution plan for a query. If the plan is not optimal, then consult the following documentation for specific tuning information:

In addition, be aware that the following expressions can be expensive to process, so they might add performance overhead when processing large volumes of data:

  • SQL expressions that use the following Oracle SQL functions, which accept XPath expression arguments:

    • appendChildXML (use insertChildXMLafter instead)

    • insertXMLafter (use insertChildXMLafter instead)

    • insertXMLbefore (use insertChildXMLbefore instead)

  • XQuery expressions that use the following axes (use forward and descendent axes instead):

    • ancestor

    • ancestor-or-self

    • descendant-or-self

    • following

    • following-sibling

    • namespace

    • parent

    • preceding

    • preceding-sibling

  • XQuery expressions that involve node identity (for example, using the order-comparison operators << and >>)

The following sections present the execution plans for some of the examples shown earlier in this chapter, to indicate how they are executed.

See Also:

Rule-Based and Cost-Based XQuery Optimization

Several competing optimization possibilities can exist for queries with XQuery expressions, depending on various factors such as the XMLType storage model and indexing that are used.

By default, Oracle XML DB follows a prioritized set of rules to determine which of the possible optimizations should be used for any given query and context. This behavior is referred to as rule-based XML query rewrite.

Alternatively, Oracle XML DB can use cost-based XML query rewrite. In this mode, Oracle XML DB estimates the performance of the various XML optimization possibilities for a given query and chooses the combination that is expected to be most performant.

You can impose cost-based optimization for a given SQL statement by using the optimizer hint /*+ COST_XML_QUERY_REWRITE */.

XQuery Optimization over Relational Data

Example 4-19 shows the optimization of XMLQuery over relational data accessed as XML. Example 4-20 shows the optimization of XMLTable in the same context.

Example 4-19 Optimization of XMLQuery over Relational Data

Here again is the query of Example 4-8, together with its execution plan, which shows that the query has been optimized.

SELECT XMLQuery(
         'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
          return <Warehouse id="{$i/WAREHOUSE_ID}">
                   <Location>
                     {for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
                      where $j/LOCATION_ID eq $i/LOCATION_ID 
                      return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
                   </Location>    
                 </Warehouse>'
         RETURNING CONTENT) FROM DUAL;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3341889589

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |            |     1 |    41 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCATIONS  |     1 |    41 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | LOC_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |            |     1 |     6 |            |          |
|   5 |   TABLE ACCESS FULL          | WAREHOUSES |     9 |    54 |     2   (0)| 00:00:01 |
|   6 |  FAST DUAL                   |            |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("LOCATION_ID"=:B1)
 
18 rows selected.

Example 4-20 Optimization of XMLTable over Relational Data

Here again is the query of Example 4-9, together with its execution plan, which shows that the query has been optimized.

SELECT * 
  FROM XMLTable(
         'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
          return <Warehouse id="{$i/WAREHOUSE_ID}">
                   <Location>
                     {for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
                      where $j/LOCATION_ID eq $i/LOCATION_ID 
                      return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
                   </Location>
                 </Warehouse>');
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1021775546
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     9 |    54 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |            |     1 |    41 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCATIONS  |     1 |    41 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | LOC_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   4 |  TABLE ACCESS FULL           | WAREHOUSES |     9 |    54 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("LOCATION_ID"=:B1)
 
16 rows selected.

XQuery Optimization over XML Schema-Based XMLType Data

Example 4-21 shows the optimization of XMLQuery over an XML schema-based XMLType table. Example 4-22 shows the optimization of XMLTable in the same context.

Example 4-21 Optimization of XMLQuery with Schema-Based XMLType Data

Here again is the query of Example 4-12, together with its execution plan, which shows that the query has been optimized.

SELECT XMLQuery('for $i in /PurchaseOrder
                 where $i/CostCenter eq "A10"
                   and $i/User eq "SMCCAIN"
                 return <A10po pono="{$i/Reference}"/>'
                PASSING OBJECT_VALUE
                RETURNING CONTENT)
  FROM purchaseorder;
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3611789148
 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |   530 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |               |     1 |       |            |          |
|*  2 |   FILTER            |               |       |       |            |          |
|   3 |    FAST DUAL        |               |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| PURCHASEORDER |     1 |   530 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(:B1='SMCCAIN' AND :B2='A10')
   4 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege
              xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
              http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd">
              <read-properties/><read-contents/></privilege>'))=1)
 
22 rows selected.

Example 4-22 Optimization of XMLTable with Schema-Based XMLType Data

Here again is the query of Example 4-16, together with its execution plan, which shows that the query has been optimized. The XQuery result is never materialized. Instead, the underlying storage columns for the XML collection element LineItem are used to generate the overall result set.

SELECT lines.lineitem, lines.description, lines.partid,
       lines.unitprice, lines.quantity
  FROM purchaseorder,
       XMLTable('for $i in /PurchaseOrder/LineItems/LineItem
                 where $i/@ItemNumber >= 8
                   and $i/Part/@UnitPrice > 50
                   and $i/Part/@Quantity > 2
                 return $i'
                PASSING OBJECT_VALUE
                COLUMNS lineitem    NUMBER       PATH '@ItemNumber',
                        description VARCHAR2(30) PATH 'Description',
                        partid      NUMBER       PATH 'Part/@Id',
                        unitprice   NUMBER       PATH 'Part/@UnitPrice',
                        quantity    NUMBER       PATH 'Part/@Quantity') lines;
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     4 |   384 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |       |       |            |          |
|   2 |   NESTED LOOPS               |                |     4 |   384 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | PURCHASEORDER  |     1 |    37 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | SYS_C005478    |    17 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| LINEITEM_TABLE |     3 |   177 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege
              xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
              http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd"><read-prop
              erties/><read-contents/></privilege>'))=1)
   4 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
   5 - filter("SYS_NC00013$">50 AND "SYS_NC00012$">2 AND "ITEMNUMBER">=8 AND
              "SYS_NC_TYPEID$" IS NOT NULL)
 
25 rows selected.

This example traverses table oe.purchaseorder completely. The XMLTable expression is evaluated for each purchase-order document. It is more efficient to have the XMLTable expression, not the purchaseorder table, drive the SQL-query execution.

Although the XQuery expression has been rewritten to relational expressions, you can improve this optimization by creating an index on the underlying relational data — you can optimize this query in the same way that you would optimize a purely SQL query. That is always the case with XQuery in Oracle XML DB: the optimization techniques you use are the same as those you use in SQL.

The UnitPrice attribute of collection element LineItem is an appropriate index target. The governing XML schema specifies that an ordered collection table (OCT) is used to store the LineItem elements.

However, the name of this OCT was generated by Oracle XML DB when the XML purchase-order documents were decomposed as XML schema-based data. Instead of using table purchaseorder from sample database schema HR, you could manually create a new purchaseorder table (in a different database schema) with the same properties and same data, but having OCTs with user-friendly names. Refer to Example 3-13 for how to do this.

Assuming that a purchaseorder table has been created as in Example 3-13, the following statement creates the appropriate index:

CREATE INDEX unitprice_index ON lineitem_table("PART"."UNITPRICE");

With this index defined, the query of Example 4-16 results in the following execution plan, which shows that the XMLTable expression has driven the overall evaluation.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1578014525
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     3 |   624 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |                   |     3 |   624 |     8   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_49323 |     3 |   564 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| UNITPRICE_INDEX   |    20 |       |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN| SYS_C004411       |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SYS_NC00013$">50)
       filter("ITEMNUMBER">=8 AND "SYS_NC00012$">2)
   3 - access("SYS_NC00013$">50)
   4 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
 
Note
-----
   - dynamic sampling used for this statement
 
23 rows selected.

Diagnosing XQuery Optimization: XMLOptimizationCheck

You can examine an execution plan for your SQL code to determine whether XQuery optimization occurs or the plan is instead suboptimal. In the latter case, a note such as the following appears immediately after the plan:

Unoptimized XML construct detected (enable XMLOptimizationCheck
for more information)

You can also compare the execution plan output with the plan output that you see after you use the optimizer hint NO_XML_QUERY_REWRITE, which turns off XQuery optimization.

In addition, you can use the SQL*Plus SET command with system variable XMLOptimizationCheck to turn on an XML diagnosability mode for SQL:

SET XMLOptimizationCheck ON

When this mode is on, the plan of execution is automatically checked for XQuery optimization, and if the plan is suboptimal then an error is raised and diagnostic information is written to the trace file indicating which operators are not rewritten.

The main advantage of XMLOptimizationCheck is that it brings a potential problem to your attention immediately. For this reason, you might find it preferable to leave it turned on at all times. Then, if an application change or a database change for some reason prevents a SQL operation from rewriting, execution is stopped instead of performance being negatively impacted without your being aware of the cause.

Note:

  • XMLOptimizationCheck was not available prior to Oracle Database 11g Release 2 (11.2.0.2). Users of older releases directly manipulated event 19201 to obtain XQuery optimization information.

  • OCI users can use OCIStmtExecute or event 19201. Only the event is available to Java users.

See Also:

"Turning Off Use of XMLIndex" for information about optimizer hint NO_XML_QUERY_REWRITE

Improving Performance for fn:doc and fn:collection on Repository Data

In Oracle XML DB, you can use XQuery functions fn:doc and fn:collection to reference documents and collections in Oracle XML DB Repository. When repository XML data is stored object-relationally or as binary XML, queries that use fn:doc and fn:collection are evaluated functionally; that is, they are not optimized to access the underlying storage tables directly.

To improve the performance of such queries, you must link them to the actual database tables that hold the repository data being queried. You can do that in either of the following ways:

  • Join view RESOURCE_VIEW with the XMLType table that holds the data, and then use the Oracle SQL functions equals_path and under_path instead of the XQuery functions fn:doc and fn:collection, respectively. These SQL functions reference repository resources in a performant way.

  • Use the Oracle XQuery extension-expression pragma ora:defaultTable.

Both methods have the same effect. Oracle recommends that you use the ora:defaultTable pragma because it lets you continue to use the XQuery standard functions fn:doc and fn:collection and it simplifies your code.

These two methods are illustrated in the examples of this section.

Using EQUALS_PATH and UNDER_PATH Instead of fn:doc and fn:collection

SQL function equals_path references a resource located at a specified repository path, and SQL function under_path references a resource located under a specified repository path. Example 4-23 and Example 4-24 illustrate this for functions fn:doc and equals_path; functions fn:collection and under_path are treated similarly.

Example 4-23 Unoptimized Repository Query Using fn:doc

SELECT XMLQuery('let $val :=
                     fn:doc("/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml")
                     /PurchaseOrder/LineItems/LineItem[@ItemNumber =19]
                 return $val' RETURNING CONTENT)
  FROM DUAL;

Example 4-24 Optimized Repository Query Using EQUALS_PATH

SELECT XMLQuery('let $val := $DOC/PurchaseOrder/LineItems/LineItem[@ItemNumber = 19]
                 return $val' PASSING OBJECT_VALUE AS "DOC" RETURNING CONTENT)
  FROM RESOURCE_VIEW rv, purchaseorder p
  WHERE ref(p) = XMLCast(XMLQuery('declare default element namespace 
                                   "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                                   fn:dataFoot 7 (/Resource/XMLRef)' PASSING rv.RES RETURNING CONTENT)
                         AS REF XMLType)
    AND equals_path(rv.RES, '/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml')
        = 1;

Using Oracle XQuery Pragma ora:defaultTable

Oracle XQuery extension-expression pragma ora:defaultTable lets you specify the default table used to store repository data that you query. The query is rewritten to automatically join the default table to view RESOURCE_VIEW and use Oracle SQL functions equals_path and under_path instead of XQuery functions fn:doc and fn:collection, respectively. The effect is thus the same as coding the query manually to use an explicit join and equals_path or under_path. Example 4-25 illustrates this; the query is rewritten automatically to what is shown in Example 4-24.

Example 4-25 Repository Query Using Oracle XQuery Pragma ora:defaultTable

SELECT XMLQuery('for $doc in (#ora:defaultTable PURCHASEORDER #)
                             {fn:doc("/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml")}
                   let $val := $doc/PurchaseOrder/LineItems/LineItem[@ItemNumber = 19]
                     return $val}'
                RETURNING CONTENT)
  FROM DUAL;

For clarity of scope Oracle recommends that you apply pragma ora:defaultTable directly to the relevant document or collection expression, fn:doc or fn:collection, rather than to a larger expression.

XQuery Static Type-Checking in Oracle XML DB

Oracle XML DB type-checks all XQuery expressions. Doing this at run time can be costly, however. As an optimization technique, whenever there is sufficient static type information available for a given query at compile time, Oracle XML DB performs static (compile time) type-checking of that query. Whenever sufficient static type information is not available for a given query at compile time, Oracle XML DB uses dynamic (run-time) type checking for that query.

Static type-checking can save execution time by raising errors at compile time. Static type-checking errors include both data-type errors and the use of XPath expressions that are invalid with respect to an XML schema.

Typical ways of providing sufficient static type information at query compile time include the following:

  • Using XQuery with fn:doc or fn:collection over relational data.

  • Using XQuery to query an XMLType table, column, or view whose XML Schema information is available at query compile time.

  • Using XQuery Update with a transform expression whose input is from an XMLType table or column that is based on an XML schema.

This section presents examples that demonstrate the utility of static type-checking and the use of these two means of communicating type information.

The XML data produced on the fly by fn:collection together with URI scheme oradb has ROW as its top-level element, but the query of Example 4-26 incorrectly lacks that ROW wrapper element. This omission raises a query compile-time error. Forgetting that fn:collection with oradb wraps relational data in this way is an easy mistake to make, and one that could be difficult to diagnose without static type-checking. Example 4-7 shows the correct code.

Example 4-26 Static Type-Checking of XQuery Expressions: oradb URI scheme

-- This produces a static-type-check error, because "ROW" is missing.
SELECT XMLQuery('for $i in fn:collection("oradb:/HR/REGIONS"),
                     $j in fn:collection("oradb:/HR/COUNTRIES")
                 where $i/REGION_ID = $j/REGION_ID and $i/REGION_NAME = "Asia"
                 return $j'
                RETURNING CONTENT) AS asian_countries
  FROM DUAL;
SELECT XMLQuery('for $i in fn:collection("oradb:/HR/REGIONS"),
*
ERROR at line 1:
ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name:
(REGION_ID)

In Example 4-27, XQuery static type-checking finds a mismatch between an XPath expression and its target XML schema-based data. Element CostCenter is misspelled here as costcenter (XQuery and XPath are case-sensitive). Example 4-13 shows the correct code.

Example 4-27 Static Type-Checking of XQuery Expressions: Schema-Based XML

-- This results in a static-type-check error: CostCenter is not the right case.
SELECT xtab.poref, xtab.usr, xtab.requestor
  FROM purchaseorder,
       XMLTable('for $i in /PurchaseOrder where $i/costcenter eq "A10" return $i'
                PASSING OBJECT_VALUE
                COLUMNS poref     VARCHAR2(20) PATH 'Reference',
                        usr       VARCHAR2(20) PATH 'User' DEFAULT 'Unknown',
                        requestor VARCHAR2(20) PATH 'Requestor') xtab;
  FROM purchaseorder,
       *
ERROR at line 2:
ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name:
(costcenter)

SQL*Plus XQUERY Command

Example 4-28 shows how you can enter an XQuery expression directly at the SQL*Plus command line, by preceding the expression with the SQL*Plus command XQUERY and following it with a slash (/) on a line by itself. Oracle Database treats XQuery expressions submitted with this command the same way it treats XQuery expressions in SQL/XML functions XMLQuery and XMLTable. Execution is identical, with the same optimizations.

Example 4-28 Using the SQL*Plus XQUERY Command

SQL> XQUERY for $i in fn:collection("oradb:/HR/DEPARTMENTS")
  2  where $i/ROW/DEPARTMENT_ID < 50
  3  return $i
  4  /
 
Result Sequence
--------------------------------------------------------------------------------
<ROW><DEPARTMENT_ID>10</DEPARTMENT_ID><DEPARTMENT_NAME>Administration</DEPARTMEN
T_NAME><MANAGER_ID>200</MANAGER_ID><LOCATION_ID>1700</LOCATION_ID></ROW>
 
<ROW><DEPARTMENT_ID>20</DEPARTMENT_ID><DEPARTMENT_NAME>Marketing</DEPARTMENT_NAM
E><MANAGER_ID>201</MANAGER_ID><LOCATION_ID>1800</LOCATION_ID></ROW>
 
<ROW><DEPARTMENT_ID>30</DEPARTMENT_ID><DEPARTMENT_NAME>Purchasing</DEPARTMENT_NA
ME><MANAGER_ID>114</MANAGER_ID><LOCATION_ID>1700</LOCATION_ID></ROW>
 
<ROW><DEPARTMENT_ID>40</DEPARTMENT_ID><DEPARTMENT_NAME>Human Resources</DEPARTME
NT_NAME><MANAGER_ID>203</MANAGER_ID><LOCATION_ID>2400</LOCATION_ID></ROW>

There are also a few SQL*Plus SET commands that you can use for settings that are specific to XQuery. Use SHOW XQUERY to see the current settings.

  • SET XQUERY BASEURI – Set the base URI for XQUERY. URIs in XQuery expressions are relative to this URI.

  • SET XQUERY CONTEXT – Specify a context item for subsequent XQUERY evaluations.

Using XQuery with XQJ to Access Database Data

XQuery API for Java (XQJ), also known as JSR-225, provides an industry-standard way for Java programs to access XML data using XQuery. It lets you evaluate XQuery expressions against XML data sources and process the results as XML data.

Oracle provides two XQuery engines for evaluating XQuery expressions: one in Oracle XML DB, for use with XML data in the database, and one in Oracle XML Developer's Kit, for use with XML data outside the database.

Similarly, Oracle provides two mid-tier XQJ implementations for accessing these two XQuery engines. Both implementations are part of Oracle XML Developer's Kit (XDK). You use XDK to access XML data with XQJ, regardless of whether that data resides in the database or elsewhere.

In particular, you can use XDK and XQJ to access XML data in Oracle XML DB. A typical use case for this feature is to access data stored in remote databases from a local Java program.

See Also:

Using XQuery with PL/SQL, JDBC, and ODP.NET to Access Database Data

This section provides examples of using XQuery with the Oracle APIs for PL/SQL, JDBC, and Oracle Data Provider for .NET (ODP.NET).

Example 4-29 shows how to use XQuery with PL/SQL, in particular, how to bind dynamic variables to an XQuery expression using the XMLQuery PASSING clause. The bind variables :1 and :2 are bound to the PL/SQL bind arguments nbitems and partid, respectively. These are then passed to XQuery as XQuery variables itemno and id, respectively.

Example 4-29 Using XQuery with PL/SQL

DECLARE
  sql_stmt VARCHAR2(2000); -- Dynamic SQL statement to execute
  nbitems  NUMBER := 3; -- Number of items
  partid   VARCHAR2(20):= '715515009058'; -- Part ID
  result   XMLType;
  doc      DBMS_XMLDOM.DOMDocument;
  ndoc     DBMS_XMLDOM.DOMNode;
  buf      VARCHAR2(20000);
BEGIN
  sql_stmt :=
    'SELECT XMLQuery(
              ''for $i in fn:collection("oradb:/OE/PURCHASEORDER") ' ||
               'where count($i/PurchaseOrder/LineItems/LineItem) = $itemno ' ||
                 'and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id ' ||
               'return $i/PurchaseOrder/LineItems'' ' ||
              'PASSING :1 AS "itemno", :2 AS "id" ' ||
              'RETURNING CONTENT) FROM DUAL';
 
  EXECUTE IMMEDIATE sql_stmt INTO result USING nbitems, partid;
  doc  := DBMS_XMLDOM.newDOMDocument(result);
  ndoc := DBMS_XMLDOM.makeNode(doc);
  DBMS_XMLDOM.writeToBuffer(ndoc, buf);
  DBMS_OUTPUT.put_line(buf);
END;
/

This produces the following output:

<LineItems>
  <LineItem ItemNumber="1">
    <Description>Samurai 2: Duel at Ichijoji Temple</Description>
    <Part Id="37429125526" UnitPrice="29.95" Quantity="3"/>
  </LineItem>
  <LineItem ItemNumber="2">
    <Description>The Red Shoes</Description>
    <Part Id="37429128220" UnitPrice="39.95" Quantity="4"/>
  </LineItem>
  <LineItem ItemNumber="3">
    <Description>A Night to Remember</Description>
    <Part Id="715515009058" UnitPrice="39.95" Quantity="1"/>
  </LineItem>
</LineItems>
<LineItems>
  <LineItem ItemNumber="1">
    <Description>A Night to Remember</Description>
    <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="2">
    <Description>The Unbearable Lightness Of Being</Description>
    <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="3">
    <Description>Sisters</Description>
    <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
  </LineItem>
</LineItems>

PL/SQL procedure successfully completed.

Example 4-30 shows how to use XQuery with JDBC, binding variables by position with the PASSING clause of SQL/XML function XMLTable.

Example 4-30 Using XQuery with JDBC

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.xdb.XMLType; 
import java.util.*;
 
public class QueryBindByPos
{
  public static void main(String[] args) throws Exception, SQLException
  {
    System.out.println("*** JDBC Access of XQuery using Bind Variables ***");
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    OracleConnection conn
      = (OracleConnection)
        DriverManager.getConnection("jdbc:oracle:oci8:@localhost:1521:ora11gR1", "oe", "oe");
    String xqString
      = "SELECT COLUMN_VALUE" +
          "FROM XMLTable('for $i in fn:collection(\"oradb:/OE/PURCHASEORDER\") " +
                         "where $i/PurchaseOrder/Reference= $ref " +
                         "return $i/PurchaseOrder/LineItems' " +
                        "PASSING ? AS \"ref\")";
    OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareStatement(xqString);
    String refString = "EABEL-20021009123336251PDT"; // Set the filter value
    stmt.setString(1, refString); // Bind the string
    ResultSet rs = stmt.executeQuery();
    while (rs.next())
    {
       XMLType desc = (XMLType) rs.getObject(1);
       System.out.println("LineItem Description: " + desc.getStringVal());
       desc.close();
    }
    rs.close();
    stmt.close();
  }
}

This produces the following output:

*** JDBC Access of Database XQuery with Bind Variables ***
LineItem Description: Samurai 2: Duel at Ichijoji Temple
LineItem Description: The Red Shoes
LineItem Description: A Night to Remember

Example 4-31 shows how to use XQuery with ODP.NET and the C# language. The C# input parameters :nbitems and :partid are passed to XQuery as XQuery variables itemno and id, respectively.

Example 4-31 Using XQuery with ODP.NET and C#

using System;
using System.Data;
using System.Text;
using System.IO;
using System.Xml;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
namespace XQuery
{
  /// <summary>
  /// Demonstrates how to bind variables for XQuery calls
  /// </summary>
  class XQuery
  {
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    static void Main(string[] args)
    {
      int rows = 0;
      StreamReader sr = null;
 
      // Create the connection.
      string constr = "User Id=oe;Password=***********;Data Source=ora11gr2"; // Replace with real password.
      OracleConnection con = new OracleConnection(constr);
      con.Open();
 
      // Create the command.
      OracleCommand cmd = new OracleCommand("", con);
 
      // Set the XML command type to query.
      cmd.CommandType   = CommandType.Text;
        
      // Create the SQL query with the XQuery expression.
      StringBuilder blr = new StringBuilder();
      blr.Append("SELECT COLUMN_VALUE FROM XMLTable");
      blr.Append("(\'for $i in fn:collection(\"oradb:/OE/PURCHASEORDER\") ");
      blr.Append("   where count($i/PurchaseOrder/LineItems/LineItem) = $itemno ");
      blr.Append("      and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id ");
      blr.Append("   return $i/PurchaseOrder/LineItems\' ");
      blr.Append("  PASSING :nbitems AS \"itemno\", :partid AS \"id\")");
 
      cmd.CommandText = blr.ToString();
      cmd.Parameters.Add(":nbitems", OracleDbType.Int16, 3, ParameterDirection.Input);
      cmd.Parameters.Add(":partid", OracleDbType.Varchar2, "715515009058", ParameterDirection.Input);
 
      // Get the XML document as an XmlReader.
      OracleDataReader dr = cmd.ExecuteReader();
      dr.Read();
 
      // Get the XMLType column as an OracleXmlType
      OracleXmlType xml = dr.GetOracleXmlType(0);
 
      // Print the XML data in the OracleXmlType object
      Console.WriteLine(xml.Value);
      xml.Dispose();
 
      // Clean up.
      cmd.Dispose();
      con.Close();
      con.Dispose();
    }
  }
}

This produces the following output:

<LineItems>
  <LineItem ItemNumber="1">
    <Description>Samurai 2: Duel at Ichijoji Temple</Description>
    <Part Id="37429125526" UnitPrice="29.95" Quantity="3"/>
  </LineItem>
  <LineItem ItemNumber="2">
    <Description>The Red Shoes</Description>
    <Part Id="37429128220" UnitPrice="39.95" Quantity="4"/>
  </LineItem>
  <LineItem ItemNumber="3">
    <Description>A Night to Remember</Description>
    <Part Id="715515009058" UnitPrice="39.95" Quantity="1"/>
  </LineItem>
</LineItems>

Oracle XML DB Support for XQuery

This section describes Oracle XML DB for the XQuery language.

Support for XQuery and SQL

Support for the XQuery language in Oracle XML DB is designed to provide the best fit between the worlds of relational storage and querying XML data. That is, Oracle XML DB is a general XQuery implementation, but it is in addition specifically designed to make relational and XQuery queries work well together.

The specific properties of the Oracle XML DB XQuery implementation are described in this section. The XQuery standard explicitly calls out certain aspects of the language processing as implementation-defined or implementation-dependent. There are also some features that are specified by the XQuery standard but are not supported by Oracle XML DB.

Implementation Choices Specified in the XQuery Standards

The XQuery standards specify that each of the following aspects of language processing is to be defined by the implementation.

  • Implicit time zone support – In Oracle XML DB, the implicit time zone is always assumed to be Z, and instances of xs:date, xs:time, and xs:datetime that are missing time zones are  automatically converted to UTC.

  • copy-namespaces default value – The default value for a copy-namespaces declaration (used in XQuery Update) is inherit.

  • Revalidation mode – The default mode for XQuery Update transform expression revalidation is skip. However, if the result of a transform expression is an update to XML schema-based data in an XMLType table or column, then XML schema validation is enforced.

XQuery Features Not Supported by Oracle XML DB

The following features specified by the XQuery standard are not supported by Oracle XML DB:

  • Copy-namespace mode – Oracle XML DB supports only preserve and inherit for a copy-namespaces declaration. If an existing element node is copied by an element constructor or a document constructor, all in-scope namespaces of the original element are retained in the copy. Otherwise, the copied node inherits all in-scope namespaces of the constructed node. An error is raised if you specify no-preserve or no-inherit.

  • Version encoding – Oracle XML DB does not support an optional encoding declaration in a version declaration. That is, you cannot include (encoding an-encoding) in a declaration xquery version a-version;. In particular, you cannot specify an encoding used in the query. An error is raised if you include an encoding declaration.

  • xml:id – Oracle XML DB does not support use of xml:id. If you use xml:id, then an error is raised.

  • XQuery prolog default-collation declaration.

  • XQuery prolog boundary-space declaration.

  • XQuery data type xs:duration. Use either xs:yearMonthDuration or xs:DayTimeDuration instead.

  • XQuery Update function fn:put.

XQuery Optional Features

The following optional features specified by the XQuery standard are not supported by Oracle XML DB:

  • Schema Validation Feature

  • Module Feature

The following optional XQuery features are supported by Oracle XML DB:

  • XQuery Static Typing Feature

  • XQuery Update Static Typing Feature

Support for XQuery Functions and Operators

Oracle XML DB supports all of the XQuery functions and operators included in the latest XQuery 1.0 and XPath 2.0 Functions and Operators specification, with the following exceptions. There is no support for the following:

  • Function fn:tokenize. Use Oracle XQuery function ora:tokenize instead.

  • Functions fn:id and fn:idref.

  • Function fn:collection without arguments.

  • Optional collation parameters for XQuery functions.

XQuery Functions fn:doc, fn:collection, and fn:doc-available

Oracle XML DB supports XQuery functions fn:doc, fn:collection, and fn:doc-available for all resources in Oracle XML DB Repository.

Function fn:doc returns the repository file resource that is targeted by its URI argument; it must be a file of well-formed XML data. Function fn:collection is similar, but works on repository folder resources (each file in the folder must contain well-formed XML data).

When used with Oracle URI scheme oradb, fn:collection can return XML data derived on the fly from existing relational data that is not in the repository.

XQuery function fn:collection raises an error when used with URI scheme oradb, if its targeted table or view, or a targeted column, does not exist. Functions fn:doc and fn:collection do not raise an error if the repository resource passed as argument is not found. Instead, they return an empty sequence.

You can determine whether a given document exists using XQuery function fn:doc-available. It returns true if its document argument exists, false if not.

See Also:

http://www.w3.org/ for the definitions of XQuery functions and operators

Support for XQuery Full Text

This section describes Oracle support for the XQuery and XPath Full Text 1.0 Recommendation (hereafter XQuery Full Text). Refer to that standard for information about any terms that are not detailed here.

A general rule for understanding Oracle support for XQuery Full Text is that the Oracle implementation is based on Oracle Text, which provides full-text indexing and search for Oracle products and for applications developed using them. The support details provided in this section are a consequence of this Oracle Text based implementation.

XQuery Full Text, XML Schema-Based Data, and Pragma ora:no_schema

You can use XQuery Full Text to query XMLType data regardless of the storage model used. However, if you use it with XML Schema-based data then you must also use the XQuery extension-expression pragma ora:no_schema in your query, or else an error is raised.

And if you use ora:no_schema then, for purposes of XQuery Full Text, the XML data is implicitly cast to non XML Schema-based data. In other words, Oracle support of XQuery Full Text treats all XML data as if it were not based on an XML schema.

In particular, this means that if you include in your query an XQuery Full Text condition that makes use of XML Schema data types, such type considerations are ignored. A comparison of two XML Schema date values, for instance, is handled as a simple string comparison. Oracle support for XQuery Full Text is not XML Schema-aware.

Restrictions on Using XQuery Full Text with XMLExists

You can pass only one XMLType instance as a SQL expression in the PASSING clause of SQL/XML function XMLExists, and each of the other, non-XMLType SQL expressions in that clause must be either a compile-time constant of a SQL built-in data type or a bind variable that is bound to an instance of such a data type. If this restriction is not respected then compile-time error ORA-18177 is raised.

Supported XQuery Full Text FTSelection Operators

Oracle XML DB supports only the following XQuery Full Text FTSelection operators. Any applicable restrictions are noted. Use of the terms "must" and "must not" means that an error is raised if the specified restriction is not respected. Use of any operators not listed here raises an error.

  • FTAnd (ftand)

  • FTMildNot (not in)

    Each operand for operator FTMildNot must be either a term or a phrase, that is, an instance of FTWords. It must not be an expression. Oracle handles FTMildNot the same way it handles Oracle Text operator MNOT.

  • FTOr (ftor)

  • FTOrder (ordered)

    Oracle supports the use of FTOrder only when used in the context of a window (FTWindow). Otherwise, it is not supported. For example, you can use ordered window 5 words, but you cannot use only ordered without also window. Oracle handles FTOrder the same way it handles Oracle Text operator NEAR with a TRUE value for option ORDER.

  • FTUnaryNot (ftnot)

    FTUnaryNot must be used with FTAnd. You cannot use FTUnaryNot by itself. For example, you can use ftand ftnot, but you cannot use only ftnot without also ftand. Oracle handles FTUnaryNot the same way it handles Oracle Text operator NOT.

  • FTWindow (window)

    Oracle handles FTWindow the same way it handles Oracle Text operator NEAR. You must specify the window size only in words, not in sentences or paragraphs (for example, window 2 paragraphs), and you must specify it as a numeric constant that is less than or equal to 100.

  • FTWords

    FTWordsValue must be an XQuery literal string or a SQL bind variable whose value is passed to SQL function XMLExists or XMLQuery from a SQL expression whose evaluation returns a non-XMLType value.

    In addition, FTAnyallOption, if present, must be any. That is, FTWords must correspond to a sequence with only one item.

    Note:

    Even though FTWords corresponds to a sequence of only one item, you can still search for a phrase of multiple words, by using a single string for the entire phrase. So for example, although Oracle XML DB does not support using {"found" "necklace"} for FTWords, you can use "found necklace".

Supported XQuery Full Text Match Options

Oracle XML DB supports only the following XQuery Full Text match options. Any applicable restrictions are noted. Use of the terms "must" and "must not" means that an error is raised if the specified restriction is not respected. Use of any match options not listed here raises an error.

  • FTStemOption (stemming, no stemming)

The default behavior specified in the XQuery and XPath Full Text 1.0 Recommendation is used for each unsupported match option, with the following exceptions:

  • FTLanguage (unsupported) – The language used is the language defined by the default lexer, which means the language that was used when the database was installed.

  • FTStopWordOption (unsupported) – The stoplist used is the stoplist defined for that language.

See Also:

Unsupported XQuery Full Text Features

In addition to all FTSelection operators not mentioned in "Supported XQuery Full Text FTSelection Operators" and all match options not mentioned in "Supported XQuery Full Text Match Options", Oracle XML DB does not support the following XQuery Full Text features:

  • FTIgnoreOption

  • FTWeight (weight declarations, used with FTPrimaryWithOptions)

  • FTScoreVar (score variables, used with XQuery ForClause and LetClause or with XPath 2.0 SimpleForClause)

XQuery Full Text Errors

A compile-time error is raised whenever you use an XQuery Full Text (XQFT) feature that Oracle does not support.

In addition, compile-time error ORA-18177 is raised whenever you use a supported XQFT expression in a SQL WHERE clause (typically in XMLExists), if you did not create a corresponding XML-enabled Oracle Text index or if that index is not picked up.

See Also:



Footnote Legend

Footnote 1: The value returned is a sequence, as always. However, in XQuery, a sequence of one item is equivalent to that item itself. In this case, the single item is a Boolean value.
Footnote 2: Namespace prefix xs is predefined for the XML Schema namespace, http://www.w3.org/2001/XMLSchema.
Footnote 3: The question mark (?) here is a zero-or-one occurrence indicator that indicates that the argument can be the empty sequence. See "XQuery Expressions".
Footnote 4: The question mark (?) here is a zero-or-one occurrence indicator that indicates that the argument can be the empty sequence. See "XQuery Expressions".
Footnote 5: The question mark (?) here is a zero-or-one occurrence indicator that indicates that the argument can be the empty sequence. See "XQuery Expressions".
Footnote 6: Prior to Oracle Database 12c Release 1 (12.1), pragmas ora:no_xmlquery_rewrite and ora:xmlquery_rewrite were named ora:xq_proc and ora:xq_qry, respectively. They were renamed for readability, with no change in meaning.
Footnote 7: XQuery function fn:data is used here to atomize its argument, in this case returning the XMLRef node's typed atomic value.