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.

Adding records to View Objects at custom locations

if you are using a table component in your adf page you should have noticed by now that when you invoke the createInsert method of any view object then the new record will be added before the currently selected row.of course this may not be that big of an issue ; since you can sort the table using any column you want.

But you may have a use case where it's required to add the row after the current row, first or last row in table.

the following are the steps needed to achieve such goals:

1. open the view object xml file in the overview mode and open the java tab.
2. generate the ViewObjectImpl class.
3. now you can either :
• override the insert method of the view object .
• add your own custom methods and expose them to the client.

if you want to override the insert method add the following in the overriding method body:

• insert as the last row:

                  @Override

                   public void insertRow(Row row){

                        last();

                        next();

                        super.insertRow(row);

                   }

• insert as the first row:

                  @Override

                   public void insertRow(Row row){

                        first();

                        super.insertRow(row);

                   }

• insert as the following row:

                  @Override

                   public void insertRow(Row row){

                        next();

                        super.insertRow(row);

                   }

then call the createInsert operation of the view object.

or you can write your own custom code, similar to:

                  public void insertLast(){

                            last();

                           next();

                          Row r= createRow();

                          insertRow(r);

                 }

then expose to the client and call it from the page.