Oracle generate XML from SQL

XML is used in numerous applications and application designs because of all the good and all the bad reasons. If you are using XML correctly or incorrectly in your application design is out of scope of this blogpost. Whatever the reason, in some cases you need to generate a XML file from the result set of your database SQL query. There are several ways of doing this and most of them involve some custom coding. The Oracle database is however also equipped with a simple solution to return your result set in a XML format. If you are in need to have it returned in a XML format you can make use of the  DBMS_XMLGEN.GETXML options that the database provides you.

When you use DBMS_XMLGEN.GETXML in the most basic way your XML will be returned as a CLOB object. As a example we have a simple query on a table named testtable as shown below

SELECT 
      tst.name,
      tst.location 
FROM 
    testtable tst
WHERE 
    tst.location NOT LIKE ('Amsterdam')

This provides the result as shown below as can be expected from a simple SQL select statement:

NAME                 LOCATION                                         
-------------------- --------------------------------------------------
Johan                Utrecht                                            
Krista               Utrecht                                            
Johan                De Meern                                           
Martin               De Meern 

However what we wanted to do is to have the resultset returned as CLOB object which holds a XML structure with the return set. To be able to do so we have to make use of DBMS_XMLGEN.GETXML. This is done in the below example. Do make note of the escape character for '. If you do not use it in this case you will run into an error.

SELECT DBMS_XMLGEN.GETXML
('
  SELECT 
        tst.name,
        tst.location 
  FROM 
      testtable tst
  WHERE 
      tst.location NOT LIKE (''Amsterdam'')
  ')
FROM 
    dual;

This query will return you a CLOB object which holds the following XML structure:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<NAME>Johan</NAME>
<LOCATION>Utrecht</LOCATION>
</ROW>
<ROW>
<NAME>Krista</NAME>
<LOCATION>Utrecht</LOCATION>
</ROW>
<ROW>
<NAME>Johan</NAME>
<LOCATION>De Meern</LOCATION>
</ROW>
<ROW>
<NAME>Martin</NAME>
<LOCATION>De Meern</LOCATION>
</ROW>
</ROWSET>

This is the most simple and most easy way to do this. You can however make it more usable and more designed for your application and use the CLOB object somewhere else in your code or store it in a table... this however is the first step in building XML directly in your database.