Oracle DB XML generation

As you can tell from the title this is article is not related to ADF, but to the oracle database.

the use case: 

    generate xml document based on a certain requirement in oracle database.

for example :using the HR schema, list all department id, name and all of it's employees as well as listing each employee name and job.

something like :   1     dept1

                                     10    emp1 clerk

                                     11    emp2  it

                           2    dept2

the solution:

there are several xml functions in the oracle database that can be used to generate and format xml documents. I will use them to generate the needed xml.

there are two solutions we found that could solve the issue:

1) simple :

SELECT DBMS_XMLGEN.getxml ('select d.department_name,d.department_id,cursor(select first_name ||'' ''||last_name name,job_id from employees e where E.DEPARTMENT_ID=D.DEPARTMENT_ID) employees from departments d ') FROM DUAL;

will  return something like:

<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Jennifer Whalen</NAME>

    <JOB_ID>AD_ASST</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

 <ROW>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Michael Hartstein</NAME>

    <JOB_ID>MK_MAN</JOB_ID>

   </EMPLOYEES_ROW>

   <EMPLOYEES_ROW>

    <NAME>Pat Fay</NAME>

    <JOB_ID>MK_REP</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

.

.

.

.

.

</ROWSET>

as you can see the  DBMS_XMLGEN.getxml generate the output based on whatever sql query is passed into it as input and the generated output tags are based on the column names.

still there are tags that are named row,rowset that you cannot control their names.

of course ,you can use the replace function to change those tags.

but what if the data returned from the query contains data that have strings similar to these tags?

this led me to look for another solution.

2) more complex:

DBMS_XMLGEN function is not the only xml function provided by oracle. There are handful of other functions, which we will use in the following solution:

SELECT SYS_XMLAGG (

          SYS_XMLGEN (

             XMLFOREST (

                D.DEPARTMENT_NAME,

                D.DEPARTMENT_ID,

                (SELECT SYS_XMLAGG (

                           SYS_XMLGEN (XMLFOREST (E.FIRST_NAME, E.JOB_ID),

                                       XMLFormat ('Employee')),

                           XMLFormat ('List'))

                   FROM employees e

                  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) "Employees"),

             XMLFormat ('Department')),

          XMLFormat ('Departments'))

          "xml query"

  FROM departments d;

which will produce :

<?xml version="1.0"?>

<Departments>

<Department>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Jennifer</FIRST_NAME>

        <JOB_ID>AD_ASST</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

<Department>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Michael</FIRST_NAME>

        <JOB_ID>MK_MAN</JOB_ID>

      </Employee>

      <Employee>

        <FIRST_NAME>Pat</FIRST_NAME>

        <JOB_ID>MK_REP</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

.

.

.

</Departments>

of course you can always write a lot of pl/sql code to read the result and concatenate the result to create the desired output.