This discussion is archived
2 Replies Latest reply: Oct 4, 2012 10:15 AM by 966197 RSS

Forming XML based on Dept/Emp Tables

966197 Newbie
Currently Being Moderated
Hi,

I have to form the XML as below from DEPARTMENTS, EMPLOYEES tables in HR schema. I am using Oracle 11g R2.

<?xml version="1.0"?>
<DEPARTMENT_LIST>
<DEPARTMENT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<EMPLOYEE_LIST>
<EMPLOYEE>
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<EMPLOYEE_NAME>Jennifer</EMPLOYEE_NAME>
</EMPLOYEE>
</EMPLOYEE_LIST>
</DEPARTMENT>
<DEPARTMENT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMPLOYEE_LIST>
<EMPLOYEE>
<EMPLOYEE_ID>201</EMPLOYEE_ID>
<EMPLOYEE_NAME>Michael</EMPLOYEE_NAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPLOYEE_ID>202</EMPLOYEE_ID>
<EMPLOYEE_NAME>Pat</EMPLOYEE_NAME>
</EMPLOYEE>
</EMPLOYEE_LIST>
</DEPARTMENT>
<DEPARTMENT>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
<DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
<EMPLOYEE_LIST>
<EMPLOYEE>
<EMPLOYEE_ID>114</EMPLOYEE_ID>
<EMPLOYEE_NAME>Den</EMPLOYEE_NAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPLOYEE_ID>119</EMPLOYEE_ID>
<EMPLOYEE_NAME>Karen</EMPLOYEE_NAME>
</EMPLOYEE>
               ...
               ...
</EMPLOYEE_LIST>
</DEPARTMENT>          
</DEPARTMENT_LIST>

Please help on that... From XMLAGG, XMLFOREST, XMLELEMENT how to achieve that??

Thanks,
Sathya

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points