An Easy Way to Convert a Hierarchical Query Result to XML

Consider the following classic employees table:

SQL> SELECT employee_id,
  2    manager_id,
  3    first_name,
  4    salary,
  5    hire_date
  6  FROM employees;

EMPLOYEE_ID MANAGER_ID FIRST_NAME               SALARY HIRE_DATE
----------- ---------- -------------------- ---------- ---------
        100            Steven                    24000 17-JUN-87
        101        100 Neena                     17000 21-SEP-89
        102        100 Lex                       17000 13-JAN-93
        103        102 Alexander                  9000 03-JAN-90
        104        103 Bruce                      6000 21-MAY-91
        105        103 David                      4800 25-JUN-97
        106        103 Valli                      4800 05-FEB-98
        107        103 Diana                      4200 07-FEB-99
        108        101 Nancy                     12000 17-AUG-94
        109        108 Daniel                     9000 16-AUG-94
        110        108 John                       8200 28-SEP-97
        ...

The manager_id is also an employee_id. So, we can build the following hierarchical query:

 

SQL> SELECT
  2    rpad(' ',   4 * LEVEL -1,   ' ') || first_name tree,
  3    LEVEL,
  4    employee_id,
  5    manager_id,
  6    salary,
  7    hire_date
  8  FROM employees
  9  START WITH manager_id IS NULL
 10  CONNECT BY PRIOR employee_id = manager_id
 11  ORDER siblings BY first_name;

TREE                      LEVEL EMPLOYEE_ID MANAGER_ID     SALARY HIRE_DATE
-------------------- ---------- ----------- ---------- ---------- ---------
   Steven                     1         100                 24000 17-JUN-87
       Adam                   2         121        100       8200 10-APR-97
           Alexis             3         185        121       4100 20-FEB-97
           Anthony            3         187        121       3000 07-FEB-99
           James              3         131        121       2500 16-FEB-97
           Julia              3         186        121       3400 24-JUN-98
           Laura              3         129        121       3300 20-AUG-97
           Mozhe              3         130        121       2800 30-OCT-97
           Nandita            3         184        121       4200 27-JAN-96
           TJ                 3         132        121       2100 10-APR-99
       Alberto                2         147        100      12000 10-MAR-97
       ...

Now what we want is to transform the above result set to XML. We could use DBMS_XMLGEN.getxml like this:

SQL> SELECT DBMS_XMLGEN.getxml('
  2          SELECT
  3            employee_id,
  4            manager_id,
  5            first_name,
  6            salary,
  7            hire_date
  8          FROM employees
  9          START WITH manager_id IS NULL
 10         CONNECT BY PRIOR employee_id = manager_id
 11         ORDER siblings BY first_name
 12  ') xml
 13  FROM dual;

XML
----------------------------------------------------    
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <SALARY>24000</SALARY>
  <HIRE_DATE>17-JUN-87</HIRE_DATE>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>121</EMPLOYEE_ID>
  <MANAGER_ID>100</MANAGER_ID>
  <FIRST_NAME>Adam</FIRST_NAME>
  <SALARY>8200</SALARY>
  <HIRE_DATE>10-APR-97</HIRE_DATE>
 </ROW>
 ...
</ROWSET>

But this is not what we want. We want the hierarchical structure to be maintained in the XML output. In other words, we would like to have hierarchical XML with recursive elements.

Starting with Oracle Database 10g Release 1 (10.0.1), we can use DBMS_XMLGEN.newContextFromHierarchy like this:

SQL> SELECT XMLELEMENT
  2            ("employees",
  3             (SELECT DBMS_XMLGEN.getxmltype
  4                        (DBMS_XMLGEN.newcontextfromhierarchy
  5                            ('SELECT level,
  6                XMLElement("employee",
  7                           XMLElement("id", employee_id),
  8                           XMLElement("name", first_name),
  9                           XMLElement("salary", salary),
 10                           XMLElement("hiredate", hire_date))
 11           FROM employees
 12           START WITH manager_id is null
 13           CONNECT BY PRIOR employee_id = manager_id
 14           ORDER SIBLINGS BY first_name'))
 15                FROM DUAL)) xmldoc
 16    FROM DUAL;

XMLDOC
-----------------------------------------------------------------------
<employees>
  <employee>
    <id>100</id>
    <name>Steven</name>
    <salary>24000</salary>
    <hiredate>1987-06-17</hiredate>
    <employee>
      <id>121</id>
      <name>Adam</name>
      <salary>8200</salary>
      <hiredate>1997-04-10</hiredate>
      <employee>
        <id>185</id>
        <name>Alexis</name>
        <salary>4100</salary>
        <hiredate>1997-02-20</hiredate>
      </employee>
    </employee>
    ...
</employees>

And that’s what we want.

Here is what the documentation says about the DBMS_XMLGEN.newContextFromHierarchy function:

newContextFromHierarchy (queryString IN VARCHAR2) RETURN ctxHandle;

Parameter: queryString (IN) – the query string, the result of which must be converted to XML. The query is a hierarchical query typically formed using a CONNECT BY clause, and the result must have the same property as the result set generated by a CONNECT BY query. The result set must have only two columns, the level number and an XML value. The level number is used to determine the hierarchical position of the XML value within the result XML document.

Returns: Context handle. Call this function first to obtain a handle that you can use in the getXML() and other functions to get a hierarchical XML with recursive elements back from the result.

Since the function returns a context handle, it is a good practice to close the context to release all resources associated with that context, including the SQL cursor and bind and define buffers, and so on. This can be done using the procedure DBMS_XMLGEN.closeContext and requires the use of PL/SQL:

SQL> CREATE TABLE xml_documents OF  XMLTYPE
  2  /

Table created.

SQL> DECLARE
  2     qryctx DBMS_XMLGEN.ctxhandle;
  3  BEGIN
  4     qryctx :=
  5        DBMS_XMLGEN.newcontextfromhierarchy
  6           ('SELECT level,
  7                XMLElement("employee",
  8                           XMLElement("id", employee_id),
  9                           XMLElement("name", first_name),
 10                           XMLElement("salary", salary),
 11                           XMLElement("hiredate", hire_date))
 12           FROM employees
 13           START WITH manager_id is null
 14           CONNECT BY PRIOR employee_id=manager_id
 15           ORDER SIBLINGS BY first_name');
 16
 17     INSERT INTO xml_documents
 18        SELECT XMLELEMENT ("emloyees", DBMS_XMLGEN.getxmltype (qryctx))
 19          FROM DUAL;
 20
 21     DBMS_XMLGEN.closecontext (qryctx);
 22  END;
 23  /

PL/SQL procedure successfully completed.

SQL> SELECT sys_nc_rowinfo$
  2    FROM xml_documents
  3  /

SYS_NC_ROWINFO$
------------------------------------------------------------------------------
<employees>
  <employee>
    <id>100</id>
    <name>Steven</name>
    <salary>24000</salary>
    <hiredate>1987-06-17</hiredate>
    <employee>
      <id>121</id>
      <name>Adam</name>
      <salary>8200</salary>
      <hiredate>1997-04-10</hiredate>
      <employee>
        <id>185</id>
        <name>Alexis</name>
        <salary>4100</salary>
        <hiredate>1997-02-20</hiredate>
      </employee>
    </employee>
    ...
</employees>

A couple of notes:

• The XMLType datatype was first introduced in Oracle Database 9i Release 1 (9.0.1)

• Oracle XML DB is a feature of the Oracle Database. It provides a high-performance, native XML storage and retrieval technology. You can verify that XML DB has been installed by simply running the SQL below:

    SQL> SELECT comp_name
      2    FROM dba_registry
      3  WHERE comp_name LIKE '%XML%';

    COMP_NAME
    ------------------------------------
    Oracle XML Database

Sources and Resources:

• Getting into SQL/XML
• Oracle XML DB
• Generate hierarchical XML
• Oracle XML DB Developer’s Guide

Related articles:

• Beware of Comments in SQL
• Producing XML from SQL using cursor expressions
• Cool SQL Analytic Function: NTILE
• How to select an xmltree recursive?

 

from: http://awads.net/wp/2007/02/20/an-easy-way-to-convert-a-hierarchical-query-result-to-xml/