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