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.

Content of the saved marco.xml file.
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.

The difference between “anton.xml” (dbms_xslprocessor) and “marco.xml” (dbms_xmldom)
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.

100% CPU consumption on Windows 2003 Server
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