How to Generate XML from the Oracle Database

Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.

I’ll start with creating a simple table and fill it with sample data:

To be able to use SYS_XMLGEN, I’ll create an object to map our data:

Now I can easily convert my data to XML:

It will convert each row as a separate XML, so I’ll use SYS_XMLAGG to aggregate all rows:

If I can use UTL_FILE to write this output to a file:

Here’s the content of the “test.xml” file:

As you see, it’s very easy to generate XML file from the Oracle databases with using SYS_XMLGEN. On the other hand, DBMS_XMLQUERY package provides much more simply way to generate XML:

Here’s the result:

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.


  1. Ashish

    Really useful article. Thanks 🙂

    Can you also throw some light that can we change the format of the XML here.



Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.