Saving (XML) data directly to disk
I was asked, also after the former post "Selecting (XML) data directly from disk", what the best way would be to save data directly (locally) to disk via XMLDB methods. I proposed a simple solution based on DBMS_XMLDOM.WRITETOFILE, I will demonstrate this below. My colleague Anton came also today up with an "older" method, at least it was a more appropriate one in Oracle 9.2, but after testing it is looks (I didn’t trace the method, yet) like it is actually faster then my proposed one with DBMS_XMLDOM.WRITETOFILE, in Oracle 11gR1.
SQL> conn test@workshop.amis.nl<br /><br />SQL> -- creating emp and dept tables using the data from a different schema<br /><br />SQL> create table emp as select * from up01.emp;<br /><br />Table created.<br /><br />SQL> create table dept as select * from up01.dept;<br /><br />Table created.<br /><br />SQL> set lines 80<br /><br />SQL> desc emp<br /> Name Null? Type<br /> ----------------------------------------- -------- ----------------------------<br /> EMPNO NUMBER(4)<br /> ENAME VARCHAR2(10)<br /> JOB VARCHAR2(9)<br /> MGR NUMBER(4)<br /> HIREDATE DATE<br /> SAL NUMBER(7,2)<br /> COMM NUMBER(7,2)<br /> DEPTNO NUMBER(2)<br /><br />SQL> desc dept<br /> Name Null? Type<br /> ----------------------------------------- -------- ----------------------------<br /> DEPTNO NUMBER(2)<br /> DNAME VARCHAR2(14)<br /> LOC VARCHAR2(13)<br /><br />SQL> select e.empno<br /> 2 , e.ENAME<br /> 3 , e.JOB<br /> 4 , e.SAL<br /> 5 , d.DNAME<br /> 6 , d.LOC<br /> 7 from emp e<br /> 8 , dept d<br /> 9 where e.DEPTNO=d.DEPTNO<br /> 10 ;<br /><br /> EMPNO ENAME JOB SAL DNAME LOC<br />--------- ---------- --------- --------- -------------- -------------<br /> 7369 SMITH CLERK 800 RESEARCH DALLAS<br /> 7499 ALLEN SALESMAN 1600 SALES CHICAGO<br /> 7521 WARD SALESMAN 1250 SALES CHICAGO<br /> 7566 JONES MANAGER 2975 RESEARCH DALLAS<br /> 7654 MARTIN SALESMAN 1250 SALES CHICAGO<br /> 7698 BLAKE MANAGER 2850 SALES CHICAGO<br /> 7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK<br /> 7788 SCOTT ANALYST 3000 RESEARCH DALLAS<br /> 7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK<br /> 7844 TURNER SALESMAN 1500 SALES CHICAGO<br /> 7876 ADAMS CLERK 1100 RESEARCH DALLAS<br /> 7900 JAMES CLERK 950 SALES CHICAGO<br /> 7902 FORD ANALYST 3000 RESEARCH DALLAS<br /> 7934 MILLER CLERK 1300 ACCOUNTING NEW YORK<br /><br />14 rows selected.<br /><br />SQL> -- Some simple examples regarding xmltype objects generating XML<br /><br />SQL> select xmlelement("EmployeeNumber",e.empno)<br /> 2 from emp e<br /> 3 , dept d<br /> 4 where e.DEPTNO=d.DEPTNO<br /> 5 ;<br /><br />XMLELEMENT("EMPLOYEENUMBER",E.EMPNO)<br />---------------------------------------------------------------------------<br /><EmployeeNumber>7369</EmployeeNumber><br /><EmployeeNumber>7499</EmployeeNumber><br /><EmployeeNumber>7521</EmployeeNumber><br /><EmployeeNumber>7566</EmployeeNumber><br /><EmployeeNumber>7654</EmployeeNumber><br /><EmployeeNumber>7698</EmployeeNumber><br /><EmployeeNumber>7782</EmployeeNumber><br /><EmployeeNumber>7788</EmployeeNumber><br /><EmployeeNumber>7839</EmployeeNumber><br /><EmployeeNumber>7844</EmployeeNumber><br /><EmployeeNumber>7876</EmployeeNumber><br /><EmployeeNumber>7900</EmployeeNumber><br /><EmployeeNumber>7902</EmployeeNumber><br /><EmployeeNumber>7934</EmployeeNumber><br /><br />14 rows selected.<br /><br /><br />SQL> set lines 130<br /><br />SQL> select xmlelement("Employee",xmlelement("EmployeeNumber",e.empno)<br /> 2 ,xmlelement("EmployeeName",e.ename)<br /> 3 )<br /> 4 from emp e<br /> 5 , dept d<br /> 6 where e.DEPTNO=d.DEPTNO<br /> 7 ;<br /><br />XMLELEMENT("EMPLOYEE",XMLELEMENT("EMPLOYEENUMBER",E.EMPNO),XMLELEMENT("EMPLOYEENAME",E.ENAME))<br />--------------------------------------------------------------------------------------------------<br /><Employee><EmployeeNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName></Employee><br /><Employee><EmployeeNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName></Employee><br /><Employee><EmployeeNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName></Employee><br /><Employee><EmployeeNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName></Employee><br /><Employee><EmployeeNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName></Employee><br /><Employee><EmployeeNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName></Employee><br /><Employee><EmployeeNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName></Employee><br /><Employee><EmployeeNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName></Employee><br /><Employee><EmployeeNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName></Employee><br /><Employee><EmployeeNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName></Employee><br /><Employee><EmployeeNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName></Employee><br /><Employee><EmployeeNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName></Employee><br /><Employee><EmployeeNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName></Employee><br /><Employee><EmployeeNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName></Employee><br /><br />14 rows selected.<br /><br />SQL> select xmlelement("Employee",xmlelement("EmployeeNumber",e.empno)<br /> 2 ,xmlelement("EmployeeName",e.ename)<br /> 3 ,xmlelement("Department",xmlelement("DepartmentName",d.dname)<br /> 4 ,xmlelement("Location",d.loc)<br /> 5 )<br /> 6 ) as XMLDATA<br /> 7 from emp e<br /> 8 , dept d<br /> 9 where e.DEPTNO=d.DEPTNO<br /> 10 ;<br /><br />XMLDATA<br />---------------------------------------------------------------------------------------
----------------------------
------------------<br /><Employee><EmployeeNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName<br />><Location>DALLAS</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName><Department><DepartmentName>SALES</DepartmentName<br />><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName><Department><DepartmentName>SALES</DepartmentName><br /><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN<br />ame><Location>DALLAS</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName><Department><DepartmentName>SALES</DepartmentNam<br />e><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName><Department><DepartmentName>SALES</DepartmentName<br />><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName><Department><DepartmentName>ACCOUNTING</Departmen<br />tName><Location>NEW YORK</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN<br />ame><Location>DALLAS</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName><Department><DepartmentName>ACCOUNTING</Department<br />Name><Location>NEW YORK</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName><Department><DepartmentName>SALES</DepartmentNam<br />e><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN<br />ame><Location>DALLAS</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName><Department><DepartmentName>SALES</DepartmentName<br />><Location>CHICAGO</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentNa<br />me><Location>DALLAS</Location></Department></Employee><br /><br /><Employee><EmployeeNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName><Department><DepartmentName>ACCOUNTING</Departme<br />ntName><Location>NEW YORK</Location></Department></Employee><br /><br /><br />SQL> select xmlelement("Employee",XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi", <br /> 2 'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation") <br /> 3 ,xmlelement("EmployeeNumber",e.empno) <br /> 4 ,xmlelement("EmployeeName",e.ename) <br /> 5 ,xmlelement("Department",xmlelement("DepartmentName",d.dname) <br /> 6 ,xmlelement("Location",d.loc) <br /> 7 ) <br /> 8 ) as XMLDATA <br /> 9 from emp e <br /> 10 , dept d <br /> 11 where e.DEPTNO=d.DEPTNO <br /> 12 ; <br /><br /><br />XMLDATA<br />----------------------------------------------------------------------------------------------------------------------------------<br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL<br />AS</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO<br /></Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO<<br />/Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL<br />AS</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAG<br />O</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO<br /></Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Emplo
ye<br />eNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>NE<br />W YORK</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL<br />AS</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>NEW<br /> YORK</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAG<br />O</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL<br />AS</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO<br /></Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALLA<br />S</Location></Department></Employee><br /><br /><Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe<br />eNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>N<br />EW YORK</Location></Department></Employee><br /><br /><br />14 rows selected.<br /><br /><br />SQL> create or replace directory utldata as 'C:\temp';<br /><br />Directory created.<br /><br />SQL> declare <br /> 2 doc DBMS_XMLDOM.DOMDocument; <br /> 3 xdata XMLTYPE; <br /> 4 <br /> 5 CURSOR xmlcur IS <br /> 6 select xmlelement("Employee",XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi", <br /> 7 'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation") <br /> 8 ,xmlelement("EmployeeNumber",e.empno) <br /> 9 ,xmlelement("EmployeeName",e.ename) <br /> 10 ,xmlelement("Department",xmlelement("DepartmentName",d.dname) <br /> 11 ,xmlelement("Location",d.loc) <br /> 12 ) <br /> 13 ) <br /> 14 from emp e <br /> 15 , dept d <br /> 16 where e.DEPTNO=d.DEPTNO; <br /> 17 <br /> 18 begin <br /> 19 OPEN xmlcur; <br /> 20 FETCH xmlcur INTO xdata; <br /> 21 CLOSE xmlcur; <br /> 22 doc := DBMS_XMLDOM.NewDOMDocument(xdata); <br /> 23 DBMS_XMLDOM.WRITETOFILE(doc, 'UTLDATA/marco.xml'); <br /> 24 end; <br /> 25 / <br /><br />PL/SQL procedure successfully completed.<br /><br />
The outcome of this small example is an XML file in C:\temp called marco.xml with the following content.
Anton’s method is based on DBMS_XSLPROCESSOR.CLOB2FILE. Comparing the two methods, at least on conclusion can be made and that DBMS_XSLPROCESSOR, in 11g, generates less whitespace, which result in the difference in file size between the two.
SQL> set timing on <br /><p>SQL> declare<br /> 2 rc sys_refcursor;<br /> 3 begin<br /> 4 open rc for select * from ( select rownum from dual connect by level < 500000 );<br /> 5 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , 'UTLDATA','anton.xml');<br /> 6 end;<br /> 7 /</p><p><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:59.61</p><p>SQL> declare<br /> 2 rc sys_refcursor;<br /> 3 doc DBMS_XMLDOM.DOMDocument; <br /> 4 begin<br /> 5 open rc for select * from ( select rownum from dual connect by level < 500000 );<br /> 6 doc := DBMS_XMLDOM.NewDOMDocument(xmltype( rc ));<br /> 7 DBMS_XMLDOM.WRITETOFILE(doc, 'UTLDATA/marco.xml'); <br /> 8 end;<br /> 9 /</p><p>PL/SQL procedure successfull y c ompleted.<br /><br />Elapsed: 00:01:41.94 </p>
So the second method, based on DBMS_XSLPROCESSOR.CLOB2FILE looks faster, plus it produces files smaller in size then the DBMS_XMLDOM method. Have a look at the sizes and the layout format below.
<img src="http://technology.amis.nl/wp-content/uploads/images/antonxml-m.png" />
<img vspace="0" hspace="0" border="0" align="bottom" src="http://technology.amis.nl/wp-content/uploads/images/format_differencem.PNG" />
If opened via internet explorer both files marco.xml and anton.xml look the same via the presented pretty print look in the browser.
Marco
-
Comments are closed
-
#2 written by Jurgen Kemmelings 5 years ago
- Comment Feed for this Post
According to the manuals you are correct. According to the real world (and I double checked on a internal 9.2.0.7 EE database) there is a dbms_xslprocessor.clob2file procedure. AFAIK it is normally not part of a standard install. Depending on database versions, it is part of or the XDK or XMLDB (at current state this is part of standard XMLDB functionality).