Oracle XMLAGG Function

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.