Create output XML data files from oracle with dbms_xmlgen
The below code will generate XML format output, just plug in any SQL and it will work without further development.
This is based on three articles by other authors:
- Tim Hall – but Tim’s code has a bug that stops it outputting after the first 32k of data.
- Tom Kyte – the fix for that bug.
- Steve Karam – code to nest XML data.
Performance is kept optimal by using set based SQL statements without cursor loops, and by using utl_file.put to write in 32k chunks rather than line-by-line of dbms_output.put_line or utl_file.put_line.
Before generating XML, ask if this is the best approach for transferring the data. Database to database copying is better done across database link, if possible.
Code:
CREATE OR REPLACE PROCEDURE myxmlproc AS
/*
* Procedure: myxmlproc
* Purpose: Creates XML data file from any SQL statements
* Depends on: Directory (formerly utl_file_dir) needs set up in database with permissions to
* allow writing to file with utl_file, e.g.:
* CREATE DIRECTORY mydir AS '/U01/oracle/mydir' ;
* GRANT READ , WRITE on mydir TO scott ;
* Based on: http://andrewfraserdba.com/2014/01/26/create-output-xml-data-files-from-oracle-with-dbms_xmlgen/
* http://www.oracle-base.com/articles/9i/xml-generation-9i.php#dbms_xmlgen and
* http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:744825627183
* http://www.dba-oracle.com/t_dbms_xmlgen.htm
*
* Version: A00
* Date: 26-Jan-2014
* Author: Andrew Fraser.
* Comments: First version.
*/
l_ctx DBMS_XMLGEN.ctxhandle ;
l_file UTL_FILE.file_type ;
l_xml CLOB ;
l_more BOOLEAN ;
/*
* Output code called after every SELECT, so keeping in private procedure to avoid duplication of coding.
* Based on : http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:744825627183
*/
PROCEDURE output_to_file AS
l_amt NUMBER DEFAULT 32000 ;
l_offset NUMBER DEFAULT 1 ;
l_length NUMBER ;
BEGIN
/*
* Create the XML document.
*/
l_xml := DBMS_XMLGEN.getxml ( l_ctx ) ;
DBMS_XMLGEN.closecontext ( l_ctx ) ;
/*
* Output XML document to file.
*/
l_length := NVL ( DBMS_LOB.getlength ( l_xml ) , 0 ) ;
WHILE ( l_offset < l_length )
LOOP
UTL_FILE.put ( l_file , DBMS_LOB.substr ( l_xml , l_amt , l_offset ) ) ;
UTL_FILE.fflush ( l_file ) ;
l_offset := l_offset + l_amt ;
END LOOP ;
UTL_FILE.new_line ( l_file ) ;
END output_to_file ;
BEGIN
/*
* open file for writing
*/
l_file := UTL_FILE.fopen ( '/U01/oracle/mydir' , 'myxmlproc.xml' , 'w' , 32760 ) ;
/*
* 1) Dept table
* Create XML context.
*/
l_ctx := DBMS_XMLGEN.newcontext ( q'[
SELECT d.deptno
, d.dname
, d.loc
, INITCAP ( d.loc ) location
FROM dept d
WHERE d.dname != 'Invalid'
ORDER BY 1
]' ) ;
/*
* Set parameters to alter default Rowset and Row tag names.
*/
DBMS_XMLGEN.setrowsettag ( l_ctx , 'Depts' ) ;
DBMS_XMLGEN.setrowtag ( l_ctx , 'Dept' ) ;
output_to_file ;
/*
* 2) Emp
* Create XML context.
*/
l_ctx := DBMS_XMLGEN.newcontext ( q'[
SELECT e.*
FROM emp e
WHERE e.hiredate > TO_DATE ( '01-APR-1950' , 'DD-MON-YYYY' )
ORDER BY e.empno
]' ) ;
/*
* Set parameters to alter default Rowset and Row tag names.
*/
DBMS_XMLGEN.setrowsettag ( l_ctx , 'Emps' ) ;
DBMS_XMLGEN.setrowtag ( l_ctx , 'Emp' ) ;
output_to_file ;
/*
* 3) Nested XML of Dept and Emp together
* Create XML context.
*/
l_ctx := DBMS_XMLGEN.newcontext ( q'[
SELECT d.deptno
, d.dname
, d.loc
, INITCAP ( d.loc ) location
, CURSOR (
SELECT e.empno
, e.ename
, e.job
, e.mgr
, e.hiredate
, e.sal
, e.comm
FROM emp e
WHERE e.hiredate > TO_DATE ( '01-APR-1950' , 'DD-MON-YYYY' )
AND e.deptno = d.deptno
) emp
FROM dept d
WHERE d.dname != 'Invalid'
ORDER BY 1
]' ) ;
/*
* Set parameters to alter default Rowset and Row tag names.
*/
DBMS_XMLGEN.setrowsettag ( l_ctx , 'Depts' ) ;
DBMS_XMLGEN.setrowtag ( l_ctx , 'Dept' ) ;
output_to_file ;
UTL_FILE.fclose ( l_file ) ;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.fclose ( l_file ) ;
RAISE ; -- triggers a rollback, as well as writing more error info to screen
END myxmlproc ;
/
sho err
exec myxmlproc
host view /U01/oracle/mydir/myxmlproc.xml
Output:
10 ACCOUNTING NEW YORK New York 20 RESEARCH DALLAS Dallas 30 SALES CHICAGO Chicago 40 OPERATIONS BOSTON Boston 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK New York 7839 KING PRESIDENT 17-NOV-81 5000 7782 CLARK MANAGER 7839 09-JUN-81 2450 7934 MILLER CLERK 7782 23-JAN-82 1300 20 RESEARCH DALLAS Dallas 7566 JONES MANAGER 7839 02-APR-81 2975 7788 SCOTT ANALYST 7566 19-APR-87 3000 7902 FORD ANALYST 7566 03-DEC-81 3000 7369 SMITH CLERK 7902 17-DEC-80 800 7876 ADAMS CLERK 7788 23-MAY-87 1100 30 SALES CHICAGO Chicago 7698 BLAKE MANAGER 7839 01-MAY-81 2850 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 7900 JAMES CLERK 7698 03-DEC-81 950 40 OPERATIONS BOSTON Boston
Hi, nice code, thank you.
But I have a question:
You use setrowsettag / setrowtag to set a readable tag for the departments:
DBMS_XMLGEN.setrowsettag ( l_ctx , ‘Depts’ ) ;
DBMS_XMLGEN.setrowtag ( l_ctx , ‘Dept’ ) ;
Is there a similar way to set the tags for the emploees in the cursor, instead of ?
Greetings, Martin