XML is still everywhere in integration: invoices, travel industry messages, configuration files, and SOAP services. When the data lives in tables, XMLELEMENT builds the XML in SQL. It creates an element with a name and content, XMLATTRIBUTES adds attributes to it, and XMLFOREST creates a series of simple elements from columns, so one query turns a row into a well-formed XML element.
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:
xmlelement("name" [, xmlattributes(expr as "name" [, ...])] [, content, ...])
xmlforest(expr as "name" [, ...])Put element and attribute names in double quotes to keep their case; without quotes, Oracle uppercases them. Content can be text, values, or other XML, such as nested XMLELEMENT calls.
Build an Element from a Row
This query builds an airport element with two attributes, a nested city element, and two more elements from XMLFOREST. XMLSERIALIZE with INDENT turns the result into readable text.
Example:
select xmlserialize(content
xmlelement("airport",
xmlattributes(airport_code as "code", is_hub as "hub"),
xmlelement("city", city),
xmlforest(time_zone as "timeZone", elevation_ft as "elevation"))
indent size = 2) as airport_xml
from airports
where airport_code = 'DXB';Output:
AIRPORT_XML ____________________________________ <airport code="DXB" hub="TRUE"> <city>Dubai</city> <timeZone>Asia/Dubai</timeZone> <elevation>62</elevation> </airport>
Values are converted to text, and characters such as < and & are escaped automatically, so the result is always well formed. The BOOLEAN column is_hub becomes TRUE.
NULL Values
XMLFOREST and XMLELEMENT treat NULL differently: XMLFOREST leaves the element out, while XMLELEMENT creates an empty one.
Example:
-- XMLFOREST leaves out NULL values; XMLELEMENT keeps an empty element
select xmlserialize(content
xmlelement("employee", xmlattributes(employee_id as "id"),
xmlforest(last_name as "name", salary as "salary", commission_pct as "commission"),
xmlelement("bonus", commission_pct))
indent size = 2) as employee_xml
from employees
where employee_id = 150;Output:
EMPLOYEE_XML ___________________________ <employee id="150"> <name>Kapoor</name> <salary>21000</salary> <bonus/> </employee>
Employee 150 has no commission, so there is no commission element from XMLFOREST, but there is an empty bonus element from XMLELEMENT. Choose the function that matches what the receiving system expects.
Things to Know
- XMLELEMENT returns an XMLType value; use XMLSERIALIZE to get text, or store the value in an XMLType column.
- To build one parent with a child per row, combine XMLELEMENT with the XMLAGG aggregate.
- An unquoted name is uppercased: xmlelement(airport, 'x') produces <AIRPORT>x</AIRPORT>.
Related Guides
- Oracle JSON_OBJECT Function, the JSON counterpart
Conclusion
XMLELEMENT creates XML elements in SQL, with XMLATTRIBUTES for attributes and XMLFOREST for a run of simple elements from columns. Quote names to keep their case, rely on automatic escaping, and choose XMLFOREST when NULL values should be omitted.
