XMLEXISTS predicate

The XMLEXISTS predicate tests whether an XQuery expression returns a sequence of one or more items.

Read syntax diagramSkip visual syntax diagramXMLEXISTS(xquery-expression-constant PASSING1BY REF,xquery-argument)
xquery-argument
Read syntax diagramSkip visual syntax diagramxquery-context-item-expressionxquery-variable-expression2ASidentifierBY REF
Notes:
  • 1 The data type cannot be DECFLOAT.
  • 2 The data type of the expression cannot be DECFLOAT.
xquery-expression-constant
Specifies an SQL character string constant that is interpreted as an XQuery expression. The constant string is converted directly to UTF-8 without conversion to the database or section code page. The XQuery expression executes using an optional set of input XML values, and returns an output sequence that is tested to determine the result of the XMLEXISTS predicate. The value for xquery-expression-constant must not be an empty string or a string of blank characters (SQLSTATE 10505).
PASSING
Specifies input values and the manner in which these values are passed to the XQuery expression specified by xquery-expression-constant. By default, every unique column name that is in the scope where the function is invoked is implicitly passed to the XQuery expression using the name of the column as the variable name. If an identifier in a specified xquery-argument matches an in-scope column name, then the explicit xquery-argument is passed to the XQuery expression overriding that implicit column.
BY REF
Specifies that the default passing mechanism is by reference for any xquery-variable-expression of data type XML. When XML values are passed by reference, the XQuery evaluation uses the input node trees, if any, directly from the specified input expressions, preserving all properties, including the original node identities and document order. If two arguments pass the same XML value, node identity comparisons and document ordering comparisons involving some nodes contained between the two input arguments might refer to nodes within the same XML node tree.

This clause has no impact on how non-XML values are passed. The non-XML values create a new copy of the value during the cast to XML.

xquery-argument
Specifies an argument that is to be passed to the XQuery expression specified by row-xquery-expression-constant. The method through which row-xquery-argument is used in the XQuery expression depends on whether the argument is specified as an xquery-context-item-expression or an xquery-variable-expression.
  • If the resulting value is of type XML, it becomes an input-xml-value. A null XML value is converted to an XML empty sequence.
  • If the resulting value is not of type XML, it must be castable to the XML data type. A null value is converted to an XML empty sequence. The converted value becomes an input-xml-value.
When the xquery-expression-constant is evaluated, an XQuery variable is presented with a value equal to input-xml-value and a name specified by the AS clause.
xquery-context-item-expression
xquery-context-item-expression specifies the initial context item in the XQuery expression specified by xquery-expression-constant. The value of the initial context item is the result of xquery-context-item-expression cast to XML. xquery-context-item-expression must not be specified more than one time.
xquery-context-item-expression must not be a sequence of more than one item. If the result of xquery-context-item-expression is an empty string, the XQuery expression is evaluated with the initial context item set to an XML empty string.
If the xquery-context-item-expression is not specified or is an empty string, the initial context item in the XQuery expression is undefined, and the XQuery expression must not reference the initial context item. An XQuery variable is not created for the context item expression.
If the xquery-context-expression is not specified or the input-xml-value that results from the xquery-context-expression is an XML empty sequence, the initial context item is undefined. If the XQuery expression refers to the initial context item, it must be specified with a value that is not an XML empty sequence.
xquery-variable-expression
Specifies an SQL expression whose value is available to the XQuery expression specified by xquery-expression-constant during execution. The expression cannot contain a sequence reference (SQLSTATE 428F9) or an OLAP function (SQLSTATE 42903). The data type of the expression cannot be DECFLOAT.
AS identifier
Specifies that the value generated by xquery-variable-expression will be passed to xquery-expression-constant as an XQuery variable. The variable name will be identifier. The leading dollar sign ($) that precedes variable names in the XQuery language is not included in identifier. The identifier must be a valid XQuery variable name and is restricted to an XML NCName. The identifier must not be greater than 128 bytes in length. Two arguments within the same PASSING clause cannot use the same identifier (SQLSTATE 42711).
BY REF
Indicates that an XML input value is to be passed by reference. When XML values are passed by reference, the XQuery evaluation uses the input node trees, if any, directly from the specified input expressions, preserving all properties, including the original node identities and document order. If two arguments pass the same XML value, node identity comparisons and document ordering comparisons involving some nodes contained between the two input arguments might refer to nodes within the same XML node tree. If BY REF is not specified following an xquery-variable-expression, XML arguments are passed by way of the default passing mechanism that is provided through the syntax that follows the PASSING keyword. This option cannot be specified for non-XML values. When a non-XML value is passed, the value is converted to XML; this process creates a copy.

Notes

The XMLEXISTS predicate cannot be:
  • Part of the ON clause that is associated with a JOIN operator or a MERGE statement (SQLSTATE 42972)
  • Part of the GENERATE KEY USING or RANGE THROUGH clause in the CREATE INDEX EXTENSION statement (SQLSTATE 428E3)
  • Part of the FILTER USING clause in the CREATE FUNCTION (External Scalar) statement, or the FILTER USING clause in the CREATE INDEX EXTENSION statement (SQLSTATE 428E4)
  • Part of a check constraint or a column generation expression (SQLSTATE 42621)
  • Part of a group-by-clause (SQLSTATE 42822)
  • Part of an argument for an aggregate function (SQLSTATE 42607)

An XMLEXISTS predicate that involves a subquery might be restricted by statements that restrict subqueries.

Example

   SELECT c.cid FROM customer c
     WHERE XMLEXISTS('$d/*:customerinfo/*:addr[ *:city = "Aurora" ]'
       PASSING info AS "d")