Oracle XMLQUERY Function

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

• Oracle XMLTABLE Function
• Oracle JSON_QUERY Function

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.