Converting Between Oracle Data and XML

Introduction

This blog post describes various mechanisms for converting between Oracle data and XML, and vice versa, focusing on the former. Many blog posts have been written on this subject. Most are very short and only demonstrate the usage but not the associated issues and I’ll try to dig a little deeper than most others and also mention the issues I’ve encountered or are aware of using these techniques.

I find combining Oracle data and XML useful for the following use cases:

• Extraction, Transformation and Load (ETL) for a datawarehouse or other types of transfers: Data is on a structured form with widespread tool (XML editors), programming language and standard support (eg XSLT, JAX, DOM etc) and is easier to transform in the process.
• Easier to manipulate in a decent XML editor with expand/collapse (hiding irrelevant data sets) and XPath functionality (used for extracting fragments of the XML document).
• Comparison: Comparing data sets for equality, eg in automated unit or regression testing. It’s easier to write a good, generic XML differ than a good, generic SQL result set differ. My next blog post will cover this.

The examples in this blog post assume that you are logged on as SCOTT and that you work in a SQL*Plus environment with the following settings and data in DEPT and EMP tables intact from installation:

set serveroutput on format truncated
set long 100000
set pagesize 50000
set linesize 1000

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff';
alter session set nls_timestamp_tz_format = 'dd.mm.yyyy hh24:mi:ss.ff tzh:tzm';

Oracle Functionality

In the following sections I’ll give an overview of the functionality Oracle provides in terms of converting between Oracle data and XML. Most of this functionality works on what Oracle calls canonical form, which is XML with the following structure (the comments <!-- ... --> are only included for illustration):

<ROWSET>
 <ROW><!-- Row 1 -->
  <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
  <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
  <!-- ... -->
  <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 </ROW>
 <ROW><!-- Row 2 -->
 <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
  <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
  <!-- ... -->
  <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 </ROW>
 <!-- ... -->
 <ROW><!-- Row n -->
  <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
  <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
  <!-- ... -->
  <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 </ROW>
</ROWSET>

So basically there’s an XML element for each column and the name of the element corresponds to the column name. There’s a <ROW> fragment for each row in the query and the root element is called <ROWSET> (all XML documents must have at most one root element).

In this blog post I’ll focus on DBMS_XMLGEN and XMLTYPE.

DBMS_XMLQUERY

DBMS_XMLQUERY provides functionality for obtaining XML data based on a dynamic SQL SELECT statement, optionally using bind parameters (all of datatype VARCHAR2). It’s possible to control names of tags generated and you can also control the date/time format (same format for all) but unfortunately ownly down to milliseconds as internally, Java’s java.text.SimpleDateFormat is used, which doesn’t support higher precision than milliseconds. Also, it’s inconvenient that it’s not possible to have empty elements with no attributes for NULL values, only either no element or an empty element with an attribute NULL="YES". XML is returned as CLOB.

There are two ways of using DBMS_XMLQUERY:

• Quick and easy in one call, but less flexible: GETXML that works on a SQLQUERY parameter of type VARCHAR2 or CLOB and returning a CLOB.
• Fully flexible by a series of calls, all working on a context handle:
  • NEWCONTEXT called with SQLQUERY parameter of type VARCHAR2 or CLOB and returning a context handle of type CTXTYPE. This handle must be used in subsequent calls.
  • One or more calls that set various options controlling the XML generation (the SET% procedures), such as NULL value handling, number of rows to process, bind variable values, tag names, etc.
  • CLOSECONTEXT called with the context handle, which closes the context and frees resources.

DBMS_XMLQUERY was introduced with Oracle 8i Release 3 and it has been superseded by DBMS_XMLGEN. DBMS_XMLQUERY is implemented in Java and hence is not supported with Oracle Database Express Edition.

DBMS_XMLGEN

DBMS_XMLGEN was introduced with Oracle Database 9i Release 1. It supersedes DBMS_XMLQUERY, is implemented in C (hence faster) and is a little more flexible than DBMS_XMLQUERY in some ways so in general you should use DBMS_XMLGEN over DBMS_XMLQUERY. Bind variable support (all of datatype VARCHAR2), XSLT support and pretty printing was introduced in Oracle Database 10g Release 1.

In general, DBMS_XMLGEN mimics the functionality in DBMS_XMLQUERY with subtle differences. One of these is that it’s not possible to pass in a query larger than 32KB, but contrary to DBMS_XMLQUERY you can also pass in a SYS_REFCURSOR, so you can use the OPEN-FOR statement that from Oracle Database 11g Release 1 supports CLOB. It’s also possible to obtain the XML as an XMLTYPE, not only a CLOB.

Here are a few examples:

Selecting all rows and columns from SCOTT.DEPT will produce the following result:

select dbms_xmlgen.getxml('select * from dept order by deptno') xml
from   dual;

XML
---------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <!-- Abbreviated... -->
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>

Selecting all rows and columns from SCOTT.EMP will produce the following result:

select dbms_xmlgen.getxml('select * from emp order by empno') xml
from   dual;

XML
--------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17.12.1980 00:00:00</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20.02.1981 00:00:00</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <!-- Abbreviated... -->
 <ROW>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>23.01.1982 00:00:00</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
</ROWSET>

It’s noted how the date values match the session NLS_DATE_FORMAT 'dd.mm.yyyy hh24:mi:ss'. It is also noted that no elements are returned for column with NULL values, eg for COMM for EMPNO = 7934. If you prefer to always have an element, either with or without an attribute indicating NULL, you can control this with the following type of code, also demonstrating how to work with the context handle:

variable xml clob

declare
  context dbms_xmlgen.ctxtype;
begin
  context := dbms_xmlgen.newcontext('select * from emp where empno = 7934');
  dbms_xmlgen.setnullhandling(context, dbms_xmlgen.empty_tag);
  :xml := dbms_xmlgen.getxml(context);
  dbms_xmlgen.closecontext(context);
end;
/

print :xml

XML
------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>23.01.1982 00:00:00</HIREDATE>
  <SAL>1300</SAL>
  <COMM/>
  <DEPTNO>10</DEPTNO>
 </ROW>
</ROWSET>

Note how the empty element <COMM/> is now included in the XML. Another alternative is:

variable xml clob

declare
  context dbms_xmlgen.ctxtype;
begin
  context := dbms_xmlgen.newcontext('select * from emp where empno = 7934');
  dbms_xmlgen.setnullhandling(context, dbms_xmlgen.null_attr);
  dbms_xmlgen.setindentationwidth(context, 2);
  :xml := dbms_xmlgen.getxml(context);
  dbms_xmlgen.closecontext(context);
end;
/

print :xml

XML
----------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
  <ROW>
    <EMPNO>7934</EMPNO>
    <ENAME>MILLER</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7782</MGR>
    <HIREDATE>23.01.1982 00:00:00</HIREDATE>
    <SAL>1300</SAL>
    <COMM xsi:nil = "true"/>
    <DEPTNO>10</DEPTNO>
  </ROW>
</ROWSET>

Note how the attribute xsi:nil is now included with a value of "true". Seen that this uses a namespace xsi a namespace specification has been added to the root element <ROWSET>. Also note that we added a call to control the indentation width for each level, in this case a value of 2 instead of the default 1.

If you work with LONG columns it’s worth knowing that DBMS_XMLGEN truncates values of LONG columns to 32KB. For more information, please refer to Adrian Billington’s blog post mentioned at the end of this blog post. I’m also describing a slightly different technique for converting LONG to CLOB in my blog post Converting a LONG Column to a CLOB on the fly.

XMLELEMENT, XMLFOREST, XMLAGG etc

Oracle provides a collections of functions that you can use to construct the XML based on your relational (optionally object-relational) data. Here’s an example on a hierarchical XML construction with employees under departments:

select xmlelement(
         "DEPARTMENTS",
         xmlagg(
           xmlelement(
             "DEPARTMENT",
             xmlforest(
               d.deptno,
               d.dname,
               (
                 select xmlagg(
                          xmlelement(
                            "EMPLOYEE",
                             xmlforest(
                               e.empno,
                               xmlcdata(e.ename) ename,
                               e.hiredate
                             )
                           )
                        )
                 from   emp e
                 where  e.deptno = d.deptno and
                        e.empno in (7369, 7499, 7934)
               ) employees
             )
           )
         )
       ) x
from   dept d;

(output wrapped and indented for improved readability)

<DEPARTMENTS>
  <DEPARTMENT>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7934</EMPNO>
        <ENAME><![CDATA[MILLER]]></ENAME>
        <HIREDATE>1982-01-23</HIREDATE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7369</EMPNO>
        <ENAME><![CDATA[SMITH]]></ENAME>
        <HIREDATE>1980-12-17</HIREDATE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7499</EMPNO>
        <ENAME><![CDATA[ALLEN]]></ENAME>
        <HIREDATE>1981-02-20</HIREDATE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
  </DEPARTMENT>
</DEPARTMENTS>

If you prefer empty tags for NULL values, you can take this approach instead, writing a little more code (it would have been nice if XMLFOREST/XMLELEMENT etc would take options for handling NULL values):

select xmlelement(
         "DEPARTMENTS",
         xmlagg(
           xmlelement(
             "DEPARTMENT",
             xmlforest(
               d.deptno,
               d.dname,
               (
                 select xmlagg(
                          xmlelement(
                            "EMPLOYEE",
                             xmlelement("EMPNO", e.empno),
                             xmlelement("ENAME", xmlcdata(e.ename)),
                             xmlelement("HIREDATE", e.hiredate),
                             xmlelement("COMM", e.comm)
                           )
                        )
                 from   emp e
                 where  e.deptno = d.deptno and
                        e.empno in (7369, 7499, 7934)
               ) employees
             )
           )
         )
       ).getclobval() x
from   dept d;

(output wrapped and indented for improved readability)

<DEPARTMENTS>
  <DEPARTMENT>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7934</EMPNO>
        <ENAME><![CDATA[MILLER]]></ENAME>
        <HIREDATE>1982-01-23</HIREDATE>
        <COMM></COMM>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7369</EMPNO>
        <ENAME><![CDATA[SMITH]]></ENAME>
        <HIREDATE>1980-12-17</HIREDATE>
        <COMM></COMM>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7499</EMPNO>
        <ENAME><![CDATA[ALLEN]]></ENAME>
        <HIREDATE>1981-02-20</HIREDATE>
        <COMM>300</COMM>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
  </DEPARTMENT>
</DEPARTMENTS>

It’s worth noting that these functions generate XML with date/time values conforming to XML Schema standards, which is great. This was changed in Oracle Database 10g Release 2 and in earlier releases the NLS settings were used (not so great). In this example we enclosed the values of ENAME in a CDATA section (starting with <![CDATA[ and ending with ]]>), which means that Oracle doesn’t have to escape the special characters that could be found in the ENAME column. The Oracle function XMLCDATA is used for this, but it has several limitations: It doesn’t support values larger than 4000 bytes and it doesn’t handle embedded ]]> characters within the text (that would terminate the CDATA section), which is normally treated by chaining several CDATA sections for the element value, ie by replacing any occurrence of ']]>' with ']]]]><![CDATA[>'.

Please be aware that DATE values with time parts are truncated to the date value only, which is very unfortunate:

select xmlelement(
         "DATETIME",
         xmlforest(
           trunc(sysdate) date_no_time,
           sysdate date_with_time,
           systimestamp timestamp_with_tz,
           localtimestamp timestamp_local_tz
         )
       ) xml
from   dual;

(output wrapped and indented for improved readability)

<DATETIME>
  <DATE_NO_TIME>2011-01-26</DATE_NO_TIME>
  <DATE_WITH_TIME>2011-01-26</DATE_WITH_TIME>
  <TIMESTAMP_WITH_TZ>2011-01-26T10:47:38.026000+01:00</TIMESTAMP_WITH_TZ>
  <TIMESTAMP_LOCAL_TZ>2011-01-26T10:47:38.026000</TIMESTAMP_LOCAL_TZ>
</DATETIME>

You can convert the dates to timestamps, but it’s a little inconvenient and this means you get fractional seconds as well, even though they’ll always be zero:

select xmlelement(
         "DATETIME",
         xmlforest(
           trunc(sysdate) date_no_time,
           cast(sysdate as timestamp) date_with_time,
           systimestamp timestamp_with_tz,
           localtimestamp timestamp_local_tz
         )
       ) xml
from   dual;

(output wrapped and indented for improved readability)

<DATETIME>
  <DATE_NO_TIME>2011-01-26</DATE_NO_TIME>
  <DATE_WITH_TIME>2011-01-26T10:50:32.000000</DATE_WITH_TIME>
  <TIMESTAMP_WITH_TZ>2011-01-26T10:47:38.026000+01:00</TIMESTAMP_WITH_TZ>
  <TIMESTAMP_LOCAL_TZ>2011-01-26T10:47:38.026000</TIMESTAMP_LOCAL_TZ>
</DATETIME>

Personally, I prefer DBMS_XMLGEN to these functions as you need to write more fiddly SQL to get it working (it would be nice if you could work on all columns with a * for instance, but instead you need to list all the columns), but I guess that’s a matter of taste.

XMLTYPE

The XMLTYPE object type has various means of conversions, eg from any object type instance to corresponding XML (CREATEXML static function and constructor taking a parameter of type "<ADT_1>") and vice versa, ie from the XMLTYPE instance to an object type instance (member procedure TOOBJECT).

Here’s an example:

select xmltype(sys.aq$_agent('name', 'address', 1)).getclobval() xml
from   dual;

XML
--------------------------------------------------------------------------------
<AQ_x0024__AGENT><NAME>name</NAME><ADDRESS>address</ADDRESS><PROTOCOL>1</PROTOCO
L></AQ_x0024__AGENT>

This output is not that user-friendly as GETCLOBVAL by default has it’s own idea on how and when to indent the elements. If you’re on Oracle Database 11g Release 1 or newer, there’s an enhanced version of GETCLOBVAL you can use:

select xmltype(sys.aq$_agent('name', 'address', 1)).getclobval(0, 2) xml
from   dual;

XML
----------------------------
<AQ_x0024__AGENT>
  <NAME>name</NAME>
  <ADDRESS>address</ADDRESS>
  <PROTOCOL>1</PROTOCOL>
</AQ_x0024__AGENT>

It may come as a surprise that the root tag isn’t called <AQ$_AGENT> but $ is replaced by _x0024_ because $ is an invalid character for XML element names.

DBMS_XMLSAVE

DBMS_XMLSAVE can be used to INSERT, UPDATE and DELETE data in a table based on XML in Oracle canonical form. It works on a context handle that you obtain, set options, call either INSERTXML, UPDATEXML or DELETEXML and then close the context again.

Example:

select *
from   dept
order  by deptno;

declare
  context dbms_xmlsave.ctxtype;
  row_count integer;
  xml_insert xmltype := xmltype('
<ROWSET>
 <ROW>
  <DEPTNO>50</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>ALBANY</LOC>
 </ROW>
 <ROW>
  <DEPTNO>60</DEPTNO>
  <DNAME>TO_BE_DELETED</DNAME>
  <LOC>N/A</LOC>
 </ROW>
</ROWSET>
  ');
  xml_update xmltype := xmltype('
<ROWSET>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>XOPERATIONSETC</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
</ROWSET>
  ');
  xml_delete xmltype := xmltype('
<ROWSET>
 <ROW>
  <DEPTNO>60</DEPTNO>
 </ROW>
</ROWSET>
  ');
begin
  -- Create new context for INSERT.
  context := dbms_xmlsave.newcontext('SCOTT.DEPT');
  -- Insert rows.
  row_count := dbms_xmlsave.insertxml(context, xml_insert.getclobval);
  -- Close the context.
  dbms_xmlsave.closecontext(context);
  dbms_output.put_line(row_count || ' row(s) inserted.');

  -- Create new context for UPDATE.
  context := dbms_xmlsave.newcontext('SCOTT.DEPT');
  -- Set primary key column for UPDATE to work.
  dbms_xmlsave.clearupdatecolumnlist(context);
  dbms_xmlsave.setkeycolumn(context, 'DEPTNO');
  -- UPDATE the document.
  row_count := dbms_xmlsave.updatexml(context, xml_update.getclobval);
  -- Close the context.
  dbms_xmlsave.closecontext(context);
  dbms_output.put_line(row_count || ' row(s) updated.');

  -- Create new context for DELETE.
  context := dbms_xmlsave.newcontext('SCOTT.DEPT');
  -- Set primary key column for DELETE to work.
  dbms_xmlsave.clearupdatecolumnlist(context);
  dbms_xmlsave.setkeycolumn(context, 'DEPTNO');
  -- DELETE the document.
  row_count := dbms_xmlsave.deletexml(context, xml_delete.getclobval);
  -- Close the context.
  dbms_xmlsave.closecontext(context);
  dbms_output.put_line(row_count || ' row(s) deleted.');
end;
/

select *
from   dept
order  by deptno;

rollback;

Running the above would produce the following output:

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

2 row(s) inserted.
1 row(s) updated.
1 row(s) deleted.

PL/SQL procedure successfully completed.

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 XOPERATIONSETC NEW YORK
        50 ACCOUNTING     ALBANY

Rollback complete.

Like DBMS_XMLQUERY, DBMS_XMLSAVE is implemented in Java and hence it’s not supported with Oracle Database Express Edition.

DBMS_XMLSTORE

DBMS_XMLSTORE was introduced with Oracle Database 10g Release 1 and should be used instead of DBMS_XMLSAVE in order to work around limitations in the latter, such as unsupported TIMESTAMP fractional seconds with precisions larger than 3 decimal places and also that the time part of DATE values is mandatory. DBMS_XMLSTORE is written in C and provides better performance than DBMS_XMLSAVE. DBMS_XMLSTORE uses NLS settings for date/time values so you must be careful to set these for XML Schema compliance as described elsewhere in this blog post.

Example: As the example for DBMS_XMLSAVE, simply replace DBMS_XMLSAVE with DBMS_XMLSTORE.

DBMS_METADATA

DBMS_METADATA can be used to obtain DDL for your database objects. This can either be returned as DDL or XML. Example:

select dbms_metadata.get_xml('TABLE', 'DEPT') xml
from   dual;

(Output abbreviated for legibility)

DBMS_METADATA.GET_XML('TABLE','DEPT')
------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TABLE_T>
   <VERS_MAJOR>1</VERS_MAJOR>
   <VERS_MINOR>3 </VERS_MINOR>
   <OBJ_NUM>73194</OBJ_NUM>
   <SCHEMA_OBJ>
    <OBJ_NUM>73194</OBJ_NUM>
    <DATAOBJ_NUM>73194</DATAOBJ_NUM>
    <OWNER_NUM>84</OWNER_NUM>
    <OWNER_NAME>SCOTT</OWNER_NAME>
    <NAME>DEPT</NAME>
    <NAMESPACE>1</NAMESPACE>
    <!-- Snip -->
   </SCHEMA_OBJ>
   <STORAGE>
    <FILE_NUM>4</FILE_NUM>
    <BLOCK_NUM>130</BLOCK_NUM>
    <TYPE_NUM>5</TYPE_NUM>
    <TS_NUM>4</TS_NUM>
    <BLOCKS>8</BLOCKS>
    <EXTENTS>1</EXTENTS>
    <INIEXTS>8</INIEXTS>
    <MINEXTS>1</MINEXTS>
    <MAXEXTS>2147483645</MAXEXTS>
    <EXTSIZE>128</EXTSIZE>
    <!-- Snip -->
   </STORAGE>
   <COL_LIST>
    <COL_LIST_ITEM>
     <OBJ_NUM>73194</OBJ_NUM>
     <COL_NUM>1</COL_NUM>
     <INTCOL_NUM>1</INTCOL_NUM>
     <SEGCOL_NUM>1</SEGCOL_NUM>
     <PROPERTY>0</PROPERTY>
     <NAME>DEPTNO</NAME>
     <TYPE_NUM>2</TYPE_NUM>
     <LENGTH>22</LENGTH>
     <PRECISION_NUM>2</PRECISION_NUM>
     <SCALE>0</SCALE>
     <NOT_NULL>1</NOT_NULL>
     <!-- Snip -->
    </COL_LIST_ITEM>
   </COL_LIST>
  </TABLE_T>
 </ROW>
</ROWSET>

XSQL

Oracle XSQL pages publishing framework is an extensible platform for publishing XML in multiple formats. It can also be used to call PL/SQL APIs, INSERT/UPDATE/DELETE data based on one (unfortunately only one) posted document. It has been available since Oracle Database 8i Release 3 and is accessible through either a Java Servlet hosted on your application server, the command line (xsql.bat batch file/xsql shell script) or your own Java program. XSQL is not available from inside the database, but you can load the oraclexsql.jar into the database with loadjava and write your own PL/SQL wrapper for it. Example of how to insert two rows into DEPT and query all the rows afterwards:
Contents of dept.xsql:

<page connection="scott" xmlns:xsql="urn:oracle-xsql">
  <xsql:insert-request table="dept"/>
  <xsql:query>
    select *
    from   dept
  </xsql:query>
</page>

Contents of dept.xml:

<ROWSET>
 <ROW>
  <DEPTNO>-1</DEPTNO>
  <DNAME>Dept A</DNAME>
  <LOC>Loc A</LOC>
 </ROW>
 <ROW>
  <DEPTNO>-2</DEPTNO>
  <DNAME>Dept B</DNAME>
  <LOC>Loc B</LOC>
 </ROW>
</ROWSET>

Contents of dept_xsql.bat (adapt this, potentially %ORACLE_HOME%\bin\xsql.bat and XSQLConfig.xml to your environment):

@setlocal
rem %ORACLE_HOME%\xdk\admin\XSQLConfig.xml must be configured with a connection called "scott".
rem Set all CP in C:\Oracle\O112\BIN\xsql.bat
set ORACLE_HOME=c:\oracle\o112
set JAVA_HOME=%ORACLE_HOME%\jdk
set CP=%ORACLE_HOME%\RDBMS\jlib\xdb.jar
xsql.bat dept.xsql posted-xml=dept.xml
@endlocal

After invoking dept_xsql.bat the result is (output wrapped and indented for improved legibility):

<page>
 <xsql-status action="xsql:insert-request" rows="2"/>
 <ROWSET>
  <ROW num="1">
   <DEPTNO>-1</DEPTNO>
   <DNAME>Dept A</DNAME>
   <LOC>Loc A</LOC>
  </ROW>
  <ROW num="2">
   <DEPTNO>-2</DEPTNO>
   <DNAME>Dept B</DNAME>
   <LOC>Loc B</LOC>
  </ROW>
  <ROW num="3">
   <DEPTNO>10</DEPTNO>
   <DNAME>ACCOUNTING</DNAME>
   <LOC>NEW YORK</LOC>
  </ROW>
  <ROW num="4">
   <DEPTNO>20</DEPTNO>
   <DNAME>RESEARCH</DNAME>
   <LOC>DALLAS</LOC>
  </ROW>
  <ROW num="5">
   <DEPTNO>30</DEPTNO>
   <DNAME>SALES</DNAME>
   <LOC>CHICAGO</LOC>
  </ROW>
  <ROW num="6">
   <DEPTNO>40</DEPTNO>
   <DNAME>OPERATIONS</DNAME>
   <LOC>BOSTON</LOC>
  </ROW>
 </ROWSET>
</page>

XML Schema Compliance

In order to be compliant with XML Schema, all DATE and TIMESTAMP values must adhere to the following Oracle formats:

• DATE: 'yyyy-mm-dd"T"hh24:mi:ss' (for time part as well, note the T between date and time part) or 'yyyy-mm-dd' without time part.
• TIMESTAMP: 'yyyy-mm-dd"T"hh24:mi:ss.ff9' or 'yyyy-mm-dd"T"hh24:mi:ss.ff'.
• TIMESTAMP WITH [LOCAL] TIME ZONE: 'yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm' or 'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm'.

Unfortunately, Oracle’s DBMS_XMLGEN and XMLTYPE (plus XMLELEMENT, XMLFOREST etc prior to Oracle Database 10g Release 2) do not adhere to the XML Schema standard and instead by default they generate the values according to the current NLS settings. Your session’s NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT must be set to one of the values mentioned above in order to work around this.

XML Character Entities

The default behavior is that characters that have a special meaning in XML are converted to their corresponding XML character entities as it would otherwise be invalid XML. Such characters are <, >, " and ', which are converted to <, >, " and ' respectively:

select dbms_xmlgen.getxml(q'[select '<>"''' test from dual]') xml
from   dual;

XML
-----------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEST>&lt;&gt;&quot;&apos;</TEST>
 </ROW>
</ROWSET>

By enclosing the text in CDATA sections you avoid this required escaping as previously mentioned.

Encoding

Encoding of an XML document can be specified in the document prolog, such as:

<?xml version="1.0" encoding="ISO-8859-1"?>
<blogs>
  <blog>
    <url>ellebaek.wordpress.com</url>
    <author>Finn Ellebæk Nielsen</author>
  </blog>
</blogs>

If you omit the encoding, UTF-8 is assumed by default by most XML parsers.

Code

I’ve written a PL/SQL package in order to demonstrate how to generate XML based on Oracle data, using DBMS_XMLGEN, XMLTYPE and a few tricks. My primary goal was to achieve the the following functionality:

• Get XML from dynamic SQL query with optional support for bind variables.
• Get XML from open REF CURSOR.
• Get XML from object type instance.
• Get XML from collection.
• Get XML from ANYDATA instance.
• Get XML from ANYDATASET instance.
• Get XML from ANYTYPE instance.

The code has been tested with the following releases and editions (all 32-bit versions under Windows XP SP3):

• Oracle Database 10g Release 1 10.1.0.5.0 Personal Edition.
• Oracle Database 10g Release 2 10.2.0.4.0 Personal Edition.
• Oracle Database 10g Release 2 10.2.0.1.0 Express Edition.
• Oracle Database 11g Release 1 11.1.0.6.0 Personal Edition.
• Oracle Database 11g Release 2 11.2.0.1.0 Personal Edition.

The first version of this package had the following issues:

1. It used packaged types for the bind variable collection. This is fine if you use PL/SQL to call the function FROM_SELECT with a bind variable collection. However, it wasn’t possible to call this function directly from a PL/SQL expression or from SQL, so I changed the implementation to use two object types instead.
2. FROM_REF_CURSOR didn’t generate date values in the XML Schema compliant format. Instead, date values were generated according to the NLS_DATE_FORMAT active when the REF CURSOR was opened, at least on some Oracle versions on some platforms and sometimes only for the first row. This was attempted fixed by calling DBMS_XMLGEN.RESTARTQUERY after having changed the NLS settings but this also proved unstable.
3. Changing the NLS settings on the fly has the following side effect: Any call to TO_CHAR/TO_DATE/TO_TIMESTAMP etc in the incoming query/REF CURSOR (including PL/SQL functions called) without a specific date/time format will change semantics as the result will adhere to the XML Schema compliant formats (for dates, truncated to the least of the lengths of the original NLS_DATE_FORMAT and the XML compliant NLS_DATE_FORMAT), instead of the original NLS setting in place when calling XML_GEN. This may seem critical, however it’s considered poor practice to call these functions without a specific format.

Anyway, in order to work around the issues with XML Schema-compliant date/time values I decided to fix this for the third updated version of the code. My idea was to implement some code that would do the following:

• Fetch the dynamic SQL/REF CURSOR into memory or a table.
• Change the NLS settings for XML Schema-compliance.
• Open a new REF CURSOR on the copy in memory/table.
• Call DBMS_XMLGEN on the new REF CURSOR.
• Change the NLS settings back to the previous session NLS.

This way we would have no side effects as the NLS would be changed after the original data had been fetched. However, this proved to be a major task to implement and in fact I’ve written two separate blog posts about it:

• Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C
• Copying/Transforming a REF CURSOR in Oracle 10g+

In fact, had I known it would have been that difficult, I would probably have chosen to write an improved version of DBMS_XMLGEN instead. Perhaps a subject for a future blog post or product?

You control the NLS handling through the optional parameter DATE_TIME_OPTION. If using this with a value of either DATE_TIME_XML_SET_NLS_COPY_MEM or DATE_TIME_XML_SET_NLS_COPY_TAB you need the version of REF_CURSOR_COPY from this blog post: Copying/Transforming a REF CURSOR in Oracle 10g+.

Here is the code:

create or replace type bind_variable_t as object (

/**
 * Bind variables: Name and value.
 * @version     $Revision: 1 $
 */

  --- Name.
  name varchar2(30),
  --- Value.
  value varchar2(32767)
);
/

create or replace type bind_variable_c as

/**
 * Unordered collection of bind variables.
 * @version     $Revision: 1 $
 */

table of bind_variable_t;
/

create or replace package xml_gen
authid current_user as
/**
 * XML utilities for converting Oracle data and objects to XML.
 * @version   $Revision: 3 $
 * @author    Finn Ellebaek Nielsen.
 */

  /**
   * Generate date/time values according to current session NLS settings.
   */
  date_time_sess_nls             constant pls_integer := 1;

  /**
   * Generate XML Schema-compliant date/time values. This is done by internally
   * by changing the NLS settings and generating the XML. This has the side
   * effect that any implit/explicit calls to TO_CHAR without a specific format
   * mask will work on the internal NLS setting. Also, for REF CURSORs, the
   * internal NLS setting will not be in effect in all cases, because Oracle
   * pre-allocated bindings to the session NLS in effect when the REF CURSOR was
   * opened.
   */
  date_time_xml_set_nls          constant pls_integer := 2;

  /**
   * Generate XML Schema-compliant date/time values. This is done by internally
   * by changing the NLS settings, restarting the query and generating the XML.
   * For dynamic SQL, this has the side effect that any implit/explicit calls to
   * TO_CHAR without a specific format mask will work on the internal NLS
   * setting. For REF CURSORs, the internal NLS setting sometimes will be in
   * effect because the REF CURSOR was restarted. However, when is very unstable
   * across Oracle versions. On some versions DATE columns will have a truncated
   * value if the session NLS_DATE_FORMAT was shorter than the XML Schema-
   * compliant one, on some it will have a format according to the session
   * NLS_DATE_FORMAT. TIMESTAMP% columns seem to work correctly. On some
   * versions Oracle will throw an error "ORA-01801: date format is too long for
   * internal buffer" if the session NLS format mask in effect when opening the
   * REF CURSOR was shorter that what is required for XML Schema-compliance.
   * This also depends on how many rows are returned by the REF CURSOR.
   * Sometimes it doesn't work correctly for the first row, but it works
   * correctly for subsequent rows.
   * In general, you should avoid using this setting as it's very unstable.
   * Setting your session's NLS_DATE_FORMAT to at least 23 characters makes it
   * slightly more stable.
   */
  date_time_xml_set_nls_restart  constant pls_integer := 3;

  /**
   * Generate XML Schema-compliant date/time values. This is done by internally
   * by fetching the incoming dynamic SQL or REF CURSOR to memory, changing the
   * NLS settings, generating the XML on the memory copy. This has no side
   * effects on implit/explicit calls to TO_CHAR without a specific format mask
   * but the performance is affected.
   * Used with the FROM_SELECT function this is only supported on Oracle 11.1
   * and newer.
   */
  date_time_xml_set_nls_copy_mem constant pls_integer := 4;

  /**
   * Generate XML Schema-compliant date/time values. This is done by internally
   * by fetching the incoming dynamic SQL or REF CURSOR to a global temporary
   * table, changing the NLS settings, generating the XML on the table copy.
   * This has no side effects on implit/explicit calls to TO_CHAR without a
   * specific format mask but the performance is affected.
   * Used with the FROM_SELECT function on Oracle versions older than 11 (or if
   * you're using quoted bind variable names -- necessary because DBMS_SQL does
   * not support quoted bind variable names) this attempts to locate the bind
   * variables in SELECT_STATEMENT and this is not done in a fool proof manner
   * if you're using quoted bind variable names using non-identifier characters.
   */
  date_time_xml_set_nls_copy_tab constant pls_integer := 5;

  function from_select(
    select_statement in varchar2,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  function from_select(
    select_statement in varchar2,
    bind_variables in bind_variable_c,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  function from_ref_cursor(
    ref_cursor in out sys_refcursor,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  function from_anydata(
    ad in anydata,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  function from_anydataset(
    ads in anydataset,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  function from_anytype(
    "at" in anytype,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

  procedure set_xml_nls_formats;
  procedure set_session_nls_formats;
end xml_gen;
/

create or replace package body xml_gen as
  --- @version  $Revision: 3 $

  /**
   * XML Schema compliant datetime format, no fraction, no time zone
   * (Oracle DATE).
   */
  xml_nls_date_format constant varchar2(23) :=
      'yyyy-mm-dd"T"hh24:mi:ss';
  /**
   * XML Schema compliant datetime format, with fraction, no time zone (Oracle
   * TIMESTAMP).
   */
  xml_nls_timestamp_format constant varchar2(27) :=
      'yyyy-mm-dd"T"hh24:mi:ss.ff9';
  /**
   * XML Schema compliant datetime format, with fraction, with time zone (Oracle
   * TIMESTAMP WITH [LOCAL] TIME ZONE).
   */
  xml_nls_timestamp_tz_format constant varchar2(34) :=
      'yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm';

  /**
   * Collection of V$NLS_PARAMETERS.VALUE, indexed by
   * V$NLS_PARAMETERS.PARAMETER.
   */
  type nls_parameters_c is table of
  v$nls_parameters.value%type
  index by v$nls_parameters.parameter%type;

  --- Session NLS formats.
  session_nls_parameters nls_parameters_c;
  --- NLS Formats required for XML compliance.
  xml_nls_parameters nls_parameters_c;

  -- Empty bind variable collection.
  bind_variables_empty bind_variable_c;

  function get_xml(
    context in dbms_xmlgen.ctxtype,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype;

/**
 * Gets the result set of a given SELECT statement as XML in Oracle's
 * "canonical form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   select_statement
 *                  SELECT statement.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  Result set as XML.
 */

  function from_select(
    select_statement in varchar2,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

  begin
    return from_select(
      select_statement, bind_variables_empty, date_time_option
    );
  end from_select;

/**
 * Gets the result set of a given SELECT statement as XML in Oracle's
 * "canonical form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   select_statement
 *                  SELECT statement with bind variables.
 * @param   bind_variables
 *                  Collection of bind variables to be bound.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  Result set as XML.
 */

  function from_select(
    select_statement in varchar2,
    bind_variables in bind_variable_c,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

    context dbms_xmlgen.ctxtype;
    dbms_sql_cursor binary_integer;
    n pls_integer;
    ref_cursor sys_refcursor;
    quoted_bind_variable_names boolean := false;

  begin
    if substr(
         upper(ltrim(select_statement, chr(13) || chr(10) || ' ')),
         1,
         6
       ) != 'SELECT' then
      -- Invalid statement given, not a SELECT. Attempting to prevent injection.
      raise_application_error(
        -20000,
        'xml_gen.from_select(select_statement => ''' ||
          select_statement || '''): ' ||
          'Only SELECT statements are allowed'
      );
    end if;
    if date_time_option in (
          date_time_xml_set_nls_copy_mem,
          date_time_xml_set_nls_copy_tab
        ) then
      if bind_variables is null or bind_variables.count = 0 then
        open ref_cursor for
        select_statement;
      else
        for i in
              nvl(bind_variables.first, 0) ..
              nvl(bind_variables.last, -1)
            loop
          if substr(bind_variables(i).name, 1, 1) = '"' then
            quoted_bind_variable_names := true;
          end if;
        end loop;

        if portable.get_major_version < 11 or quoted_bind_variable_names then
          -- Older than Oracle 11.1. Dynamically build REF CURSOR OPEN
          -- statement. This is not foolproof if quoted bind variable names are
          -- used and we might not find them correctly.
          declare
            select_statement_upper varchar2(32767) := upper(select_statement);
            first_bind_positions dbms_utility.number_array;
            sorted_bind_variables bind_variable_c := bind_variable_c();
            j pls_integer;
            open_statement varchar2(32767);
            next_char varchar2(1);
            quoted_bind_variable_name boolean;
          begin
            for i in
                  nvl(bind_variables.first, 0) ..
                  nvl(bind_variables.last, -1)
                loop
              -- Locate the bind variables in the SELECT statement.
              n := 1;
              quoted_bind_variable_name :=
                substr(bind_variables(i).name, 1, 1) = '"';
              loop
                if quoted_bind_variable_name then
                  -- Case sensitive search, bind variable name quoted..
                  j := instr(
                    select_statement,
                    ':' || bind_variables(i).name,
                    1,
                    n
                  );
                else
                  -- Case insensitive search, bind variable name not quoted.
                  j := instr(
                    select_statement_upper,
                    ':' || upper(bind_variables(i).name),
                    1,
                    n
                  );
                end if;
                -- Ensure that this is the whole word and not part of another
                -- bind variable. Check that next character cannot be part of an
                -- identifier.
                next_char := substr(
                  select_statement_upper,
                  j + length(bind_variables(i).name) + 1,
                  1
                );
                exit when next_char is null or
                    ascii(next_char) <= 32 or
                    instr('+-/*(),|''', next_char) > 0;
                n := n + 1;
              end loop;
              if j = 0 then
                -- Not found, raise error.
                raise_application_error(
                  -20001,
                  'xml_gen.from_select: Bind variable ' ||
                      bind_variables(i).name || ' not found in select_statement'
                );
              end if;
              first_bind_positions(j) := i;
            end loop;

            j := first_bind_positions.first;
            while j is not null loop
              -- Sorted list of bind positions, sort the bind variables
              -- accordingly.
              sorted_bind_variables.extend(1);
              sorted_bind_variables(sorted_bind_variables.count) :=
                  bind_variables(first_bind_positions(j));
              j := first_bind_positions.next(j);
            end loop;

            open_statement :=
                'begin' || chr(10) ||
                '  open :rc for ' || chr(10) ||
                '''' || replace(select_statement, '''', '''''') || '''' || chr(10) ||
                '  using ';

            for i in
                  nvl(sorted_bind_variables.first, 0) ..
                  nvl(sorted_bind_variables.last, -1)
                loop
              open_statement := open_statement ||
                  '''' || replace(sorted_bind_variables(i).value, '''', '''''') ||
                      ''', ';
            end loop;
            open_statement :=
                rtrim(open_statement, ', ') || ';' || chr(10) ||
                'end;';

            execute immediate open_statement
            using in out ref_cursor;
          end;
        else
          -- Oracle 11.1 or newer -- open with DBMS_SQL, then convert to REF
          -- CURSOR. This does not support quoted bind variable names as
          -- DBMS_SQL will raise a "ORA-01008: not all variables bound" even
          -- through it doesn't raise a "ORA-01006: bind variable does not
          -- exist".
          dbms_sql_cursor := dbms_sql.open_cursor;
          dbms_sql.parse(dbms_sql_cursor, select_statement, dbms_sql.native);

          for i in
                nvl(bind_variables.first, 0) ..
                nvl(bind_variables.last, -1)
              loop
            -- Bind each bind variable.
            dbms_sql.bind_variable(
              dbms_sql_cursor,
              bind_variables(i).name,
              bind_variables(i).value
            );
          end loop;

          begin
            n := dbms_sql.execute(dbms_sql_cursor);
          exception
            when others then
              dbms_output.put_line('xml_gen.from_select');
              dbms_output_put_line(
                'select_statement = ''' || select_statement || ''''
              );
              if sqlcode = -1008 then
                -- ORA-01008: not all variables bound.
                for i in
                      nvl(bind_variables.first, 0) ..
                      nvl(bind_variables.last, -1)
                    loop
                  -- Bind each bind variable.
                  dbms_output_put_line(
                    bind_variables(i).name || ' => ' ||
                        '''' || bind_variables(i).value || ''''
                  );
                  if substr(bind_variables(i).name, 1, 1) = '"' then
                    dbms_output.put_line(
                      'ERROR: DBMS_SQL doesn''t support quoted bind ' ||
                          'variable names (EXECUTE IMMEDIATE does)'
                    );
                  end if;
                end loop;
              end if;
              dbms_output.put_line(sqlerrm);
              dbms_output.put_line(dbms_utility.format_error_backtrace);
              raise;
          end;

          execute immediate
          'begin :ref_cursor := dbms_sql.to_refcursor(:dbms_sql_cursor); end;'
          using out ref_cursor, in out dbms_sql_cursor;
        end if;
      end if;

      return from_ref_cursor(ref_cursor, date_time_option);
    else
      context := dbms_xmlgen.newcontext(select_statement);

      if bind_variables is not null then
        for i in
              nvl(bind_variables.first, 0) ..
              nvl(bind_variables.last, -1)
            loop
          -- Bind each bind variable.
          dbms_xmlgen.setbindvalue(
            context,
            bind_variables(i).name,
            bind_variables(i).value
          );
        end loop;
      end if;
    end if;

    return get_xml(context, date_time_option);
  end from_select;

/**
 * Gets the result set of a given REF CURSOR as XML in Oracle's "canonical
 * form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   select_statement
 *                  SELECT statement.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  Result set as XML.
 */

  function from_ref_cursor(
    ref_cursor in out sys_refcursor,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype is

    context dbms_xmlgen.ctxtype;
    result xmltype;
    rc2 sys_refcursor;
    destination char(1);
    type_name user_types.type_name%type;

  begin
    if date_time_option in (
          date_time_xml_set_nls_copy_mem,
          date_time_xml_set_nls_copy_tab
        ) then
      -- Copy to memory/table first, then change NLS, then generate off the
      -- copy.
      if date_time_option = date_time_xml_set_nls_copy_mem then
        destination := 'M';
      else
        destination := 'T';
      end if;

      rc2 := ref_cursor_copy.to_ref_cursor(
        ref_cursor,
        type_name,
        destination,
        plsql_block_before_copy_open => 'xml_gen.set_xml_nls_formats;'
      );

      ref_cursor := rc2;
    end if;

    context := dbms_xmlgen.newcontext(ref_cursor);
    result := get_xml(context, date_time_option);
    begin
      if ref_cursor%isopen then
        close ref_cursor;
      end if;
    exception
      when others then
        null;
    end;
    return result;
  end from_ref_cursor;

/**
 * Gets the result set of a given ANYDATA instance as XML in Oracle's "canonical
 * form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * This is the function to use when you need to obtain XML for object type/
 * collection instances and ANYDATA in general. You use ANYDATA.CONVERTOBJECT
 * and ANYDATA.CONVERTCOLLECTION respectively to convert from object types and
 * collections for this. XMLTYPE can also convert these directly but has several
 * flaws in terms of incurrent DATE/TIMESTAMP formats, missing elements for NULL
 * values, etc.
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   ad      ANYDATA instance.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  ANYDATA instance as XML.
 */

  function from_anydata(
    ad in anydata,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

    ref_cursor sys_refcursor;

  begin
    open ref_cursor for
    select ad
    from   dual;

    -- Get rid of surrounding XML due to the SELECT statement.
    return from_ref_cursor(ref_cursor, date_time_option).extract('/*/*/*/*');
  end from_anydata;

/**
 * Gets the result set of a given ANYDATASET instance as XML in Oracle's
 * "canonical form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   ads     ANYDATASET instance.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  ANYDATASET instance as XML.
 */

  function from_anydataset(
    ads in anydataset,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

    ref_cursor sys_refcursor;

  begin
    open ref_cursor for
    select ads
    from   dual;

    -- Get rid of surrounding XML due to the SELECT statement.
    return from_ref_cursor(ref_cursor, date_time_option).extract('/*/*/*/*');
  end from_anydataset;

/**
 * Gets the result set of a given ANYTYPE instance as XML in Oracle's "canonical
 * form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   "at"    ANYTYPE instance.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  ANYTYPE instance as XML.
 */

  function from_anytype(
    "at" in anytype,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

    ref_cursor sys_refcursor;

  begin
    open ref_cursor for
    select "at"
    from   dual;

    -- Get rid of surrounding XML due to the SELECT statement.
    return from_ref_cursor(ref_cursor, date_time_option).extract('/*/*/*/*');
  end from_anytype;

/**
 * Gets the result set of a given DBMS_XMLGEN context as XML in Oracle's
 * "canonical form", ie:
 * <ROWSET>
 *   <ROW>
 *     <COLUMN_NAME_1>column_value_1</COLUMN_NAME_1>
 *     <COLUMN_NAME_2>column_value_2</COLUMN_NAME_2>
 *     ...
 *     <COLUMN_NAME_N>column_value_n</COLUMN_NAME_N>
 *   </ROW>
 * </ROWSET>
 * Text columns will have their values converted to XML character entities, also
 * if it's XML, HTML etc. This is not done with XMLTYPE column values as we
 * assume it's valid XML.
 * @param   context DBMS_XMLGEN context created based on either SELECT statement
 *                  or REF CURSOR.
 * @param   date_time_option
 *                  Date/time options. One of the DATE_TIME_% constants from the
 *                  package specification.
 * @return  Result set as XML.
 */

  function get_xml(
    context in dbms_xmlgen.ctxtype,
    date_time_option in pls_integer := date_time_xml_set_nls
  )
  return xmltype as

    result xmltype;

  begin
    if date_time_option in (
          date_time_xml_set_nls,
          date_time_xml_set_nls_restart
        ) then
      set_xml_nls_formats;
    end if;
    if date_time_option = date_time_xml_set_nls_restart then
      dbms_xmlgen.restartquery(context);
    end if;
    dbms_xmlgen.setnullhandling(context, dbms_xmlgen.empty_tag);
    dbms_xmlgen.setconvertspecialchars(context, true);
    result := dbms_xmlgen.getxmltype(context);
    dbms_xmlgen.closecontext(context);
    if date_time_option in (
          date_time_xml_set_nls,
          date_time_xml_set_nls_restart,
          date_time_xml_set_nls_copy_mem,
          date_time_xml_set_nls_copy_tab
        ) then
      set_session_nls_formats;
    end if;

    return result;
  exception
    when others then
      if date_time_option in (
            date_time_xml_set_nls,
            date_time_xml_set_nls_restart,
            date_time_xml_set_nls_copy_mem,
            date_time_xml_set_nls_copy_tab
          ) then
        set_session_nls_formats;
      end if;
      raise;
  end get_xml;

/**
 * Sets specific NLS date/timestamp formats required for XML compliance.
 */

  procedure set_xml_nls_formats is
  pragma autonomous_transaction;

    needs_commit boolean := false;

  begin
    for nls in (
          select /*+ cursor_sharing_exact */
                 parameter,
                 value
          from   v$nls_parameters
          where  parameter like 'NLS_%FORMAT' and
                 parameter not like 'NLS\_TIME\_%' escape '\'
        ) loop
      session_nls_parameters(nls.parameter) := nls.value;
      if nls.value != xml_nls_parameters(nls.parameter) then
        -- Change NLS format for XML-compliance.
        dbms_session.set_nls(
          nls.parameter,
          '''' || xml_nls_parameters(nls.parameter) || ''''
        );

        needs_commit := true;
      end if;
    end loop;

    if needs_commit then
      commit;
    end if;
  end set_xml_nls_formats;

/**
 * Restores NLS date/timestamp formats as it was before setting up for XML
 * compliance.
 */

  procedure set_session_nls_formats is
  pragma autonomous_transaction;

    needs_commit boolean := false;
    nls_parameter v$nls_parameters.parameter%type;

  begin
    nls_parameter := xml_nls_parameters.first;
    while nls_parameter is not null loop
      if xml_nls_parameters(nls_parameter) !=
          session_nls_parameters(nls_parameter) then
        -- Restore original NLS format.
        dbms_session.set_nls(
          nls_parameter,
          '''' || session_nls_parameters(nls_parameter) || ''''
        );

        needs_commit := true;
      end if;

      nls_parameter := xml_nls_parameters.next(nls_parameter);
    end loop;

    if needs_commit then
      commit;
    end if;
  exception
    when no_data_found then
      -- Ignore that we don't have a backup. Presumably because
      -- SET_XML_NLS_FORMATS hasn't been called.
      null;
  end set_session_nls_formats;

begin
  xml_nls_parameters('NLS_DATE_FORMAT') := xml_nls_date_format;
  xml_nls_parameters('NLS_TIMESTAMP_FORMAT') := xml_nls_timestamp_format;
  xml_nls_parameters('NLS_TIMESTAMP_TZ_FORMAT') := xml_nls_timestamp_tz_format;
end xml_gen;
/

Example Usage

Here are a few examples of usage:

set long 100000
set pagesize 50000
set linesize 1000

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff';
alter session set nls_timestamp_tz_format = 'dd.mm.yyyy hh24:mi:ss.ff tzh:tzm';

variable xml clob

prompt INSERT

begin
  :xml := '';
  :xml := xml_gen.from_select(
    'insert into dept values (50, ''abc'', ''def'')'
  ).getclobval();
end;
/

print :xml

prompt FROM_SELECT

begin
  :xml := '';
  :xml := xml_gen.from_select('select * from dept').getclobval();
end;
/

print :xml

prompt FROM_SELECT with bind variables

begin
  :xml := xml_gen.from_select(
    'select * from emp where empno in (:empno1, :empno2)',
    bind_variable_c(
      bind_variable_t('empno1', '7499'),
      bind_variable_t('empno2', '7934')
    )
  ).getclobval();
end;
/

print :xml

prompt FROM_REF_CURSOR with bind variables

declare
  rc sys_refcursor;
begin
  open rc for
  'select * from emp where empno in (:empno1, :empno2)'
  using 7369, 7499;

  :xml := xml_gen.from_ref_cursor(rc).getclobval();
end;
/

print :xml

-- grant execute on sys.aq$_subscribers to scott;
-- grant execute on sys.aq$_agent to scott;

prompt FROM_ANYDATA object type instance

begin
  :xml := '';
  :xml := xml_gen.from_anydata(
    anydata.convertobject(sys.aq$_agent('name', 'address', 1))
  ).getclobval();
end;
/

print :xml

prompt FROM_ANYDATA collection

begin
  :xml := '';
  :xml := xml_gen.from_anydata(
    anydata.convertcollection(
      sys.aq$_subscribers(
        sys.aq$_agent('name1', 'address1', 1),
        sys.aq$_agent('name2', 'address2', 2)
      )
    )
  ).getclobval();
end;
/

print :xml

prompt FROM_ANYTYPE throws ORA-19201

declare
  a anytype;
begin
  anytype.begincreate(
    typecode => dbms_types.typecode_object,
    atype => a
  );
  a.addattr(
    typecode => dbms_types.typecode_number,
    aname => 'n',
    prec => 38,
    scale => 0,
    len => null,
    csid => null,
    csfrm => null
  );
  a.addattr(
    typecode => dbms_types.typecode_date,
    aname => 'd',
    prec => 5,
    scale => 5,
    len => null,
    csid => null,
    csfrm => null
  );
  a.endcreate;

  begin
    :xml := '';
    :xml := xml_gen.from_anytype(a).getclobval();
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;

  begin
    select sys_xmlgen(a).getclobval()
    into   :xml
    from   dual;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;
end;
/

print :xml

prompt FROM_ANYDATASET throws ORA-19201

declare
  a anytype;
  ads anydataset;
begin
  anytype.begincreate(
    typecode => dbms_types.typecode_object,
    atype => a
  );
  a.addattr(
    typecode => dbms_types.typecode_number,
    aname => 'n',
    prec => 38,
    scale => 0,
    len => null,
    csid => null,
    csfrm => null
  );
  a.addattr(
    typecode => dbms_types.typecode_varchar2,
    aname => 'vc2',
    prec => null,
    scale => null,
    len => 10,
    csid => null,
    csfrm => null
  );
  a.endcreate;

  anydataset.begincreate( dbms_types.typecode_object, a, ads);
  for i in 1 .. 2 loop
    ads.addinstance;
    ads.piecewise;
    ads.setnumber(i);
    ads.setvarchar2('vc2 ' || i);
  end loop;
  ads.endcreate;

  begin
    :xml := '';
    :xml := xml_gen.from_anydataset(ads).getclobval();
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;

  begin
    select sys_xmlgen(ads).getclobval()
    into   :xml
    from   dual;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;
end;
/

print :xml

Source Code

You can download the source code here.
Feel free to use the code at your own risk. I welcome your feedback and suggestions for improvements but the code as such is not supported.

Conclusion

Did I achieve my goals? Not entirely, but close. I’ll describe this in detail in the following sections.

Object Type Instance

Object type instance: Unfortunately, Oracle doesn’t allow us to write custom PL/SQL code that can receive or return every possible object type instance. Oracle reserves the datatypes "<ADT_1>" (ADT is an acronym for Abstract Data Type, another term used for object types) and "<COLLECTION_1>" for SYS objects so we’re not allowed to use them. This is very unfortunate, as in this case we could just pass them on to other Oracle functionality that would know what to do with them.

We have a few workarounds:

• Convert the object type instance and collection to an ANYDATA instance by calling the ANYDATA.CONVERTOBJECT and ANYDATA.CONVERTCOLLECTION “constructors” respectively and then calling XML_GEN.FROM_ANYDATA.
• Add an overloaded FROM_OBJECT function in XML_GEN for each object type you need to support. This should simply convert the object type instance to an ANYDATA instance (by calling the ANYDATA.CONVERTOBJECT “constructor”) and invoke XML_GEN.FROM_ANYDATA. If your object types inherit from a few base object types this would be easily done by just adding overloads for the base object types.
• Add a member function to each of the object types called TO_XML, which basically does what the overloaded FROM_OBJECT function mentioned above would do.

ANYTYPE and ANYDATASET

ANYTYPE and ANYDATASET are not supported by DBMS_XMLGEN and any of the other XML generating functionality. Oracle throws the following error if you invoke with any of those datatypes:

ORA-19201: Datatype not supported

I considered implementing a workaround that would generate the XML by recursively inspecting the structure (and values for ANYDATASET) but I’ll leave this for a future version of my XML_GEN package.

Related Blog Posts

A few other related and interesting blog posts are (please notify me if you know of other interesting, relevant blog posts on this subject and I’ll add them to this list):

• Kevin Meade’s blog: Easy XML – Let the Database do the Work: http://www.orafaq.com/node/1025.
• Adrian Billington’s blog: working with long columns: http://www.oracle-developer.net/display.php?id=430.

References

• Oracle® XML DB Developer’s Guide 11g Release 2 (11.2) Chapter 18 Generating XML Data from the Database: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb13gen.htm#g1047191.
• Oracle XSQL: Combining SQL, Oracle Text, XSLT…: Michael D. Thomas. Wiley 2003. ISBN 0471271209: http://wiley.com/compbooks/thomas/.
• Oracle® XML Developer’s Kit Programmer’s Guide 11g Release 2 (11.2) Chapter 15 Using the XSQL Pages Publishing Framework: Advanced Topics: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10708/adx_j_xsqladv.htm#BABCIGBB.
• Oracle® XML Developer’s Kit Programmer’s Guide 11g Release 2 (11.2) Chapter 30 XSQL Pages Reference: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10708/adx_ref_xsql.htm#BJEEABJJ.