XMLEXISTS is an SQL/XML operator that you can use to query XML
values in SQL.
The XMLEXISTS operator has two arguments, an XML query expression
and a Derby XML value.
XMLEXISTS ( xquery-string-literal
PASSING BY REF xml-value-expression [ BY REF ] )
- Must be specified as a string literal. If this argument is specified as
a parameter, an expression that is not a literal, or a literal that is not
a string (for example an integer), Derby throws
an error. The xquery-string-literal argument must also be
an XPath expression that is supported by Apache Xalan. Derby uses
Apache Xalan to evaluate all XML query expressions. Because Xalan does not
support full XQuery, neither does Derby.
If Xalan is unable to compile or execute the query argument, Derby catches
the error that is thrown by Xalan and throws the error as a SQLException.
For more on XPath and XQuery expressions, see these Web sites: http://www.w3.org/TR/xpath and http://www.w3.org/TR/xquery/.
- Must be an XML data value and must constitute a well-formed SQL/XML document.
The xml-value-expression argument cannot be a parameter.
Derby does not perform
implicit parsing nor casting of XML values, so use of strings or any other
data type results in an error. If the argument is a sequence that is returned
by the Derby XMLQUERY operator,
the argument is accepted if it is a sequence of exactly one node that is a
document node. Otherwise Derby throws
- BY REF
- Optional keywords that describe the only value passing mechanism supported
by Derby. Since BY REF
is also the default passing mechanism, the XMLEXISTS operator behaves the
same whether the keywords are present or not. For more information on passing
mechanisms, see the SQL/XML specification.
Operator results and combining with other operators
result of the XMLEXISTS operator is a SQL boolean value that is based on the
results from evaluating the xquery-string-literal
. The XMLEXISTS operator returns:
- When the xml-value-expression is null.
- When the evaluation of the specified query expression against the specified
xml-value returns a non-empty sequence of nodes or values.
- When evaluation of the specified query expression against the specified
xml-value returns an empty sequence.
The XMLEXISTS operator does not return the actual results
from the evaluation of the query. You must use the XMLQUERY operator to retrieve
the actual results.
Since the result of the XMLEXISTS operator is an
SQL boolean data type, you can use the XMLEXISTS operator wherever a boolean
function is allowed. For example, you can use the XMLEXISTS operator as a
check constraint in a table declaration or as a predicate in a WHERE clause.
In the x_table
to determine if the xcol
XML column for each row has an element
with an age
to 20, use this statement:
SELECT id, XMLEXISTS('//student[@age=20]' PASSING BY REF xcol)
In the x_table
to return the ID for every row whose xcol
XML column is non-null
and contains the element /roster/student
, use this statement:
SELECT id FROM x_table WHERE XMLEXISTS('/roster/student' PASSING BY REF xcol)
can create the x_table
table with a check constraint that
limits which XML values can be inserted into the xcol
column. In this example, the constraint is that the column has at least one student
with an age
attribute with a value that is less than 25.
To create the table, use this statement:
CREATE TABLE x_table ( id INT, xcol XML CHECK (XMLEXISTS ('//student[@age < 25]' PASSING BY REF xcol)) )
that a JAXP parser (such as Apache Xerces) and Apache Xalan are listed
in the Java classpath for the XML functions to work. If either the JAXP parser
or Xalan is missing from the classpath, attempts to use the XMLEXISTS operator
will result in an error. In some situations, you may need to take steps to place
the parser and Xalan in your classpath. See "XML data types and operators" in
the Derby Developer's Guide for details.