Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
This chapter describes how to use the XQuery language with Oracle XML DB. It covers Oracle XML DB support for the language, including SQL functions XMLQuery
and XMLTable
and the SQL*Plus XQUERY
command.
This chapter contains these topics:
Note:
At the time of release of Oracle Database 10g Release 2, the W3C XQuery working group had not yet published the XQuery recommendation. Oracle will continue to track the evolution of the XQuery standard, until such time as it becomes a recommendation. During this period, in order to follow the evolution of the XQuery standard, Oracle may release updates to the XQuery implementation which are not backwards compatible with previous releases or patch sets.Oracle XML DB support for the XQuery language is provided through a native implementation of SQL/XML functions XMLQuery
and XMLTable
. 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 generally evaluates XQuery expressions by compiling them into the same underlying structures as relational queries. Queries are optimized, leveraging both relational-database and XQuery-specific optimization technologies, so that Oracle XML DB serves as a native XQuery engine.
There are a few XQuery expressions that cannot be rewritten to relational expressions. To provide you the full power of XQuery, Oracle XML DB evaluates these XQuery expressions using a functional XQuery interpreter, or evaluation engine (which itself has been compiled into the database). The treatment of all XQuery expressions, whether natively compiled or evaluated functionally, is transparent to you: you will never need to change your code in any way to take advantage of available XQuery optimizations.
See Also:
SQL Functions XMLQuery and XMLTable and SQL*Plus XQUERY Command
Oracle XQuery Extension Functions for Oracle-specific XQuery functions that extend the language
Oracle XML DB Support for XQuery for details on Oracle XML DB support for XQuery
Oracle XML DB supports the latest version of the XQuery language specifications. This section presents a brief overview of the language. For more information, consult a recent book on the language or refer to the standards documents that define it, which are available at http://www.3.org
.
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 semistructured, 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, in the case of 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 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 developing XPath 2.0 standard will be a subset of XQuery. XPath 1.0 is currently a subset, although XQuery uses a richer type system.
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 runtime 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 ("xyz12", "abc", 5) satisfies $toto = $titi
Regular expression – XQuery regexes 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.)
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
function similarly to a SQL FROM
clause; where
acts like a SQL WHERE
clause; order by
is similar to ORDER BY
in SQL; and return
is like SELECT
in SQL. In other words, 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 a sequence in any order other than document order.
SQL functions XMLQuery
and XMLTable
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, XMLQuery
and XMLTable
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.
The SQL/XML standard is ISO/IEC 9075–14:2005(E), Information technology – Database languages – SQL – Part 14: XML-Related Specifications (SQL/XML). As part of the SQL standard, it is aligned with SQL:2003. It is being developed under the auspices of these two standards bodies:
ISO/IEC JTC1/SC32 ("International Organization for Standardization and International Electrotechnical Committee Joint Technical Committee 1, Information technology, Subcommittee 32, Data Management and Interchange").
INCITS Technical Committee H2 ("INCITS" stands for "International Committee for Information Technology Standards"). INCITS is an Accredited Standards Development Organization operating under the policies and procedures of ANSI, the American National Standards Institute. Committee H2 is the committee responsible for SQL and SQL/MM.
This SQL/XML standardization process is ongoing. Please refer to http://www.sqlx.org
for the latest information about XMLQuery
and XMLTable
.
See Also:
http://www.sqlx.org
for information on SQL functions XMLQuery
and XMLTable
http://www.w3.org
for information on the XQuery language
"Generating XML Using SQL Functions" for information on using other SQL/XML functions with Oracle XML DB
You use SQL 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.
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. 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 17-8. To query a relational table or view as if it were XML, without having to first create a SQL/XML view on top of it, use XQuery function ora:view
within an XQuery expression — see, for example, Example 17-6.
See Also:
http://www.sqlx.org
for information on the definition of SQL function XMLQuery
Oracle Database SQL Reference for reference information on SQL function XMLQuery
in Oracle Database
You use SQL function XMLTable
to shred 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 17-9). You use XMLTable
in a SQL FROM
clause.
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 shredded into 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. 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 datatype NUMBER
). There must be at most one FOR ORDINALITY
clause.
You must specify the datatype
of each resulting column
except the FOR ORDINALITY
column.
The optional PATH
clause specifies that the portion of the XQuery result that is addressed by XPath 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 XPath expression column
is assumed. For example, these two expressions are equivalent:
XMLTable(... COLUMNS foo) XMLTable(... COLUMNS foo PATH 'FOO')
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:
http://www.sqlx.org
for information on the definition of SQL function XMLTable
Oracle Database SQL Reference for reference information on SQL function XMLTable
in Oracle Database
The following namespaces and prefixes are predefined for use with XQuery in Oracle XML DB:
Table 17-1 Predefined Namespaces and Prefixes
Prefix | Namespace | Description |
---|---|---|
|
|
Oracle XML DB namespace |
|
|
XPath local function declaration namespace |
|
|
XPath function namespace |
|
|
XPath datatype namespace |
|
|
XML namespace |
|
|
XML Schema namespace |
|
|
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.
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.
Syntax
ora:contains (input_text, text_query [, policy_name] [, policy_owner])
XPath function ora:contains
can be used in an XPath expression inside an XQuery expression or in a call to SQL function existsNode
, extract
, or extractValue
. 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, 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.
See Also:
"ora:contains XPath Function"Syntax
ora:matches (target_string, match_pattern [, match_parameter])
XQuery function ora:match
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 datatypes. The argument types are as follows:
target_string
– xs:string?
Foot 3
match_pattern
– xs:string
match_parameter
– xs:string
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:matches
is the same as that of SQL function regexp_replace
, but the types of its arguments are XQuery types instead of SQL datatypes. 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 occurences – search starts with the first character and all occurrences are replaced.
Syntax
ora:sqrt (number)
XQuery function ora:sqrt
returns the square root of its numerical 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.
Syntax
ora:view ([db-schema STRING,] db-table STRING) RETURNS document-node(element())*Foot 5
XQuery function ora:view
lets you query existing database tables or views inside an XQuery expression, as if they were XML documents. In effect, ora:view
creates XML views over the relational data, on the fly. You can thus use ora:view
to avoid explicitly creating XML views on top of relational data.
The input parameters are as follows:
db-schema
– An optional string literal that names a database schema.
db-table
– A string literal naming a database table or view. If db-schema
is present, then db-table
is in database schema db-schema
.
Function ora:view
returns an unordered sequence of document
nodes, one for each row of db-table
. The SQL/XML standard is used to map each input row to the output XML document: relational column names become XML element names. Unless db-table
is of type XMLType
, the column elements derived from a given table row are wrapped together in a ROW
element. In that case, the return type is, more precisely, document-node(element(ROW))*
.
XQuery is a very general and expressive language, and SQL functions XMLQuery
and XMLTable
combine that power of expression and computation with the similar strengths of SQL. This section illustrates some of what you can do with these two SQL/XML functions.
You will typically use XQuery with Oracle XML DB in the following ways. The examples here are organized to reflect these different uses.
Query XML data in Oracle XML DB Repository.
See "Using XQuery to Query XML Data in Oracle XML DB Repository".
Query a relational table or view as if it were XML data. To do this, you use Oracle XQuery function ora:view
to create an XML view over the relational data, on the fly.
See "Using ora:view to Query Relational Data in XQuery Expressions".
Query XMLType
relational data, possibly shredding the resulting XML into relational data using function XMLTable
.
Example 17-1 Creating Resources for Examples
This example creates repository resources that are used in some of the other 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://emp.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.createResource('/public/emps.xml', empsxmlstring); res := DBMS_XDB.createResource('/public/empsns.xml', empsxmlnsstring); res := DBMS_XDB.createResource('/public/depts.xml', deptsxmlstring); END; /
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, as well as various types of XML node (document-node()
, element()
, attribute()
, text()
, namespace()
, and so on). Example 17-2 provides a sampling.
Example 17-2 XMLQuery Applied to a Sequence of Items of Different Types
This example applies SQL 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>
This example also shows construction of a sequence using the comma operator (,
) and parentheses ((
, )
) for grouping.
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.
The sequence expression 100 to 102
evaluates to the sequence (100, 101, 102)
, so the argument to XMLQuery
is actually a sequence that contains a nested sequence. The sequence argument is automatically flattened, as is always the case for XQuery sequences. The actual argument is, in effect, (1, 5, "a", 100, 101, 102, <A>33</A>)
.
This section presents examples of using XQuery with XML data in Oracle XML DB Repository. In Oracle XML DB, functions fn:doc
and fn:collection
return file and folder resources in the repository, respectively. Each example in this section uses XQuery function fn:doc
to obtain a repository file that contains XML data, and then binds XQuery variables to parts of that data using for
and let
FLWOR-expression clauses.
See Also:
XQuery Functions doc and collectionExample 17-3 FLOWR Expression Using For, Let, Order By, Where, and Return
This example queries two XML-document resources in Oracle XML DB Repository: /public/emps.xml
and /public/depts.xml
. It illustrates the use of each of the possible FLWOR-expression clauses, as well as the use of fn:doc
.
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 17-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 17-4 also uses each of the FLWOR-expression clauses. In addition, it demonstrates the use of other XQuery functions, besides fn:doc
.
Example 17-4 FLOWR Expression Using Built-In Functions
This example 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>10<headcount>2</headcount><avgsal>165500</avgsal></big-dept> 1 row selected.
In Example 17-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.
This section presents examples of using Oracle XQuery function ora:view
to query relational data as if it were XML data, from within an XQuery expression.
See Also:
"ora:view XQuery Function"Example 17-5 Using ora:view to Query Relational Tables as XML Views
This example uses Oracle XQuery function ora:view
in a FLWOR expression to query two relational tables, regions
and countries
joining. Both tables belong to sample database schema hr
.
SELECT XMLQuery('for $i in ora:view("REGIONS"), $j in ora:view("COUNTRIES") where $i/ROW/REGION_ID = $j/ROW/REGION_ID and $i/ROW/REGION_NAME = "Asia" return $j' RETURNING CONTENT) AS asian_countries FROM DUAL;
This produces the following result (the actual result is not pretty-printed).
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 17-5, the various FLWOR clauses perform these operations:
for
iterates over sequences of XML elements returned by calls to ora:view
. In the first call, each element corresponds to a row of relational table regions
and is bound to variable $i
. Similarly, in the second call to ora:view
, $j
is bound to successive rows of table 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 the countries
table as an XML document containing XML fragments with ROW
as their top-level element.
Example 17-6 uses ora:view
within nested FLWOR expressions.
Example 17-6 Using ora:view in a Nested FLWOR Query
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
. The two-argument form of ora:view
is used here, to specify the database schema (first argument) in addition to the table (second argument).
CONNECT HR/HR GRANT SELECT ON LOCATIONS TO OE / CONNECT OE/OE SELECT XMLQuery( 'for $i in ora:view("OE", "WAREHOUSES")/ROW return <Warehouse id="{$i/WAREHOUSE_ID}"> <Location> {for $j in ora:view("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 produces the following result (the actual result is not pretty-printed).
XMLQUERY('FOR$IINORA:VIEW("OE","WAREHOUSES")/ROWRETURN<WAREHOUSEID="{$I/WAREHOUS -------------------------------------------------------------------------------- <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 17-6, the various FLWOR clauses perform these operations:
The outer for
iterates over the sequence of XML elements returned by ora:view
: each element corresponds to a row of relational table 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 ora:view
: each element corresponds to a row of relational table 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 17-7 Using ora:view with XMLTable to Query a Relational Table as XML
In this example, SQL function XMLTable
is used to shred the result of an XQuery query to virtual relational data. The XQuery expression used in this example is identical to the one used in Example 17-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 ora:view("OE", "WAREHOUSES")/ROW return <Warehouse id="{$i/WAREHOUSE_ID}"> <Location> {for $j in ora:view("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 17-6, 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.
This section presents examples of using XQuery with XMLType
relational data.
Example 17-8 Using XMLQuery with PASSING Clause, to Query an XMLType Column
This example passes an XMLType
column, oe.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 warehouses;
This produces the following output:
WAREHOUSE_NAME -------------- BIG_WAREHOUSES -------------- Southlake, Texas San Francisco New Jersey <Details><Docks></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 17-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 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 17-9 Using XMLTable with XML Schema-Based Data
This example uses SQL function XMLTable
to query an XMLType
table, hr.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.
Example 17-10 Using XMLQuery with Schema-Based Data
This example is similar to Example 17-9 in its effect. It uses XMLQuery
, instead of XMLTable
, to query hr.purchaseorder
. These two examples differ in their treatment of the empty sequences returned by the XQuery expression. In Example 17-9, these empty sequences are not joined with the purchaseorder
table, so the overall SQL-query result set has only ten rows. In Example 17-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 17-11 Using XMLTable with PASSING and COLUMNS Clauses
This example 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.
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
shreds 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
.
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 '/PO/Ref', priority VARCHAR2(8) PATH '/PO/Type' DEFAULT 'Regular', contact VARCHAR2(20) PATH '/PO/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.
Example 17-12 Using XMLTable to Shred XML Collection Elements into Relational Data
In this example, SQL function XMLTable
is used to shred 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.
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.
An XQuery namespace declaration has no effect outside of its XQuery expression, however. 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 17-13, XMLNAMESPACES
is used to define the prefix e
for the namespace http://emp.com
. This namespace is used in the COLUMNS
clause as well as the XQuery expression of the XMLTable
expression.
Example 17-13 Using XMLTable with the NAMESPACES Clause
SELECT * FROM XMLTable(XMLNAMESPACES('http://emp.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://emp.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://emp.com')
. The qualified name $i/e:emps/e:emp
could then be written without an explicit prefix: $i/emps/emp
.
As mentioned, Oracle XML DB generally evaluates XQuery expressions by executing equivalent relational expressions. This optimization uses the same mechanism as XPath rewrite, and it provides the same benefits. To tune the execution performance of XQuery expressions:
Print and examine the associated EXPLAIN PLAN
s.
If execution is not optimal, perform XPath tuning as discussed in Chapter 3 and Chapter 6.
This section presents the EXPLAIN PLAN
s for some of the examples shown earlier, to indicate how they are executed. The examples are organized into the following groups according to the target of the XQuery expression:
a SQL/XML view created on the fly using ora:view
an XML schema-based XMLType
table
Example 17-14 shows the optimization of XMLQuery
over a SQL/XML view created by ora:view
. Example 17-15 shows the optimization of XMLTable
in the same context.
Example 17-14 Optimization of XMLQuery with ora:view
Here, again, is Example 17-6:
SELECT XMLQuery( 'for $i in ora:view("OE", "WAREHOUSES")/ROW return <Warehouse id="{$i/WAREHOUSE_ID}"> <Location> {for $j in ora:view("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;
The EXPLAIN PLAN
for this example shows that the query has been optimized:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 2976528487 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 41 | | | | 2 | NESTED LOOPS | | 1 | 41 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 41 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 185 | | | | 7 | NESTED LOOPS | | 9 | 1665 | 4 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 9 | TABLE ACCESS FULL | WAREHOUSES | 9 | 1665 | 2 (0)| 00:00:01 | | 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("LOCATION_ID"=:B1) 22 rows selected.
Example 17-15 Optimization of XMLTable with ora:view
Here, again, is Example 17-7:
SELECT * FROM XMLTable( 'for $i in ora:view("OE", "WAREHOUSES")/ROW return <Warehouse id="{$i/WAREHOUSE_ID}"> <Location> {for $j in ora:view("HR", "LOCATIONS")/ROW where $j/LOCATION_ID eq $i/LOCATION_ID return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)} </Location> </Warehouse>');
The EXPLAIN PLAN
for this example shows that the query has been optimized:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 2573750906 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 1665 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 41 | | | | 2 | NESTED LOOPS | | 1 | 41 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 41 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 9 | 1665 | 4 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | WAREHOUSES | 9 | 1665 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("LOCATION_ID"=:B1) 20 rows selected.
Example 17-16 shows the optimization of XMLQuery
over an XML schema-based XMLType
table. Example 17-17 shows the optimization of XMLTable
in the same context.
Example 17-16 Optimization of XMLQuery with Schema-Based XMLType Data
Here, again, is Example 17-10:
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;
The EXPLAIN PLAN
for this example shows that the query has been optimized.
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 17-17 Optimization of XMLTable with Schema-Based XMLType Data
Here, again, is Example 17-12:
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;
The EXPLAIN PLAN
for this example 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.
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- Plan hash value: 3113556559 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 376 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 4 | 376 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| PURCHASEORDER | 1 | 37 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_48748 | 3 | 171 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - 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"><re ad-properties/><read-contents/></privilege>'))=1) 3 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$") filter("SYS_NC00013$">50 AND "SYS_NC00012$">2 AND "ITEMNUMBER">=8) 22 rows selected.
In this example, table hr.purchaseorder
is traversed completely; the XMLTable
expression is evaluated for each purchase-order document. It is more efficient, however, to have the XMLTable
expression, not the purchaseorder
table, drive the SQL-query execution. That is, 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 that you use in SQL.
The UnitPrice
attribute of collection element LineItem
is an appropriate index target. The governing XML schema specifies that a nested table is used to store the LineItem
elements.
However, the name of this nested table was system-generated when the XML purchase-order documents were shredded as XML schema-based data. Instead of using table purchaseorder
from sample database-schema hr
, for illustration we will manually create a new purchaseorder
table (in a different database schema) with the same properties and same data, but having nested tables with user-friendly names. Refer to Example 3-11 for how to do this.
Assuming that a purchaseorder
table has been created as in Example 3-11, the following statement creates the appropriate index:
CREATE INDEX unitprice_index ON lineitem_table("PART"."UNITPRICE");
With this index defined, Example 17-12 results in the following EXPLAIN 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.
Oracle XML DB performs static (that is, compile-time) type-checking of XQuery expressions. It also performs dynamic (runtime) type-checking. This section presents examples that demonstrate the utility of static type-checking.
Example 17-18 Static Type-Checking of XQuery Expressions: ora:view
The XML view produced on the fly by Oracle XQuery function ora:view
has ROW
as its top-level element, but this example incorrectly lacks that ROW
wrapper element. This omission raises a compile-time error. Forgetting that ora:view
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 17-5 shows the correct code.
-- This produces a static-type-check error, because "ROW" is missing. SELECT XMLQuery('for $i in ora:view("REGIONS"), $j in ora:view("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 ora:view("REGIONS"), $j in ora:view("COUNTRIES") * ERROR at line 1: ORA-19276: XP0005 - XPath step specifies an invalid element/attribute name: (REGION_ID)
Example 17-19 Static Type-Checking of XQuery Expressions: Schema-Based XML
In this example, 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 17-11 shows the correct code.
-- 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: XP0005 - XPath step specifies an invalid element/attribute name:
(costcenter)
Example 17-20 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 functions XMLQuery
and XMLTable
. Execution is identical, with the same optimizations.
Example 17-20 Using the SQL*Plus XQUERY Command
SQL> XQUERY for $i in ora:view("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 ORDERING
– Set the XQUERY
execution mode to ORDERED
or UNORDERED
(the default value). See "Functional Language Based on Sequences".
SET XQUERY NODE
– Set the node-identity preservation mode to BY REFERENCE
or BY VALUE
(the default value). BY REFERENCE
preserves XML node identity for subsequent operations. BY VALUE
means that node identity need not be preserved.
SET XQUERY CONTEXT
– Specify a context item for subsequent XQUERY
evaluations.
See Also:
SQL*Plus User's Guide and ReferencePrevious sections in this chapter have shown how to invoke XQuery from SQL. This section provides examples of using XQuery with the Oracle APIs for PL/SQL, JDBC, and Oracle Data Provider for .NET (ODP.NET).
Example 17-21 Using XQuery with PL/SQL
This example 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.
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 ora:view("PURCHASEORDER") ' || 'where count($i/PurchaseOrder/LineItems/LineItem) = $itemno/itemNode ' || 'and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id/idNode ' || 'return $i/PurchaseOrder/LineItems'' ' || 'PASSING XMLElement("itemNode", :1) AS "itemno", XMLElement("idNode", :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 17-22 Using XQuery with JDBC
This example shows how to use XQuery with JDBC, binding variables by position with the PASSING
clause of SQL function XMLTable
.
import java.sql.*; import oracle.sql.*; import oracle.jdbc.driver.*; 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:ora10gR2", "oe", "oe"); String xqString = "SELECT column_value" + "FROM XMLTable('for $i in ora:view(\"PURCHASEORDER\") " + "where $i/PurchaseOrder/Reference= $ref/refNode " + "return $i/PurchaseOrder/LineItems/LineItem/Description/text()' " + "PASSING XMLElement(\"refNode\", ?) 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()); } 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 17-23 Using XQuery with ODP.NET and C#
This example 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.
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=oe;Data Source=ora10gr2"; 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 ora:view(\"PURCHASEORDER\") "); blr.Append(" where count($i/PurchaseOrder/LineItems/LineItem) = $itemno/itemNode "); blr.Append(" and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id/idNode "); blr.Append(" return $i/PurchaseOrder/LineItems\' "); blr.Append(" PASSING XMLElement(\"itemNode\", :nbitems) AS \"itemno\", XMLElement(\"idNode\", :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 out 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 supports the latest definition of the XQuery language. Because the language definition is an ongoing process, some areas of the language that are not yet firmly established are unsupported by Oracle XML DB or supported in a limited manner. This limits any impact on early adopters when the language definition evolves. Oracle participates vigorously in the definition of the XQuery language, is committed to full XQuery support, and will continue to remain at the forefront of XQuery development. For the latest status of the Oracle XML DB XQuery implementation, please consult the current version of Oracle Database Read Me.
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 areas where the Oracle XML DB XQuery implementation departs from the standard.
The XQuery specification specifies 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 timestamps with missing time zones are automatically converted to UTC.
Invalid XPath expressions – Whenever it can determine at query compile time that an XPath expression is invalid, Oracle XML DB raises an error; it does not return the empty sequence.
Ordering mode – In Oracle XML DB, the default ordering mode is unordered
. You can use an ordering mode declaration in the prolog of an XQuery expression to set the ordering mode for the expression.
Collations – Oracle XML DB uses the SQL collation rules, based on the current settings for parameters NLS_LANG
, NLS_COMP
, and NLS_SORT
. You can add a default collation
clause to a query to force the standard XQuery collation rules.
Boundary condition differences – The SQL behavior is generally used for XQuery in Oracle XML DB, if the SQL behavior differs from the standard XQuery behavior. Examples include:
doc
, collection
– When XQuery functions fn:doc
and fn:collection
are passed an invalid file or collection name, the XQuery standard requires and error to be raised, but Oracle XML DB returns an empty sequence instead. In particular, this means that a name that does not point to an Oracle XML DB Repository resource results in an empty sequence.
contains
– The XQuery standard requires the result of fn:contains
to be true when the first argument is a zero-length string, but Oracle XML DB returns false in this case.
mod
– Evaluation of x mod 0
, for any number x, is required by the the XQuery standard to return NaN
(not a number), but Oracle XML DB returns x instead.
+0
and -0
– The XQuery standard distinguishes positive zero from negative zero, but Oracle XML DB does not: both are represented as 0
, and they are treated equally.
Empty string and NULL
– The XQuery standard distinguishes an empty string from NULL
, but Oracle XML DB does not: they are treated equally.
There is currently no support for the following optional XQuery features defined by the W3C:
Schema Import Feature
Schema Validation Feature
Module Feature
In addition to these defined optional features, the W3C specification allows an implementation to provide implementation-defined pragmas and extensions. These include the following:
Pragmas
Must-understand extensions
Static-typing extensions
The Oracle implementation does not require any such pragmas or extensions.
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:
XQuery regular-expression functions. Use the Oracle extensions for regular-expression operations, instead.
Implicit time zones, when using functions that involve durations, dates, or times. (See "Support for XQuery and SQL".)
Values of type xs:IDREF
or xs:IDREFS
, in string functions that involve nodes.
Function trace()
.
XQuery built-in functions fn:doc
and fn:collection
are essentially implementation-defined. Oracle XML DB supports these functions for all resources in Oracle XML DB Repository. Function doc
returns the repository file resource that is targeted by its URI argument; it must be a file of well-formed XML data. Function collection
is similar, but works on repository folder resources (each file in the folder must contain well-formed XML data). Each of these functions returns an empty sequence if the targeted resource is not found – it does not raise an error.
See Also:
http://www.w3.org
for the definitions of XQuery functions and operatorsFootnote Legend
Footnote 1: The value returned is in fact 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.xs
is predefined for the XML Schema namespace, http://www.w3.org/2001/XMLSchema
.?
) here is a zero-or-one occurrence indicator that indicates that the argument can be the empty sequence. See "XQuery Expressions".?
) here is a zero-or-one occurrence indicator that indicates that the argument can be the empty sequence. See "XQuery Expressions".*
) here is a zero-or-more occurrence indicator that indicates that the argument can be a possibly empty sequence of document nodes of type element. See "XQuery Expressions".