Saving (XML) data directly to disk

Marco Gralike February 14, 2008 2
0 0
Read Time:13 Minute, 12 Second

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.

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.

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
 
 
 
About Post Author
  Marco Gralike 
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).
  info@amis.nl 
  http://blog.gralike.com 
  Happy 
 Happy
  0 %
  Sad 
 Sad
  0 %
  Excited 
 Excited
  0 %
  Sleepy 
 Sleepy
  0 %
  Angry 
Angry
  0 %
  Surprise 
Surprise
  0 %
Related posts:
  Default ThumbnailAPEX installation via the Oracle Protocol Server (listener)  Default ThumbnailVerslag van JSpring 2006  Default ThumbnailJavapolis 2006 – Building Sexy Web apps with Flex  Default ThumbnailUKOUG – Monday First Impressions and The Human Lottery Show  Accepted AMIS Oracle Open World Presentations
Average Rating
5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
 (Add your review)
 2 thoughts on “Saving (XML) data directly to disk”
     
  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.  
  2. 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.
     
Comments are closed.
  Next Post 
Writing tables to xml
Thu Feb 14 , 2008
I was searching information about writing tables to a XML file. The process consists of 2 step Creating the XML file Writing the XML to disk The last step is easy (see https://technology.amis.nl/blog/?p=2853 Of my colleague Marco). This post concentrates on the first step. Related posts: APEX installation via the […]
You May Like
 
  
                        
%d bloggers like this: