Saving (XML) data directly to disk

2

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&gt; conn test@workshop.amis.nl<br /><br />SQL&gt; -- creating emp and dept tables using the data from a different schema<br /><br />SQL&gt; create table emp as select * from up01.emp;<br /><br />Table created.<br /><br />SQL&gt; create table dept as select * from up01.dept;<br /><br />Table created.<br /><br />SQL&gt; set lines 80<br /><br />SQL&gt; 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&gt; desc dept<br /> Name                                      Null?    Type<br /> ----------------------------------------- -------- ----------------------------<br /> DEPTNO                                             NUMBER(2)<br /> DNAME                                              VARCHAR2(14)<br /> LOC                                                VARCHAR2(13)<br /><br />SQL&gt; 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&gt; -- Some simple examples regarding xmltype objects generating XML<br /><br />SQL&gt; select xmlelement(&quot;EmployeeNumber&quot;,e.empno)<br />  2  from   emp e<br />  3  ,      dept d<br />  4  where  e.DEPTNO=d.DEPTNO<br />  5  ;<br /><br />XMLELEMENT(&quot;EMPLOYEENUMBER&quot;,E.EMPNO)<br />---------------------------------------------------------------------------<br />&lt;EmployeeNumber&gt;7369&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7499&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7521&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7566&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7654&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7698&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7782&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7788&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7839&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7844&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7876&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7900&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7902&lt;/EmployeeNumber&gt;<br />&lt;EmployeeNumber&gt;7934&lt;/EmployeeNumber&gt;<br /><br />14 rows selected.<br /><br /><br />SQL&gt; set lines 130<br /><br />SQL&gt; select xmlelement(&quot;Employee&quot;,xmlelement(&quot;EmployeeNumber&quot;,e.empno)<br />  2                              ,xmlelement(&quot;EmployeeName&quot;,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(&quot;EMPLOYEE&quot;,XMLELEMENT(&quot;EMPLOYEENUMBER&quot;,E.EMPNO),XMLELEMENT(&quot;EMPLOYEENAME&quot;,E.ENAME))<br />--------------------------------------------------------------------------------------------------<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7369&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SMITH&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7499&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ALLEN&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7521&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;WARD&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7566&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JONES&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7654&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MARTIN&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7698&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;BLAKE&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7782&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;CLARK&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7788&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SCOTT&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7839&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;KING&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7844&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;TURNER&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7876&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ADAMS&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7900&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JAMES&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7902&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;FORD&lt;/EmployeeName&gt;&lt;/Employee&gt;<br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7934&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MILLER&lt;/EmployeeName&gt;&lt;/Employee&gt;<br /><br />14 rows selected.<br /><br />SQL&gt; select xmlelement(&quot;Employee&quot;,xmlelement(&quot;EmployeeNumber&quot;,e.empno)<br />  2                              ,xmlelement(&quot;EmployeeName&quot;,e.ename)<br />  3                              ,xmlelement(&quot;Department&quot;,xmlelement(&quot;DepartmentName&quot;,d.dname)<br />  4                                                      ,xmlelement(&quot;Location&quot;,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 />&lt;Employee&gt;&lt;EmployeeNumber&gt;7369&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SMITH&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName<br />&gt;&lt;Location&gt;DALLAS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7499&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ALLEN&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName<br />&gt;&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7521&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;WARD&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;<br />&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7566&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JONES&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentN<br />ame&gt;&lt;Location&gt;DALLAS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7654&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MARTIN&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentNam<br />e&gt;&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7698&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;BLAKE&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName<br />&gt;&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7782&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;CLARK&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/Departmen<br />tName&gt;&lt;Location&gt;NEW YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7788&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SCOTT&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentN<br />ame&gt;&lt;Location&gt;DALLAS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7839&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;KING&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/Department<br />Name&gt;&lt;Location&gt;NEW YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7844&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;TURNER&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentNam<br />e&gt;&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7876&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ADAMS&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentN<br />ame&gt;&lt;Location&gt;DALLAS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7900&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JAMES&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName<br />&gt;&lt;Location&gt;CHICAGO&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7902&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;FORD&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentNa<br />me&gt;&lt;Location&gt;DALLAS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee&gt;&lt;EmployeeNumber&gt;7934&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MILLER&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/Departme<br />ntName&gt;&lt;Location&gt;NEW YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br /><br />SQL&gt; select xmlelement(&quot;Employee&quot;,XMLAttributes('http://www.w3.org/2001/XMLSchema' AS &quot;xmlns:xsi&quot;,                <br />  2                                  'http://www.oracle.com/Employee.xsd' AS &quot;xsi:nonamespaceSchemaLocation&quot;)     <br />  3                              ,xmlelement(&quot;EmployeeNumber&quot;,e.empno)                                            <br />  4                              ,xmlelement(&quot;EmployeeName&quot;,e.ename)                                              <br />  5                              ,xmlelement(&quot;Department&quot;,xmlelement(&quot;DepartmentName&quot;,d.dname)                    <br />  6                                                      ,xmlelement(&quot;Location&quot;,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 />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7369&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SMITH&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName&gt;&lt;Location&gt;DALL<br />AS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7499&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ALLEN&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAGO<br />&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7521&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;WARD&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAGO&lt;<br />/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7566&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JONES&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName&gt;&lt;Location&gt;DALL<br />AS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7654&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MARTIN&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAG<br />O&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7698&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;BLAKE&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAGO<br />&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Emplo

ye<br />eNumber&gt;7782&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;CLARK&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/DepartmentName&gt;&lt;Location&gt;NE<br />W YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7788&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;SCOTT&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName&gt;&lt;Location&gt;DALL<br />AS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7839&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;KING&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/DepartmentName&gt;&lt;Location&gt;NEW<br /> YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7844&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;TURNER&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAG<br />O&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7876&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;ADAMS&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName&gt;&lt;Location&gt;DALL<br />AS&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7900&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;JAMES&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;SALES&lt;/DepartmentName&gt;&lt;Location&gt;CHICAGO<br />&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7902&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;FORD&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;RESEARCH&lt;/DepartmentName&gt;&lt;Location&gt;DALLA<br />S&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br />&lt;Employee xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema&quot; xsi:nonamespaceSchemaLocation=&quot;http://www.oracle.com/Employee.xsd&quot;&gt;&lt;Employe<br />eNumber&gt;7934&lt;/EmployeeNumber&gt;&lt;EmployeeName&gt;MILLER&lt;/EmployeeName&gt;&lt;Department&gt;&lt;DepartmentName&gt;ACCOUNTING&lt;/DepartmentName&gt;&lt;Location&gt;N<br />EW YORK&lt;/Location&gt;&lt;/Department&gt;&lt;/Employee&gt;<br /><br /><br />14 rows selected.<br /><br /><br />SQL&gt; create or replace directory utldata as 'C:\temp';<br /><br />Directory created.<br /><br />SQL&gt; declare                                                                                                                <br />  2    doc  DBMS_XMLDOM.DOMDocument;                                                                                        <br />  3    xdata  XMLTYPE;                                                                                                      <br />  4                                                                                                                         <br />  5    CURSOR xmlcur IS                                                                                                     <br />  6    select xmlelement(&quot;Employee&quot;,XMLAttributes('http://www.w3.org/2001/XMLSchema' AS &quot;xmlns:xsi&quot;,                        <br />  7                                  'http://www.oracle.com/Employee.xsd' AS &quot;xsi:nonamespaceSchemaLocation&quot;)               <br />  8                              ,xmlelement(&quot;EmployeeNumber&quot;,e.empno)                                                      <br />  9                              ,xmlelement(&quot;EmployeeName&quot;,e.ename)                                                        <br /> 10                              ,xmlelement(&quot;Department&quot;,xmlelement(&quot;DepartmentName&quot;,d.dname)                              <br /> 11                                                      ,xmlelement(&quot;Location&quot;,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&gt; set timing on <br /><p>SQL&gt; declare<br />  2    rc sys_refcursor;<br />  3  begin<br />  4    open rc for select * from ( select rownum from dual connect by level &lt; 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&gt; 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 &lt; 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" />&nbsp;

If opened via internet explorer both files marco.xml and anton.xml look the same via the presented pretty print look in the browser.

Smiley 

Marco

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

2 Comments

  1. 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).

  2. Jurgen Kemmelings on

    I don’t think DBMS_XSLPROCESSOR.CLOB2FILE is (standard) available in Oracle 9.2? At least not in ‘my’ database: Oracle9i Enterprise Edition Release 9.2.0.7.0

    We convert the clob to a blob and then use utl_file to write to file.