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;
Oracle DATABASE 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS FOR 32-bit Windows: Version - Production
NLSRTL Version - 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;                                                                                                      
  5    CURSOR xmlcur IS                                                                                                     
  6    SELECT xmlelement("Employee",XMLAttributes('' AS "xmlns:xsi",                        
  7                                  '' 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;                                                                                           
 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 
2 rc sys_refcursor;
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 ));
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