msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Producing XML from SQL using cursor expressions

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
--------------------------------------------------
<?xml version="1.0"?>
<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
--------------------------------------------------
<?xml version="1.0"?>
<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
--------------------------------------------------
<?xml version="1.0"?>
<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
--------------------------------------------------
<?xml version="1.0"?>
<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


Filed in Oracle, Tips on 19 Dec 05 | Tags: , ,


Reader's Comments

  1. |

    Nice example, good thinking, I remember having this problem in the early Oracle XML days. With recent database versions however I would suggest to use SQLXML. It has more options, is more flexible and (in my opinion) easier. Your example would be something like:

    select  xmlelement ("company",
               xmlagg (xmlelement ("department",
               xmlelement ("name", dept.department_name),
               xmlelement ("emps",
                 (select xmlagg (xmlelement ("employee",
                   xmlattributes (emp.employee_id as "id"),
                   xmlforest (emp.first_name as "first_name")))
                 from    employees emp
                 where emp.department_id = dept.department_id)
                           )
                       )
               )
           ) xml
    from departments dept
    
  2. |

    Sorry, but the backslashes in my previous comment should not be there.

  3. |

    Aino, this is great, thanks. I ran your example above for department id 20 and 110 and got the following output:

    <company>
        <department>
            <name>Marketing</name>
            <emps>
                <employee id = "201">
                    <first_name>Michael</first_name>
                </employee>
                <employee id = "202">
                    <first_name>Pat</first_name>
                </employee>
            </emps>
        </department>
        <department>
            <name>Accounting</name>
            <emps>
                <employee id = "205">
                    <first_name>Shelley</first_name>
                </employee>
                <employee id = "206">
                    <first_name>William</first_name>
                </employee>
            </emps>
        </department>
    </company>
    

    I believe that SQLXML should work in 9i and 10g but not 8i.

    Oh! by the way, the backslashes are added by Markdown. I tried, but I could not get rid of them. Sometimes Markdown is bad :(

  4. |

    I think it’s available for 9iR2 and up. By the way, this is part of the SQL2003 standard :-). Check out: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#g1047191 As of 10gR2 you can also use XQuery, but I do not have any experince with that. My first impression was that I understand SQLXML better.

  5. |

    […] Eddie Awad has an interesting post on generating xml using cursor expressions: Producing XML from SQL using cursor expressions. Be sure to also read Aino’s comment, where he shows how you can achieve the same thing but using the xmlelement and xmlagg functions. […]

  6. |

    […] Producing XML from SQL using cursor expressions […]