In this post I will show an example of how you can transform a query result into an XML document. I will also show how you can write a query that produces nested or multi-leveled XML document using cursor expressions.

We have a department table:

HR@XE> select department_name
  2  from departments
  3  where department_id in (20,110)
  4  /

DEPARTMENT_NAME
------------------------------
Marketing
Accounting

To transform the above query result into an XML document is as simple as:

HR@XE> select dbms_xmlgen.getxml('
  2          select department_name
  3          from departments
  4          where department_id in (20,110)
  5          ') xml
  6  from dual
  7  /

XML
--------------------------------------------------

<ROWSET>
 <ROW>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
 </ROW>
</ROWSET>

As you can see, generating XML is as simple as using the Oracle’s supplied PL/SQL package dbms_xmlgen.

Let’s do the same for the employees table:

HR@XE> select first_name
  2  from employees
  3  where department_id in (20,110)
  4  /

FIRST_NAME
--------------------
Pat
William
Michael
Shelley

HR@XE> select dbms_xmlgen.getxml('
  2          select first_name
  3          from employees
  4          where department_id in (20,110)
  5          ') xml
  6  from dual
  7  /

XML
--------------------------------------------------

<ROWSET>
 <ROW>
  <FIRST_NAME>Pat</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>William</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Michael</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Shelley</FIRST_NAME>
 </ROW>
</ROWSET>

Now, I will join the employees and departments tables in one query:

HR@XE> select dept.department_name, emp.first_name
  2  from employees emp, departments dept
  3  where emp.department_id = dept.department_id
  4  and dept.department_id in (20,110)
  5  /

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Marketing                      Pat
Accounting                     William
Marketing                      Michael
Accounting                     Shelley

Applying dbms_xmlgen.getxml to the above query will give us the following result:

XML
--------------------------------------------------

<ROWSET>
 <ROW>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <FIRST_NAME>Pat</FIRST_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
  <FIRST_NAME>William</FIRST_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <FIRST_NAME>Michael</FIRST_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
  <FIRST_NAME>Shelley</FIRST_NAME>
 </ROW>
</ROWSET>

But that’s not what I exactly want. I do not want the department_name tag to repeat for every employee in that department. To do that, I have a couple of options. The first is to use object types and the second is to use cursor expressions. I will use a cursor expression in the following:

HR@XE> select dept.department_name,
  2          cursor (
  3          select emp.first_name
  4          from employees emp
  5          where emp.department_id = dept.department_id
  6          ) as emps
  7  from departments dept
  8  where dept.department_id in (20,110)
  9  /

DEPARTMENT_NAME                EMPS
------------------------------ --------------------
Marketing                      CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

FIRST_NAME
--------------------
Michael
Pat

Accounting                     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

FIRST_NAME
--------------------
Shelley
William

Now, transforming the above query into XML:

HR@XE> select dbms_xmlgen.getxml('
  2          select dept.department_name,
  3          cursor (
  4          select emp.first_name
  5          from employees emp
  6          where emp.department_id = dept.department_id
  7          ) as emps
  8          from departments dept
  9          where dept.department_id in (20,110)
 10          ') xml
 11  from dual
 12  /

gives us:

XML
--------------------------------------------------

<ROWSET>
 <ROW>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <EMPS>
   <EMPS_ROW>
    <FIRST_NAME>Michael</FIRST_NAME>
   </EMPS_ROW>
   <EMPS_ROW>
    <FIRST_NAME>Pat</FIRST_NAME>
   </EMPS_ROW>
  </EMPS>
 </ROW>
 <ROW>
  <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
  <EMPS>
   <EMPS_ROW>
    <FIRST_NAME>Shelley</FIRST_NAME>
   </EMPS_ROW>
   <EMPS_ROW>
    <FIRST_NAME>William</FIRST_NAME>
   </EMPS_ROW>
  </EMPS>
 </ROW>
</ROWSET>

And that’s exactly what I want.

You can use cursor expressions within cursor expressions within cursor expressions… In other words, from a single query, you can produce a not so simple XML document without writing PL/SQL code.

Cursor expressions (sometimes known as cursor subqueries) are an element of the SQL language. In pre-Oracle 9i they were supported in SQL but not PL/SQL. In Oracle 9i and above, cursor expressions are supported in SQL as well as PL/SQL.

Here are some cursor expression resources:

AskTom: Cursor Expressions Vs Scalar Subqueries
OTN: Using a Cursor Expression as an actual parameter to a PL/SQL function
O’Reilly: Table Functions and Cursor Expressions
Oracle docs: CURSOR Expressions

Related articles:

• 5 Recommendations About Cursor FOR Loops in Oracle PL/SQL
• XML Goodness in SQL - Part 2

---

Tagged cursor, expression, xml | Comments Closed | Trackbacks Closed