XMLELEMENT turns one row into an element. Real documents need a parent with many children: an airport's routes, an order's lines, a country's airports. XMLAGG is the aggregate that does it: it concatenates the XML values of a group, in the order you choose, into one fragment that can become the content of a parent 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:
xmlagg(xml_expr [order by expr [asc | desc] [, ...]])
Like any aggregate, XMLAGG works with GROUP BY, producing one fragment per group, or over the whole result without it.
One Child per Row
This query builds a routes element for Sydney with one to element per destination, sorted.
Example:
select xmlserialize(content
xmlelement("routes", xmlattributes(origin as "from"),
xmlagg(xmlelement("to", destination) order by destination))
indent size = 2) as routes_xml
from routes
where origin = 'SYD'
group by origin;Output:
ROUTES_XML ______________________ <routes from="SYD"> <to>AKL</to> <to>DXB</to> <to>SIN</to> </routes>
The ORDER BY inside XMLAGG controls the order of the children. Without it, the order is not guaranteed.
Nested Aggregation
An aggregate can be nested inside another in a grouped query: the inner XMLAGG builds each country's airports, and the outer one collects the countries into a single document.
Example:
-- one element per country, with its airports inside, all in one document
select xmlserialize(document
xmlelement("airports",
xmlagg(xmlelement("country", xmlattributes(country_code as "code"),
xmlagg(xmlelement("airport", city) order by city))
order by country_code))
indent size = 2) as doc
from airports
where country_code in ('IN', 'JP', 'GB')
group by country_code;Output:
DOC
___________________________________
<airports>
<country code="GB">
<airport>London</airport>
</country>
<country code="IN">
<airport>Bengaluru</airport>
<airport>Delhi</airport>
<airport>Mumbai</airport>
</country>
<country code="JP">
<airport>Tokyo</airport>
</country>
</airports>GROUP BY country_code applies to the inner aggregate; the outer XMLAGG then runs over the grouped rows, giving one row with the whole document.
Things to Know
- XMLAGG ignores NULL values, so a CASE that returns NULL leaves no element behind.
- The result is an XMLType of any size, so it does not hit the 4,000- or 32,767-byte limits that LISTAGG has with VARCHAR2.
- For JSON, JSON_ARRAYAGG does the same job.
Related Guides
- Oracle XMLELEMENT Function
- Oracle JSON_ARRAYAGG Function
- Oracle SQL Query to Use LISTAGG for String Aggregation
Conclusion
XMLAGG concatenates the XML values of a group into one fragment, ordered by its ORDER BY, so a parent element can hold one child per row. Nest it inside another XMLAGG to build multi-level documents in a single query.
