SQL XMLEXISTS

What is SQL XMLEXISTS?

XMLEXISTS evaluates an XQuery expression against XML data and returns a Boolean indicating whether the query yields at least one node.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL XMLEXISTS: Oracle, PostgreSQL, IBM Db2, SQL Server 2017+ (as part of XML Data Type Methods), MariaDB 10.3+, Amazon Redshift (partial), H2 (partial)

SQL XMLEXISTS Full Explanation

XMLEXISTS is part of the SQL/XML standard (ISO/IEC 9075-14) and allows relational queries to test the existence of XML nodes directly in SQL statements. It takes an XQuery expression and one or more XML or SQL values to bind to that expression. If the XQuery returns a non-empty sequence, XMLEXISTS returns TRUE; otherwise it returns FALSE. The function is deterministic and side-effect-free. Implementations typically map the Boolean result to their native Boolean type or to 1/0. Key points:- Uses full XQuery 1.0 syntax (or a subset, depending on the database).- PASSING clause binds context items or external variables in the XQuery.- BY REF (default) passes a reference to the XML value; BY VALUE creates an internal copy. BY VALUE can be slower but isolates the query from subsequent changes to the XML.- In most dialects, XMLEXISTS can appear in SELECT, WHERE, HAVING, CHECK constraints, triggers, and views.- NULL handling: if any argument is NULL, the result is NULL in PostgreSQL and SQL Server; Oracle and DB2 treat NULL as an empty sequence, returning FALSE.

SQL XMLEXISTS Syntax

-- Standard SQL
XMLEXISTS (
  XQUERY xquery_expression
  PASSING [BY REF | BY VALUE] value_expression [AS identifier]
          [, value_expression [AS identifier] ...]
);

-- PostgreSQL shorthand
XMLEXISTS (xquery_expression PASSING [BY REF | BY VALUE] xml_value [, xml_value ...]);

SQL XMLEXISTS Parameters

xquery_expression (string) - XQuery 1.0 expression to evaluate
value_expression (XML/any) - XML or SQL value(s) bound to the XQuery using the PASSING clause

Example Queries Using SQL XMLEXISTS

-- 1. Flag products that contain a <sale> element
SELECT id,
       XMLEXISTS('/product/sale' PASSING BY REF product_xml) AS on_sale
FROM   products;

-- 2. Filter rows by author in an XML column (Oracle)
SELECT title
FROM   books
WHERE  XMLEXISTS('declare default element namespace "urn:lib";
                  //book[author = $a]' PASSING BY REF xmlcol AS "b", 'John Doe' AS "a");

-- 3. Check XML from a text literal (PostgreSQL)
SELECT XMLEXISTS('//item[@sku="A25"]' PASSING xmlparse(document '<item sku="A25"/>'));

Expected Output Using SQL XMLEXISTS

Each query returns a Boolean column (TRUE/FALSE) or filters rows accordingly
For example, query 1 adds a column on_sale that is TRUE when exists and FALSE otherwise

Use Cases with SQL XMLEXISTS

Enforce business rules in CHECK constraints (e.g., ensure an node exists).
Filter tables that store XML documents by structural criteria.
Combine relational and XML predicates in complex joins.
Data quality validation during ETL jobs.
Conditional logic in stored procedures based on XML content.

Common Mistakes with SQL XMLEXISTS

Forgetting the PASSING clause, leading to an error that no context item is defined.
Using invalid XQuery syntax (different from XPath 1.0).
Expecting XMLEXISTS to return the matching nodes; it only returns a Boolean.
Ignoring BY VALUE performance cost on large XML documents.
Assuming NULL arguments yield FALSE in all dialects (behavior varies).

First Introduced In

SQL:2006 (ISO/IEC 9075-14) and Oracle Database 10g Release 2

Frequently Asked Questions

What databases support XMLEXISTS?

Oracle, PostgreSQL, IBM Db2, SQL Server 2017+, and several others implement XMLEXISTS or an equivalent function.

Can I bind multiple values in the PASSING clause?

Yes. You can pass several XML or scalar values and reference them in the XQuery using positional or named variables, depending on the dialect.

How is XMLEXISTS different from XMLQUERY?

XMLEXISTS returns a Boolean indicating node existence, while XMLQUERY returns the actual XML or text result of the XQuery expression.

Does BY VALUE always slow down queries?

BY VALUE copies the XML data before evaluation, which can degrade performance on large documents. Use BY REF unless you need isolation from concurrent modifications.