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

Marco Gralike Written by:

4 Comments

  1. February 15

    Isn’t this just because of the extra overhead of an implicit type conversion to DOMDOCUMENT, plus the extra cost of serialising the DOM tree? See the Preferisco blog.

    Regards Nigel

  2. February 15

    I agree fully with your explanation on your site, but I also wanted to find a way to circumvent this, so I could avoid the overhead in CPU and the “strange” (unwanted whitespace) after effects, as shown in the following post.

    PLUS

    I just wanted to see what the exact internals were.

    See post : HOWTO: Saving and Storing XML Data – A Tuning Adventure (http://www.liberidu.com/blog/?p=369)

    As Mark was saying here: ” AFAIK they go through the code path, once calls the other internally,…” : http://forums.oracle.com/forums/thread.jspa?threadID=618213&tstart=0

    It was also an attempt to see what was doing what…

  3. aDRIAN
    November 16

    Hi! I applied your code in a procedure, but the xml file generated is empty.. I dont know why…

    My code is the following:

    create or replace
    PROCEDURE exportacion_xml2
    IS
    doc DBMS_XMLDOM.DOMDocument;
    xdata XMLTYPE;

    CURSOR xmlcur IS
    SELECT xmlelement(“Root”
    ,xmlelement(“element1”, e.col1)
    ,xmlelement(“element2”, e.col2)
    )
    FROM tmpt e;

    BEGIN
    OPEN xmlcur;
    FETCH xmlcur INTO xdata;
    CLOSE xmlcur;
    doc := DBMS_XMLDOM.NewDOMDocument(xdata);
    DBMS_XMLDOM.WRITETOFILE(doc, ‘EXAMPLE_DIR/marco.xml’);
    END;

    PS: My oracle virtual directory is “EXAMPLE_DIR”.

  4. November 17

    Is the table / columns empty? If the statement is incorrect or there is no data to be returned, the Oracle database will not return XML elements/attributes.

Comments are closed.