2 Replies Latest reply: Oct 4, 2012 12:15 PM by 966197 RSS

    Forming XML based on Dept/Emp Tables

    966197
      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