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