Before the SQL/XML standard functions existed, Oracle had its own: SYS_XMLGEN turns a value into an XML document named after it, and SYS_XMLAGG gathers documents under a ROWSET element. They are still common in older code and useful for quick conversions, especially of object type instances, so it is worth knowing how they behave.
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:
sys_xmlgen(expr [, xmlformat]) sys_xmlagg(xml_expr [, xmlformat])
SYS_XMLGEN returns an XMLType document whose root element is named after the column. For an object type, the attributes become child elements. The optional XMLFormat object changes the element name and other settings.
Generate and Aggregate
The first query turns one airport code into a document; the second aggregates the Indian airport codes under ROWSET.
Example:
select sys_xmlgen(airport_code).getstringval() as generated from airports where rownum = 1;
select xmlserialize(content sys_xmlagg(sys_xmlgen(airport_code)) indent size = 2)
as aggregated
from airports
where country_code = 'IN';Output:
GENERATED ___________________________________ <?xml version="1.0"?> <AIRPORT_CODE>AKL</AIRPORT_CODE> AGGREGATED _____________________________________ <?xml version="1.0"?> <ROWSET> <AIRPORT_CODE>BOM</AIRPORT_CODE> <AIRPORT_CODE>DEL</AIRPORT_CODE> <AIRPORT_CODE>BLR</AIRPORT_CODE> </ROWSET>
Each result is a document with an XML declaration, named after the column AIRPORT_CODE. getStringVal() is an XMLType method that returns the text.
Rename the Element
An XMLFormat object passed as the second argument names the element.
Example:
-- XMLFORMAT renames the element that SYS_XMLGEN creates
select sys_xmlgen(city, xmlformat('CITY_NAME')).getstringval() as renamed
from airports
where airport_code = 'NRT';Output:
RENAMED _______________________________ <?xml version="1.0"?> <CITY_NAME>Tokyo</CITY_NAME>
SYS_XMLGEN Compared with SQL/XML
| Task | Oracle function | SQL/XML standard |
|---|---|---|
| Element from a value | SYS_XMLGEN | XMLELEMENT |
| Combine rows | SYS_XMLAGG | XMLAGG |
| Attributes, nesting, fragments | Limited | XMLATTRIBUTES, XMLFOREST, XMLCONCAT |
Prefer the SQL/XML functions in new code: they are standard, give full control over names and structure, and do not add an XML declaration to every value.
Related Guides
Conclusion
SYS_XMLGEN turns a value or object into an XML document named after it, XMLFormat renames it, and SYS_XMLAGG gathers documents under ROWSET. They remain useful for quick conversions and in older code, while XMLELEMENT and XMLAGG are the better choice for new work.
