XMLQUERY operator

XMLQUERY is a SQL/XML operator that you can use to query XML values in SQL.

The XMLQUERY operator has two arguments, an XML query expression and a Derby XML value.

See "XML data types and operators" in the Derby Developer's Guide for more information.

Syntax

XMLQUERY ( xqueryStringLiteral
    PASSING BY REF xmlValueExpression 
    [ RETURNING SEQUENCE [ BY REF ] ]
    EMPTY ON EMPTY
    )

xqueryStringLiteral: 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 xqueryStringLiteral argument must also be an XPath expression. Derby does not support full XQuery, only the XPath subset. If it cannot compile or execute the query argument, Derby throws an SQLException. See http://www.w3.org/TR/xpath for more information on XPath expressions.
xmlValueExpression: Must be an XML data value and must constitute a well-formed SQL/XML document. The xmlValueExpression 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 a Derby XMLQUERY operation, 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 XMLQUERY operator behaves the same whether the keywords are present or not. For more information on passing mechanisms, see the SQL/XML specification.
RETURNING SEQUENCE: Optional keywords that describe the only XML type returned by the Derby XMLQUERY operator. Since SEQUENCE is also the default return type, the XMLQUERY operator behaves the same whether the keywords are present or not. For more information on the different XML return types, see the SQL/XML specification.
EMPTY ON EMPTY: Required keywords that describe the way in which XMLQUERY handles an empty result sequence. The XMLQUERY operator returns an empty sequence exactly as the sequence is. The XMLQUERY operator does not convert the empty sequence to a null value. When an empty result sequence is serialized, the result is an empty string. Derby does not consider an empty result sequence to be a well-formed XML document.

The result of the XMLQUERY operator is a value of type XML. The result represents a sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a well-formed XML document and it might not be possible to insert the result of an XMLQUERY operator into an XML column. To store the result in an XML column, the result must be a sequence with exactly one item in the sequence and the item must be a well-formed document node. The result can be viewed only in serialized form by explicitly using the XMLSERIALIZE operator.

Examples

In the x_table table, to search the XML column xcol and return the students that have an age attribute that is greater than 20, use the following statement:

SELECT ID,
    XMLSERIALIZE(
        XMLQUERY('//student[@age>20]' PASSING BY REF xcol EMPTY ON EMPTY)
    AS VARCHAR(50))
FROM x_table

The result set for this query contains a row for every row in x_table, regardless of whether or not the XMLQUERY operator actually returns results.

In the x_table table, to search the XML column xcol and return the ages for any students named BC, use the following statement:

SELECT ID, 
    XMLSERIALIZE(
        XMLQUERY('string(//student[text() = "BC"]/@age)' PASSING BY REF xcol EMPTY ON EMPTY)
    AS VARCHAR(50))
FROM x_table
WHERE
    XMLEXISTS('//student[text() = "BC"]' PASSING BY REF xcol)

The result set for this query contains a row for only the rows in x_table that have a student whose name is BC.