Writing tables to xml

Willem-Jan Overink 1

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>

One thought on “Writing tables to xml

  1. I wish I read this yesterday. It would save me some time today 🙂 I’ve archived the same with more code and UTL_FILE.

Comments are closed.

Next Post

Fear for renaming (refactoring) ADF BC objects in JDeveloper 10.1.3.3 is not unfounded

Facebook0TwitterLinkedinGrowing insight during development of a project often results in a wish to rename a BC object (entity object, view object, association, view object attribute, alias, usage, etc.) in JDeveloper. Or in JHeadstart to rename objects like a group, item or a region. Often, developers are not renaming objects because it is […]