When XML documents are stored or generated in a query, the most common question is whether a document contains something: a business-class ticket, an error element, a given attribute value. XMLEXISTS answers it. It is a condition, true when an XQuery expression returns at least one item, and it belongs in WHERE and CASE.
Code for This Guide
The main example is in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
xmlexists('xquery' passing expr [as "var"] [, expr as "var" ...])The condition is true when the XQuery returns a non-empty sequence and false when it returns nothing. In Oracle AI Database 26ai the result can also be selected as a BOOLEAN value.
Test a Document
The first column of this query uses XMLEXISTS to check whether a booking document has a business ticket.
Example:
with docs as (
select xmltype('<booking ref="K7QX2P">
<ticket cabin="BUSINESS"/><ticket cabin="ECONOMY"/>
</booking>') as doc
from dual
)
select xmlexists('/booking/ticket[@cabin="BUSINESS"]' passing doc) as has_business,
xmlcast(xmlquery('count(/booking/ticket)' passing doc returning content) as number)
as tickets,
xmlserialize(content
xmlquery('for $t in /booking/ticket return <c>{data($t/@cabin)}</c>'
passing doc returning content)) as flwor
from docs;Output:
HAS_BUSINESS TICKETS FLWOR _______________ __________ ________________________________ true 2 <c>BUSINESS</c><c>ECONOMY</c>
The predicate [@cabin="BUSINESS"] filters the ticket elements, and XMLEXISTS is true because one matches.
Filter Rows with a Variable
In WHERE, XMLEXISTS keeps the rows whose XML matches. Here each booking of customer 12 is built into a small XML document, and the condition keeps the bookings with a business ticket. The cabin is passed as the variable $c rather than written into the path.
Example:
-- filter rows by the content of an XML column, with a bound variable
with bookings_xml as (
select b.booking_ref,
xmlelement("booking", xmlagg(xmlelement("ticket", xmlattributes(t.cabin as "cabin"))))
as doc
from bookings b join tickets t on t.booking_id = b.booking_id
where b.customer_id = 12
group by b.booking_ref
)
select booking_ref
from bookings_xml
where xmlexists('/booking/ticket[@cabin = $c]' passing doc, 'BUSINESS' as "c")
order by booking_ref;Output:
BOOKING_REF ______________ CUCRPP E2LRK6 HJTM5J NBMP33 XHHAH7 ZW2K9E 6 rows selected.
Six of the customer's eight bookings include business tickets. Passing values as variables keeps the XQuery constant, so the statement can be reused with bind variables.
Use Predicates, Not Comparisons
XMLEXISTS tests whether the XQuery returns anything, not whether it returns true. A comparison returns a boolean, and even false is an item, so the condition is true. Put the comparison in a predicate in square brackets, which returns the node only when it matches.
Example:
select xmlexists('/booking/@vip = "yes"' passing xmltype('<booking vip="no"/>')) as comparison,
xmlexists('/booking[@vip = "yes"]' passing xmltype('<booking vip="no"/>')) as predicate
from dual;Output:
COMPARISON PREDICATE _____________ ____________ true false
Things to Know
- An XMLIndex on an XMLType column can speed up XMLEXISTS on large tables.
- XMLEXISTS replaces the deprecated EXISTSNODE function.
Related Guides
Conclusion
XMLEXISTS is true when an XQuery expression finds something in an XML value. Use it in WHERE to filter rows by the content of their XML, pass values as variables, and write predicates in square brackets rather than bare comparisons, which always count as found.
