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 

SQL> -- creating emp and dept tables using the data from a different schema

SQL> create table emp as select * from up01.emp;

Table created.

SQL> create table dept as select * from up01.dept;

Table created.

SQL> set lines 80

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> select e.empno
  2  ,      e.ENAME
  3  ,      e.JOB
  4  ,      e.SAL
  5  ,      d.DNAME
  6  ,      d.LOC
  7  from   emp e
  8  ,      dept d
  9  where  e.DEPTNO=d.DEPTNO
 10  ;

    EMPNO ENAME      JOB             SAL DNAME          LOC
--------- ---------- --------- --------- -------------- -------------
     7369 SMITH      CLERK           800 RESEARCH       DALLAS
     7499 ALLEN      SALESMAN       1600 SALES          CHICAGO
     7521 WARD       SALESMAN       1250 SALES          CHICAGO
     7566 JONES      MANAGER        2975 RESEARCH       DALLAS
     7654 MARTIN     SALESMAN       1250 SALES          CHICAGO
     7698 BLAKE      MANAGER        2850 SALES          CHICAGO
     7782 CLARK      MANAGER        2450 ACCOUNTING     NEW YORK
     7788 SCOTT      ANALYST        3000 RESEARCH       DALLAS
     7839 KING       PRESIDENT      5000 ACCOUNTING     NEW YORK
     7844 TURNER     SALESMAN       1500 SALES          CHICAGO
     7876 ADAMS      CLERK          1100 RESEARCH       DALLAS
     7900 JAMES      CLERK           950 SALES          CHICAGO
     7902 FORD       ANALYST        3000 RESEARCH       DALLAS
     7934 MILLER     CLERK          1300 ACCOUNTING     NEW YORK

14 rows selected.

SQL> -- Some simple examples regarding xmltype objects generating XML

SQL> select xmlelement("EmployeeNumber",e.empno)
  2  from   emp e
  3  ,      dept d
  4  where  e.DEPTNO=d.DEPTNO
  5  ;

XMLELEMENT("EMPLOYEENUMBER",E.EMPNO)
---------------------------------------------------------------------------
<EmployeeNumber>7369</EmployeeNumber>
<EmployeeNumber>7499</EmployeeNumber>
<EmployeeNumber>7521</EmployeeNumber>
<EmployeeNumber>7566</EmployeeNumber>
<EmployeeNumber>7654</EmployeeNumber>
<EmployeeNumber>7698</EmployeeNumber>
<EmployeeNumber>7782</EmployeeNumber>
<EmployeeNumber>7788</EmployeeNumber>
<EmployeeNumber>7839</EmployeeNumber>
<EmployeeNumber>7844</EmployeeNumber>
<EmployeeNumber>7876</EmployeeNumber>
<EmployeeNumber>7900</EmployeeNumber>
<EmployeeNumber>7902</EmployeeNumber>
<EmployeeNumber>7934</EmployeeNumber>

14 rows selected.


SQL> set lines 130

SQL> select xmlelement("Employee",xmlelement("EmployeeNumber",e.empno)
  2                              ,xmlelement("EmployeeName",e.ename)
  3                   )
  4  from   emp e
  5  ,      dept d
  6  where  e.DEPTNO=d.DEPTNO
  7  ;

XMLELEMENT("EMPLOYEE",XMLELEMENT("EMPLOYEENUMBER",E.EMPNO),XMLELEMENT("EMPLOYEENAME",E.ENAME))
--------------------------------------------------------------------------------------------------
<Employee><EmployeeNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName></Employee>
<Employee><EmployeeNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName></Employee>
<Employee><EmployeeNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName></Employee>
<Employee><EmployeeNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName></Employee>
<Employee><EmployeeNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName></Employee>
<Employee><EmployeeNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName></Employee>
<Employee><EmployeeNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName></Employee>
<Employee><EmployeeNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName></Employee>
<Employee><EmployeeNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName></Employee>
<Employee><EmployeeNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName></Employee>
<Employee><EmployeeNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName></Employee>
<Employee><EmployeeNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName></Employee>
<Employee><EmployeeNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName></Employee>
<Employee><EmployeeNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName></Employee>

14 rows selected.

SQL> select xmlelement("Employee",xmlelement("EmployeeNumber",e.empno)
  2                              ,xmlelement("EmployeeName",e.ename)
  3                              ,xmlelement("Department",xmlelement("DepartmentName",d.dname)
  4                                                      ,xmlelement("Location",d.loc)
  5                                         )
  6                   ) as XMLDATA
  7  from   emp e
  8  ,      dept d
  9  where  e.DEPTNO=d.DEPTNO
 10  ;

XMLDATA
---------------------------------------------------------------------------------------
----------------------------
------------------
<Employee><EmployeeNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName
><Location>DALLAS</Location></Department></Employee>

<Employee><EmployeeNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName><Department><DepartmentName>SALES</DepartmentName
><Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName><Department><DepartmentName>SALES</DepartmentName>
<Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN
ame><Location>DALLAS</Location></Department></Employee>

<Employee><EmployeeNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName><Department><DepartmentName>SALES</DepartmentNam
e><Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName><Department><DepartmentName>SALES</DepartmentName
><Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName><Department><DepartmentName>ACCOUNTING</Departmen
tName><Location>NEW YORK</Location></Department></Employee>

<Employee><EmployeeNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN
ame><Location>DALLAS</Location></Department></Employee>

<Employee><EmployeeNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName><Department><DepartmentName>ACCOUNTING</Department
Name><Location>NEW YORK</Location></Department></Employee>

<Employee><EmployeeNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName><Department><DepartmentName>SALES</DepartmentNam
e><Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentN
ame><Location>DALLAS</Location></Department></Employee>

<Employee><EmployeeNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName><Department><DepartmentName>SALES</DepartmentName
><Location>CHICAGO</Location></Department></Employee>

<Employee><EmployeeNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentNa
me><Location>DALLAS</Location></Department></Employee>

<Employee><EmployeeNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName><Department><DepartmentName>ACCOUNTING</Departme
ntName><Location>NEW YORK</Location></Department></Employee>


SQL> select xmlelement("Employee",XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",                
  2                                  'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation")     
  3                              ,xmlelement("EmployeeNumber",e.empno)                                            
  4                              ,xmlelement("EmployeeName",e.ename)                                              
  5                              ,xmlelement("Department",xmlelement("DepartmentName",d.dname)                    
  6                                                      ,xmlelement("Location",d.loc)                            
  7                                         )                                                                     
  8                   ) as XMLDATA                                                                                
  9  from   emp e                                                                                                 
 10  ,      dept d                                                                                                
 11  where  e.DEPTNO=d.DEPTNO                                                                                     
 12  ;                                                                                                            


XMLDATA 
----------------------------------------------------------------------------------------------------------------------------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7369</EmployeeNumber><EmployeeName>SMITH</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL
AS</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7499</EmployeeNumber><EmployeeName>ALLEN</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO
</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7521</EmployeeNumber><EmployeeName>WARD</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO<
/Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7566</EmployeeNumber><EmployeeName>JONES</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL
AS</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7654</EmployeeNumber><EmployeeName>MARTIN</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAG
O</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7698</EmployeeNumber><EmployeeName>BLAKE</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO
</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Emplo

ye
eNumber>7782</EmployeeNumber><EmployeeName>CLARK</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>NE
W YORK</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7788</EmployeeNumber><EmployeeName>SCOTT</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL
AS</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7839</EmployeeNumber><EmployeeName>KING</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>NEW
 YORK</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7844</EmployeeNumber><EmployeeName>TURNER</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAG
O</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7876</EmployeeNumber><EmployeeName>ADAMS</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALL
AS</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7900</EmployeeNumber><EmployeeName>JAMES</EmployeeName><Department><DepartmentName>SALES</DepartmentName><Location>CHICAGO
</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7902</EmployeeNumber><EmployeeName>FORD</EmployeeName><Department><DepartmentName>RESEARCH</DepartmentName><Location>DALLA
S</Location></Department></Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"><Employe
eNumber>7934</EmployeeNumber><EmployeeName>MILLER</EmployeeName><Department><DepartmentName>ACCOUNTING</DepartmentName><Location>N
EW YORK</Location></Department></Employee>


14 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 outcome of this small example is an XML file in C:\temp called marco.xml with the following content.

Saving (XML) data directly to disk marcoxml m

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

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 successfull y c ompleted.

Elapsed: 00:01:41.94

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.


Saving (XML) data directly to disk antonxml m
Saving (XML) data directly to disk format differencem

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

 

 

2 Comments

  1. Marco Gralike February 15, 2008
  2. Jurgen Kemmelings February 15, 2008