XMLType, RefCursor and renaming ROW and ROWSET tags

The project that I'm currently involved in uses XML to send data to a reporting engine which parses the XML and creates a pretty PDF report.
In order to do this some "generic" packages were created to create the XML based on the content of a RefCursor. Works like a charm. Just call the function with a RefCursor and get an XML back.
The problem they initially had was that the XML returned uses the ROWSET and ROW tags, while they wanted different tags. No problem, some REPLACE function would do the job, and that worked fine. Until last week that was...


First let me show you how the initial function works and what the XML result was they returned.

SQL> create function rc_to_xml (p_rc in sys_refcursor)
  2     return xmltype
  3  is
  4  begin
  5     return xmltype (p_rc);
  6  end rc_to_xml;
  7  /

Function created.

The function has an input parameter which is a Ref Cursor and returns an XMLType. The XMLType constructor can take a Ref Cursor and transform that into an XMLType, pretty cool functionality. In order to test this functionality, we need two local variables - one for the Ref Cursor and one for the XMLType.

SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select ename
  7          , job
  8          , sal
  9       from emp
 10      where rownum <= 2
 11     ;
 12     x := rc_to_xml (refC);
 13     dbms_output.put_line (x.extract ('/*').getClobVal());
 14  end;
 15  /

Using DBMS_OUTPUT to see the actual content of the XMLType, we will get this result:

<ROWSET>
  
    SMITH
    CLERK
    800
  
  
    ALLEN
    SALESMAN
    1600
  
</ROWSET>

A nice XMLType is the result. The only "problem" is the standard tags used by the XMLType. In this project we didn't want to have the standard ROWSET and ROW tags, we wanted to have more meaningful names.
Changing the function to change these tags on the fly would work

SQL> create or replace
  2  function rc_to_xml (p_rc     in sys_refcursor
  3                     ,p_rowset in varchar2
  4                     ,p_row    in varchar2
  5                     )
  6     return xmltype
  7  is
  8     retval xmltype;
  9     c      clob;
 10  begin
 11     retval := xmltype (p_rc);
 12     c := retval.getClobVal();
 13     c := replace (c, '', '<'||p_rowset||'>');
 14     c := replace (c, '', '</'||p_rowset||'>');
 15     c := replace (c, '', '<'||p_row||'>');
 16     c := replace (c, '', '</'||p_row||'>');
 17     retval := xmltype (c);
 18     return retval;
 19  end rc_to_xml;
 20  /

Function created.

Just to make sure it works as expected, a small testcase:

SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select ename
  7          , job
  8          , sal
  9       from emp
 10      where rownum <= 2
 11     ;
 12     x := rc_to_xml (refC
 13                    ,'DEPT'
 14                    ,'EMP'
 15                    );
 16     dbms_output.put_line (x.extract ('/*').getClobVal());
 17  end;
 18  /
<DEPT>
  
    SMITH
    CLERK
    800
  
  
    ALLEN
    SALESMAN
    1600
  
</DEPT>

And that did the trick for quite some time (years actually).

This technique was used quite heavily for reporting purposes, it even works fine with nesting one XMLType in another:

SQL> create or replace
  2  function all_emps (p_deptno in dept.deptno%type)
  3     return xmltype
  4  is
  5     rc sys_refcursor;
  6  begin
  7     open rc for select ename
  8                      , job
  9                      , sal
 10                   from emp
 11                  where deptno = p_deptno;
 12     return rc_to_xml (rc
 13                      ,'EMPS'
 14                      ,'EMP'
 15                      );
 16  end all_emps;
 17  /

Function created.

In the below code sample you can see the above function being used in the Ref Cursor on line 8.

SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select deptno
  7          , dname
  8          , all_emps (deptno) employees
  9       from dept
 10      where deptno = 10
 11     ;
 12     x := rc_to_xml (refC
 13                    ,'DEPTS'
 14                    ,'DEPT'
 15                    );
 16     dbms_output.put_line (x.extract ('/*').getClobVal());
 17  end;
 18  /
<DEPTS>
  
    10
    ACCOUNTING
    
      
        

    CLARK
          MANAGER
          2450
        
        
          KING
          PRESIDENT
          5000
        
        
          MILLER
          CLERK
          1300
        
      
    
  
</DEPTS>


PL/SQL procedure successfully completed.

Very flexible, used extensively throughout the reporting packages.

Last week problems started to occur with these packages however. XMLTypes were not being created correctly, strange characters started to appear causing the XML to be invalid. But not with all reports, not all the time. It appeared that if the reports became to large, this problem would occur.
After looking for the cause for quite some time, we had this feeling that it had to do with the datatype conversions going on between the XMLType and the Clob. This transformation is done in the REPLACE part of the function where the ROWSET and ROW tags are replaced.
First we started looking into creating a "REPLACE function for CLOB", which is according to the documentation not necessary... what a waste of time...
I couldn't help it, but I was getting the feeling that I wasn't the first one with this "problem". Surely there must be a way to rename the ROWSET and ROW tags when you create an XML based on a Ref Cursor?
Probably I was looking in the wrong direction, instead of treating the XML as a string, I should stay in the "XML Domain".
After more searching, finally I came across this procedure: setRowTag in the dbms_xmlgen package - just what the doctor ordered. All the convoluted code we used earlier was no longer necessary.

create or replace 
function rc_to_xml (p_rc          in sys_refcursor
                   ,p_rowset_name in varchar2
                   ,p_row_name    in varchar2
                   ) 
   return xmltype
is
   retval xmltype;
   ctx dbms_xmlgen.ctxHandle;
begin
   ctx := dbms_xmlgen.newContext (p_rc);
   dbms_xmlgen.setRowTag (ctx, p_row_name);
   dbms_xmlgen.setRowSetTag (ctx, p_rowset_name);
   retval := dbms_xmlgen.getXMLType (ctx);
   dbms_xmlgen.closeContext (ctx);
   return retval;
end rc_to_xml;
/

It's always good to realize that when you are trying to solve a certain problem someone must have come across your problem before and maybe even solved it. Hope my story will save you some time when you are trying to rename the ROWSET and ROW tags.

Oracle Documentation
REPLACE; it also works with CLOB
DBMS_XMLGEN - all subprograms