5 Using XQuery with Oracle XML DB

Functional Language Based on Sequences

XQuery 1.0 is the W3C language designed for querying XML data. It 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 based on sequences: the result of each XQuery expression is a sequence. 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 a functional language. As such, it consists of a set of possible expressions that are evaluated and return values (which, for XQuery, are sequences). As a functional language, XQuery is also referentially transparent, generally: 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.

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 Expressions

XQuery expressions are case-sensitive. The 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 (Boolean^Foot 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.)

FLWOR Expressions

As for XQuery in general, there is a lot to learn about FLWOR expressions. This section provides only 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.

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

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.

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 5-1 XMLQUERY Syntax

Description of "Figure 5-1 XMLQUERY Syntax"

XML_passing_clause ::= 

• 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 5-8.

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

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 5-9). You use XMLTable in a SQL FROM clause.

Figure 5-2 XMLTABLE Syntax

Description of "Figure 5-2 XMLTABLE Syntax"

XML_namespaces_clause ::= 

XMLTABLE_options ::= 

XML_passing_clause ::= 

XML_table_column ::= 

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

  • You must specify the datatype of each resulting column except the FOR ORDINALITY column. However, see next item.

  • When XMLTable is used with XML schema-based storage of XMLType, datatype is optional. If not present, the data type is inferred from the XML schema. If Oracle XML DB is unable to determine the proper type for a node, a default type of VARCHAR2(4000) is used.

    This is an Oracle extension; in the SQL/XML standard, datatype is always required.

    Note:

    The inferred data type might change as the result of upgrading Oracle XML DB or applying a patch. In particular, a new release or patch set might be able to determine the data type when the previous release was unable to do so (and so defaulted to VARCHAR2(4000)). To protect against such an eventuality, specify an explicit data type with datatype.

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

ora:contains XQuery Function

ora:contains Syntax

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

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:matches XQuery Function

ora:matches Syntax

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

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_string – xs:string?^Foot 3 

• match_pattern – xs:string

• match_parameter – xs:string

ora:replace XQuery Function

ora:replace Syntax

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

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_string – xs:string?^Foot 4 

• match_pattern – xs:string

• replace_string – xs:string

• match_parameter – xs: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.

ora:sqrt XQuery Function

ora:sqrt Syntax

ora:sqrt (number)

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

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_string – xs:string?^Foot 5 

• match_pattern – xs:string

• match_parameter – xs:string

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 5-2 provides a sampling.

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 5-2 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 5-2 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 5-3 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.

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 5-3, 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 5-4 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.

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 5-4, 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 Table or View Data using XQuery

This section presents examples of using XQuery to query relational data as if it were XML data.

Example 5-5 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).

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;

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 5-5, 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 5-6 uses fn:collection within nested FLWOR expressions to query relational data.

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;

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 5-6, 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.

Example 5-7 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 5-6; 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, the value of pseudocolumn COLUMN_VALUE for each virtual-table row is an XML fragment (of type XMLType) with a single Warehouse element.

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>');

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.

Using XQuery with XMLType Data

This section presents examples of using XQuery with XMLType relational data.

The query in Example 5-8 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.

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;

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 5-8, 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 5-8 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 5-9 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.

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.

Example 5-10 is similar to Example 5-9 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 5-9, these empty sequences are not joined with the purchaseorder table, so the overall SQL-query result set has only ten rows. In Example 5-10, 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.

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.

Example 5-11 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.

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 5-11, 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.

In Example 5-12, SQL/XML function XMLTable is used to break up the XML data in an XMLType collection element, LineItem, into separate columns of a virtual table.

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.

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 5-13 illustrates use of a namespace declaration in an XQuery expression.

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 5-14, 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.

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');

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

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 5-15 shows the optimization of XMLQuery over relational data accessed as XML. Example 5-16 shows the optimization of XMLTable in the same context.

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.

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 5-17 shows the optimization of XMLQuery over an XML schema-based XMLType table. Example 5-18 shows the optimization of XMLTable in the same context.

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.

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.

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

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.

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.

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;

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 6 (/Resource/XMLRef)' PASSING rv.RES RETURNING CONTENT)
                         AS REF XMLType)
    AND equals_path(rv.RES, '/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml')
        = 1;

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;

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.

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:

• The XQuery regular-expression functions: fn:matches, fn:replace, and fn:tokenize. Use Oracle XQuery functions ora:matches, ora:replace, and ora:tokenize instead, respectively.

• Functions fn:id and fn:idref.

• Function fn:collection without arguments.

• Optional collation parameters for XQuery functions.