This article presents methods to transform an SQL to XML format with minimum effort possible.

Let’s take simple SQL

SELECT owner, table_name 
  FROM dba_tables
 WHERE table_name LIKE 'USER%' and owner='SYS';

OWNER                          TABLE_NAME                   
------------------------------ ------------------------------
SYS                            USER_HISTORY$                 
SYS                            USER_ASTATUS_MAP              
SYS                            USER$

and try to transform it into XML format

• XMLTYPE method

SELECT XMLTYPE(CURSOR(
                      SELECT owner, table_name 
                        FROM dba_tables
                       WHERE table_name LIKE 'USER%' and owner='SYS'
                     )
              ).getclobval() 
  FROM dual;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_HISTORY$</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_ASTATUS_MAP</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER$</TABLE_NAME>
 </ROW>
</ROWSET>

• DBMS_XMLGEN method

SELECT dbms_xmlgen.getxmltype
       (
         'SELECT owner, table_name 
           FROM dba_tables
          WHERE table_name LIKE ''USER%'' and owner=''SYS'''
       ).getclobval()    
FROM dual;

<ROWSET>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_HISTORY$</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_ASTATUS_MAP</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER$</TABLE_NAME>
 </ROW>
</ROWSET>

• Using XMLELEMENT, XMLAGG functions. Output for this method is generated in one line but I formatted it for better presentation.

SELECT 
    XMLELEMENT("ROWSET",
    XMLAGG(
     XMLELEMENT("ROW",
       XMLELEMENT("OWNER", owner), 
       XMLELEMENT("TABLE_NAME", table_name)))).getclobval()
  FROM dba_tables
 WHERE table_name LIKE 'USER%' 
   AND owner='SYS'

<ROWSET>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_HISTORY$</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER_ASTATUS_MAP</TABLE_NAME>
 </ROW>
 <ROW>
  <OWNER>SYS</OWNER>
  <TABLE_NAME>USER$</TABLE_NAME>
 </ROW>
</ROWSET>

Following two functions are useful in package UTL_I18N:

• ESCAPE_REFERENCE – converts special xml characters <,>,&,’,” to escape sequence

SELECT 
    UTL_I18N.ESCAPE_REFERENCE('<tag>data in tag</tag>') 
  FROM dual;

UTL_I18N.ESCAPE_REFERENCE('<TAG>DATAINTAG</TAG>')  
---------------------------------------------------
&lt;tag&gt;data in tag&lt;/tag&gt;

• UNESCAPE_REFERENCE – opposite to escape_reference

SELECT 
    UTL_I18N.UNESCAPE_REFERENCE('<tag>data in tag</tag>') 
  FROM dual;

UTL_I18N.UNESCAPE_REFERENCE('<TAG>DATAINTAG</TAG>') 
----------------------------------------------------------------
<tag>data in tag</tag>

Have a fun 🙂

Tomasz