Writing tables to xml

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 http://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<br />    IS                                                                                                                                                                                                                        <br />      CURSOR xmlcur IS                                                                                                     				  <br />         SELECT xmlelement(&quot;DEPARTMENTS&quot;, <br />            xmlagg(	<br />	       xmlelement(&quot;DEPARTMENT&quot;, xmlattributes(d.loc as &quot;LOC&quot;),<br />	          xmlconcat(<br />	             xmlforest(d.dname as &quot;NAME&quot;, d.deptno as &quot;DEPTNO&quot;)<br />			,xmlelement(&quot;EMPLOYEES&quot;<br />		            ,xmlagg(xmlelement(&quot;EMPLOYEE&quot;, xmlforest(e.empno as &quot;EMPNO&quot;, e.ename as &quot;NAME&quot;, e.job as &quot;JOB&quot;, e.hiredate as &quot;HIREDATE&quot;, e.sal as &quot;SAL&quot;))<br />		        ) <br />	             )  <br />	          )  <br />	       )<br />	    )            <br />        )			                                                                                                     <br />    FROM   emp e                                                                                                        <br />     ,      dept d                                                                                                       <br />    WHERE  e.DEPTNO=d.DEPTNO     <br />	GROUP BY d.deptno, d.dname, d.loc ;<br />	<br />	l_emp xmltype;                                                                                     <br />   BEGIN<br />   		open xmlcur;<br />		fetch xmlcur into l_emp;<br />                close xmlcur;<br />  		dbms_xslprocessor.clob2file( l_emp.getclobval( ) , 'TEMP', 'test.xml' );<br />   END;<br />

• 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:

&lt;DEPARTMENTS&gt;<br />  &lt;DEPARTMENT LOC=&quot;NEW YORK&quot;&gt;<br />   &lt;NAME&gt;ACCOUNTING&lt;/NAME&gt;<br />   &lt;DEPTNO&gt;10&lt;/DEPTNO&gt;<br />   &lt;EMPLOYEES&gt;<br />     &lt;EMPLOYEE&gt;<br />       &lt;EMPNO&gt;7782&lt;/EMPNO&gt;<br />       &lt;NAME&gt;CLARK&lt;/NAME&gt;<br />       &lt;JOB&gt;MANAGER&lt;/JOB&gt;<br />       &lt;HIREDATE&gt;1981-06-09&lt;/HIREDATE&gt;<br />       &lt;SAL&gt;2450&lt;/SAL&gt;<br />     &lt;/EMPLOYEE&gt;<br />     &lt;EMPLOYEE&gt;<br />       &lt;EMPNO&gt;7839&lt;/EMPNO&gt;<br />       &lt;NAME&gt;KING&lt;/NAME&gt;<br />       &lt;JOB&gt;PRESIDENT&lt;/JOB&gt;<br />       &lt;HIREDATE&gt;1981-11-17&lt;/HIREDATE&gt;<br />       &lt;SAL&gt;5000&lt;/SAL&gt;<br />     &lt;/EMPLOYEE&gt;<br />   &lt;/EMPLOYEES&gt;<br />  &lt;/DEPARTMENT&gt;<br />  &lt;DEPARTMENT LOC=&quot;DALLAS&quot;&gt;<br />    &lt;NAME&gt;RESEARCH&lt;/NAME&gt;<br />    &lt;DEPTNO&gt;20&lt;/DEPTNO&gt;<br />    &lt;EMPLOYEES&gt;<br />      &lt;EMPLOYEE&gt;<br />        &lt;EMPNO&gt;7369&lt;/EMPNO&gt;<br />        &lt;NAME&gt;SMITH&lt;/NAME&gt;<br />        &lt;JOB&gt;CLERK&lt;/JOB&gt;<br />        &lt;HIREDATE&gt;1980-12-17&lt;/HIREDATE&gt;<br />        &lt;SAL&gt;800&lt;/SAL&gt;<br />      &lt;/EMPLOYEE&gt;<br />      &lt;EMPLOYEE&gt;<br />        &lt;EMPNO&gt;7902&lt;/EMPNO&gt;<br />        &lt;NAME&gt;FORD&lt;/NAME&gt;<br />        &lt;JOB&gt;ANALYST&lt;/JOB&gt;<br />        &lt;HIREDATE&gt;1981-12-03&lt;/HIREDATE&gt;<br />        &lt;SAL&gt;3000&lt;/SAL&gt;<br />      &lt;/EMPLOYEE&gt;<br />      &lt;EMPLOYEE&gt;<br />        &lt;EMPNO&gt;7876&lt;/EMPNO&gt;<br />        &lt;NAME&gt;ADAMS&lt;/NAME&gt;<br />        &lt;JOB&gt;CLERK&lt;/JOB&gt;<br />        &lt;HIREDATE&gt;1983-01-12&lt;/HIREDATE&gt;<br />        &lt;SAL&gt;1100&lt;/SAL&gt;<br />      &lt;/EMPLOYEE&gt;<br />    &lt;/EMPLOYEES&gt;<br />  &lt;/DEPARTMENT&gt;<br />...<br />&lt;/DEPARTMENTS&gt;<br />