Oracle XMLCAST Function

XMLQUERY returns XML, even when the result is a single number or date. To use that value in SQL, in arithmetic, a comparison, or an INSERT, it must become a SQL type. XMLCAST does that conversion. Combined with XMLQUERY, it is the standard replacement for the deprecated EXTRACTVALUE 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:

xmlcast(xml_expr as datatype)

xml_expr is usually an XMLQUERY; datatype is a SQL scalar type such as NUMBER, VARCHAR2(n), DATE, or TIMESTAMP.

Read Values from XML

This query parses a fare document with XMLPARSE and reads the amount, an element's text, and the currency, an attribute.

Example:

select xmlcast(xmlquery('/fare/amount/text()' passing x returning content) as number)
         as amount,
       xmlcast(xmlquery('/fare/@currency' passing x returning content) as varchar2(3))
         as currency
from   (select xmlparse(document '<fare currency="USD"><amount>612.50</amount></fare>')
                 as x);

Output:

   AMOUNT CURRENCY
_________ ___________
    612.5 USD

Numbers and Dates

Cast to the type you will use. A date in ISO format (YYYY-MM-DD) casts to DATE directly, and a number can be used in arithmetic immediately.

Example:

with d as (
  select xmltype('<flight><no>NM101</no><date>2026-03-15</date><delay>34</delay></flight>') as x
  from   dual
)
select xmlcast(xmlquery('/flight/no/text()' passing x returning content) as varchar2(6)) as flight_no,
       xmlcast(xmlquery('/flight/date/text()' passing x returning content) as date)       as flight_date,
       xmlcast(xmlquery('/flight/delay/text()' passing x returning content) as number) + 1 as delay_plus_one
from   d;

Output:

FLIGHT_NO    FLIGHT_DATE       DELAY_PLUS_ONE
____________ ______________ _________________
NM101        15-MAR-2026                   35

Make Sure the Path Matches One Node

If the path matches several nodes, XMLCAST does not fail: it joins their text into one value.

Example:

select xmlcast(xmlquery('/a/b' passing xmltype('<a><b>1</b><b>2</b></a>') returning content)
               as number) as two_nodes
from   dual;

Output:

   TWO_NODES
____________
          12

The two b elements, 1 and 2, became the number 12. Write paths that select exactly one node, with a position such as /a/b[1] or a predicate, and use XMLTABLE when there are several values.

Related Guides

• Oracle XMLQUERY Function
• Oracle XMLTABLE Function

Conclusion

XMLCAST converts an XML value, typically the result of XMLQUERY, into a SQL scalar type such as NUMBER, VARCHAR2, or DATE. Make sure the path selects a single node, since several nodes are silently joined, and use XMLTABLE for multiple values.