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.
I decided
to create a XML file based on the standard DEPT and EMP tables. The following
code did the trick:
PROCEDURE writexml IS CURSOR xmlcur IS SELECT xmlelement("DEPARTMENTS", xmlagg( xmlelement("DEPARTMENT", xmlattributes(d.loc as "LOC"), xmlconcat( xmlforest(d.dname as "NAME", d.deptno as "DEPTNO") ,xmlelement("EMPLOYEES" ,xmlagg(xmlelement("EMPLOYEE", xmlforest(e.empno as "EMPNO", e.ename as "NAME", e.job as "JOB", e.hiredate as "HIREDATE", e.sal as "SAL")) ) ) ) ) ) ) FROM emp e , dept d WHERE e.DEPTNO=d.DEPTNO GROUP BY d.deptno, d.dname, d.loc ; l_emp xmltype; BEGIN open xmlcur; fetch xmlcur into l_emp; close xmlcur; dbms_xslprocessor.clob2file( l_emp.getclobval( ) , 'TEMP', 'test.xml' ); END;
- The
XMLelement is used to create an XML element based on the column value. - With the XMLAgg it is possible to generate collections of xml elements.
- XMLForest is used to generate several xml elements successively. It is the same
if you are using several XMLelements. - With XMLConcat you can combine XMLType values.
The xml output
is the following:
<DEPARTMENTS> <DEPARTMENT LOC="NEW YORK"> <NAME>ACCOUNTING</NAME> <DEPTNO>10</DEPTNO> <EMPLOYEES> <EMPLOYEE> <EMPNO>7782</EMPNO> <NAME>CLARK</NAME> <JOB>MANAGER</JOB> <HIREDATE>1981-06-09</HIREDATE> <SAL>2450</SAL> </EMPLOYEE> <EMPLOYEE> <EMPNO>7839</EMPNO> <NAME>KING</NAME> <JOB>PRESIDENT</JOB> <HIREDATE>1981-11-17</HIREDATE> <SAL>5000</SAL> </EMPLOYEE> </EMPLOYEES> </DEPARTMENT> <DEPARTMENT LOC="DALLAS"> <NAME>RESEARCH</NAME> <DEPTNO>20</DEPTNO> <EMPLOYEES> <EMPLOYEE> <EMPNO>7369</EMPNO> <NAME>SMITH</NAME> <JOB>CLERK</JOB> <HIREDATE>1980-12-17</HIREDATE> <SAL>800</SAL> </EMPLOYEE> <EMPLOYEE> <EMPNO>7902</EMPNO> <NAME>FORD</NAME> <JOB>ANALYST</JOB> <HIREDATE>1981-12-03</HIREDATE> <SAL>3000</SAL> </EMPLOYEE> <EMPLOYEE> <EMPNO>7876</EMPNO> <NAME>ADAMS</NAME> <JOB>CLERK</JOB> <HIREDATE>1983-01-12</HIREDATE> <SAL>1100</SAL> </EMPLOYEE> </EMPLOYEES> </DEPARTMENT> ... </DEPARTMENTS>
I wish I read this yesterday. It would save me some time today 🙂 I’ve archived the same with more code and UTL_FILE.