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
