HOWTO: Saving XML data directly to disk

This is a comprised article posted on the AMIS technology blog, but with a little twist. It is posted here not only for reference but also with some additional info. If you need more info, then have a look at the example here called: “Saving (XML) data directly to disk”. In short: how can you save XML data directly to local disks, making use of XMLDB / database functionality as shown in the following example (with a DBA look at things).

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
 
5 rows selected.

SQL> create or replace directory utldata as 'C:\temp';

Directory created.

SQL> declare                                                                                                                
  2    doc  DBMS_XMLDOM.DOMDocument;                                                                                        
  3    xdata  XMLTYPE;                                                                                                      
  4                                                                                                                         
  5    CURSOR xmlcur IS                                                                                                     
  6    select xmlelement("Employee",XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",                        
  7                                  'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation")               
  8                              ,xmlelement("EmployeeNumber",e.empno)                                                      
  9                              ,xmlelement("EmployeeName",e.ename)                                                        
 10                              ,xmlelement("Department",xmlelement("DepartmentName",d.dname)                              
 11                                                      ,xmlelement("Location",d.loc)                                      
 12                                         )                                                                               
 13                   )                                                                                                     
 14     from   emp e                                                                                                        
 15     ,      dept d                                                                                                       
 16     where  e.DEPTNO=d.DEPTNO;                                                                                           
 17                                                                                                                         
 18  begin                                                                                                                  
 19    OPEN xmlcur;                                                                                                         
 20    FETCH xmlcur INTO xdata;                                                                                             
 21    CLOSE xmlcur;                                                                                                        
 22    doc := DBMS_XMLDOM.NewDOMDocument(xdata);                                                                            
 23    DBMS_XMLDOM.WRITETOFILE(doc, 'UTLDATA/marco.xml');                                                                   
 24  end;                                                                                                                   
 25  /                                                                                                                       

PL/SQL procedure successfully completed.
.

The content of the XML file will be like the following.

Click on the picture to enlarge

An alternative way compared to DBMS_XMLDOM can be used, using DBMS_XSLPROCESSOR, in the followiing way.

SQL> set timing on 
 
SQL> declare
2 rc sys_refcursor;
3 begin
4 open rc for select * from ( select rownum from dual connect by level < 500000 );
5 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , ‘UTLDATA’,'anton.xml’);
6 end;
7 / 
 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:59.61
 
SQL> declare
2 rc sys_refcursor;
3 doc DBMS_XMLDOM.DOMDocument;
4 begin
5 open rc for select * from ( select rownum from dual connect by level < 500000 );
6 doc := DBMS_XMLDOM.NewDOMDocument(xmltype( rc ));
7 DBMS_XMLDOM.WRITETOFILE(doc, ‘UTLDATA/marco.xml’);
8 end;
9 / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:01:41.94 
.

The content of the XML files seen via notepad will be like the following.

Click on the picture to enlarge

What is troublesome is not that DBMS_XMLDOM takes almost twice the time for the same method and produces a bigger size xml document (anton.xml: 19.912 Kb, marco.xml: 21.865 Kb); what is troublesome is that both consume 100% CPU time.

Click on the picture to enlarge

I will get back to this, after I figured it out, but this will take more time. A suggestion was made to serialize the XMLtype via getClobVal(). Only one thing can really help here and thats to trace it, so that will be my next step. If I get any wiser than, I will update this post.

Related posts

• HOWTO: Selecting XML data directly from your local disk
• AMIS: Saving (XML) data directly to disk (more extensive, simple example)
• HOWTO: Saving and Storing XML Data – A Tuning Adventure
• Preferisco: Writing XML to a file