XMLQUERY runs an XQuery expression against XML and returns the result as XML. The expression can be a simple path such as /booking/ticket, a function such as count(), or a full FLWOR expression that filters, sorts, and builds new XML. It is the general-purpose way to query XML inside SQL, and the replacement for the deprecated EXTRACT function.
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. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
xmlquery('xquery' passing expr [as "var"] [, expr as "var" ...] returning content)The first value in PASSING without a name becomes the context item, which paths starting with / refer to. Named values become XQuery variables such as $var. RETURNING CONTENT returns the result as an XMLType fragment.
Paths, Functions, and FLWOR
This query tests, counts, and transforms the tickets of a booking document. XMLEXISTS checks for a business ticket, XMLQUERY with count() counts tickets, and a FLWOR expression returns a new element for each 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>
XMLQUERY returns XML, so a number such as the count is wrapped in XMLCAST to become a SQL NUMBER. data($t/@cabin) returns the value of the cabin attribute, which the FLWOR puts inside a new c element.
Filter and Sort with FLWOR
A FLWOR expression (for, let, where, order by, return) works like a small query of its own. This one keeps aircraft with more than 200 seats, sorts them by seats, and returns a new element for each.
Example:
-- FLWOR: for, where, order by, return
select xmlserialize(content
xmlquery('for $a in /fleet/aircraft
where $a/seats > 200
order by $a/seats descending
return <big tail="{$a/@tail}">{data($a/seats)}</big>'
passing xmltype('<fleet>
<aircraft tail="A6-NAA"><seats>162</seats></aircraft>
<aircraft tail="A6-NAH"><seats>325</seats></aircraft>
<aircraft tail="A6-NAK"><seats>354</seats></aircraft>
</fleet>')
returning content)) as big_aircraft
from dual;Output:
BIG_AIRCRAFT ___________________________________________________________ <big tail="A6-NAK">354</big><big tail="A6-NAH">325</big>
The braces in the return clause evaluate expressions inside the constructed element: {$a/@tail} copies the attribute value, and {data($a/seats)} the seat count.
Things to Know
- To get a single scalar value, wrap XMLQUERY in XMLCAST; to get rows, use XMLTABLE instead.
- An expression that matches nothing returns an empty result, not an error.
- XQuery is case-sensitive: element names in the path must match the document exactly.
Related Guides
Conclusion
XMLQUERY evaluates XQuery against XML passed into it and returns XML: paths select nodes, functions compute values, and FLWOR expressions filter, sort, and build new elements. Combine it with XMLCAST for scalar values and XMLSERIALIZE for text.
