# XMLEXISTS operator

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.

#### Syntax

    XMLEXISTS ( xquery-string-literal
        PASSING BY REF xml-value-expression [ BY REF ] )

`xquery-string-literal`
  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/.
`xml-value-expression`
  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 an error.
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

The result of the XMLEXISTS operator is a SQL boolean value that is based on the results from evaluating the `xquery-string-literal` against the `xml-value-expression`. The XMLEXISTS operator returns:

UNKNOWN
  When the `xml-value-expression` is null.
TRUE
  When the evaluation of the specified query expression against the specified xml-value returns a non-empty sequence of nodes or values.
FALSE
  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.

#### Examples

In the `x_table` table, to determine if the `xcol` XML column for each row has an element called `student` with an `age` attribute equal to 20, use this statement:

    SELECT id, XMLEXISTS('//student[@age=20]' PASSING BY REF xcol)
        FROM x_table

In the `x_table` 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)

You can create the `x_table` table with a check constraint that limits which XML values can be inserted into the `xcol` XML column. In this example, the constraint is that the column has at least one `student` element 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)) )

#### Usage note

Derby requires that a JAXP parser, such as Apache Xerces, and that 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.

Parent topic: Built-in functions