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

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.


  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. Required fields are marked *

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