DBMS_XMLGEN turns the result of any query into an XML document: one element per row, one child element per column. Context settings rename the row and rowset tags, decide how NULLs appear, and limit the number of rows, which makes it a quick way to export data or feed XML-based systems.
Code for This Guide
The main examples are in the examples/pkg-xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
ctx := dbms_xmlgen.newcontext('select ...');
dbms_xmlgen.setrowsettag(ctx, 'tag');
dbms_xmlgen.setrowtag(ctx, 'tag');
dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag); -- or drop_nulls, null_attr
dbms_xmlgen.setmaxrows(ctx, n);
v_xml := dbms_xmlgen.getxml(ctx);
dbms_xmlgen.closecontext(ctx);Airports as XML
Example:
declare
ctx dbms_xmlgen.ctxhandle;
v_xml clob;
begin
ctx := dbms_xmlgen.newcontext('select airport_code as "code", city as "city", is_hub
from airports where country_code = ''IN'' order by 1');
dbms_xmlgen.setrowsettag(ctx, 'airports');
dbms_xmlgen.setrowtag(ctx, 'airport');
dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag);
dbms_xmlgen.setmaxrows(ctx, 2);
v_xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(v_xml);
dbms_output.put_line('rows: ' || dbms_xmlgen.getnumrowsprocessed(ctx));
dbms_xmlgen.closecontext(ctx);
end;
/Output:
<?xml version="1.0"?> <airports> <airport> <code>BLR</code> <city>Bengaluru</city> <IS_HUB>FALSE</IS_HUB> </airport> <airport> <code>BOM</code> <city>Mumbai</city> <IS_HUB>FALSE</IS_HUB> </airport> </airports> rows: 2 PL/SQL procedure successfully completed.
The rowset tag is airports and each row is an airport element. Column aliases in double quotes keep their lowercase names, while IS_HUB stays uppercase. SETMAXROWS stops after two rows, and GETNUMROWSPROCESSED confirms it.
No Rows, and the One-Call Form
Example:
declare
ctx dbms_xmlgen.ctxhandle;
v_xml clob;
begin
ctx := dbms_xmlgen.newcontext(
'select airport_code from airports where country_code = ''ZZ''');
v_xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line('no rows: ' || nvl(to_char(dbms_lob.getlength(v_xml)), 'NULL'));
dbms_xmlgen.closecontext(ctx);
end;
/
-- the one-call SQL form
select dbms_xmlgen.getxml('select airport_code, city from airports
where airport_code = ''DXB''') as xml
from dual;Output:
no rows: NULL PL/SQL procedure successfully completed. XML _____________________________________ <?xml version="1.0"?> <ROWSET> <ROW> <AIRPORT_CODE>DXB</AIRPORT_CODE> <CITY>Dubai</CITY> </ROW> </ROWSET>
A query with no rows returns NULL rather than an empty document, so check for it. GETXML also takes the query text directly, with the default ROWSET and ROW tags, and works in SQL.
Things to Know
- Column names become element names, so give expressions aliases that are valid XML names.
- SETNULLHANDLING decides whether NULL columns are left out, written as empty tags, or marked with an attribute.
- For building XML with full control over structure, the SQL functions XMLELEMENT and XMLAGG are more flexible.
Related Guides
Conclusion
DBMS_XMLGEN converts query results into XML with one element per row and column. Set the tags, NULL handling, and row limit on a context, check for NULL when no rows match, and close the context when done.
