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.