Creating an XMLType based on Object Types

Sometimes it is necessary to create a Stored Procedure which returns an XMLType, like when you want to expose the Stored Procedure to the "outside world", like via a Mediator. There are several options to create an XMLType. In this blogpost I will show you two ways of doing this. First the "regular" way using XMLElement, XMLForest and XMLAgg. Second using Object Types which are being converted to XMLType.

For this example I will use the HR schema, and in it are the DEPARTMENTS and EMPLOYEES tables, which has a Master-Detail relation between them. The DEPARTMENTS table
has a MANAGER_ID (the manager of the department). The EMPLOYEES table also has a MANAGER_ID (the person who the employee reports to).

SQL> select d.department_name
  2       , d.manager_id
  3       , e.first_name
  4       , e.last_name
  5       , e.manager_id
  6    from departments d
  7    join employees   e
  8      on d.department_id = e.department_id
  9   where d.department_id = 20
 10  /

DEPARTMENT_NAME                MANAGER_ID FIRST_NAME           LAST_NAME                 MANAGER_ID
------------------------------ ---------- -------------------- ------------------------- ----------
Marketing                             201 Michael              Hartstein                     100
Marketing                             201 Pat                  Fay                           201

What I want is an XML structured like this:

<departments>
   <department>
      <departmentname>Marketing
      <department_manager>Steven King
      <employees>
         <employee>
            <name>Michael Hartstein
         </employee>
         <employee>
            <name>Pat Fay
         </employee>
      </employees>
   </department>
</departments>

In order to get that you can write a SQL statement like this one:

SQL> set long 90000 lines 80
SQL> select xmlelement("departments"
  2                   ,(select xmlagg(xmlelement("department"
  3                                             ,xmlelement ("departmentname"
  4                                                         ,d.department_name
  5                                                         )
  6                                             ,xmlelement ("department_manager"
  7                                                         ,(select m.first_name || ' ' ||
  8                                                                  m.last_name
  9                                                             from employees m
 10                                                            where m.employee_id = d.manager_id
 11                                                           )
 12                                                          )
 13                                             ,xmlforest ((select xmlagg(xmlelement("employee"
 14                                                                                  ,e.first_name || ' ' ||
 15                                                                                   e.last_name
 16                                                                                  )
 17                                                                       )
 18                                                            from employees e
 19                                                           where e.department_id = d.department_id
 20                                                         ) as "employees"
 21                                                        )
 22                                             )
 23                                  )
 24                       from departments d
 25                      where d.department_id = 20
 26                    )
 27                   )
 28    from dual
 29  /

XMLELEMENT("DEPARTMENTS",(SELECTXMLAGG(XMLELEMENT("DEPARTMENT",XMLELEMENT("DEPAR
--------------------------------------------------------------------------------
<departments>Marketinganager>Michael HartsteinMichael Hartst
einPat Fay>

But the are probably other ways of getting the same results as well. What you can take away from this statement is that it is quite hard to write and maintain.
Look at all those parentheses, enough to drive you crazy...

There is also the possibility of turning Object Types into XMLType, let's explore this route.
First some Object Types describing our desired XML output

SQL> create type employee_ot as object
  2  (employee_id   number
  3  ,employee_name varchar2 (60)
  4  );
  5  /

Type created.

SQL> create type employees_tt is table of employee_ot
  2  /

Type created.

SQL>
SQL> create type department_ot is object
  2  (departmentname    varchar2(30)
  3  ,departmentmanager employee_ot
  4  ,employees         employees_tt
  5  );
  6  /

Type created.

SQL> create type departments_tt is table of department_ot
  2  /

Type created.

SQL> create type all_departments_ot is object
  2  (departments departments_tt);
  3  /

Type created.

Now we can use these Object Types to construct a query like this

SQL> select department_ot (d.department_name
  2                       ,(select employee_ot (m.employee_id
  3                                           , m.first_name||' '||m.last_name
  4                                           )
  5                           from employees m
  6                          where m.employee_id = d.manager_id
  7                         )
  8                      ,cast ( multiset (select employee_ot (e.employee_id
  9                                    ,e.first_name||' '||e.last_name
 10                                    )
 11                                    from employees e where e.department_id = d.department_id
 12                                    )as employees_tt)
 13                        )
 14    from departments d
 15   where d.department_id = 20
 16  /

DEPARTMENT_OT(D.DEPARTMENT_NAME,(SELECTEMPLOYEE_OT(M.EMPLOYEE_ID,M.FIRST_NAME||'
--------------------------------------------------------------------------------
DEPARTMENT_OT('Marketing', EMPLOYEE_OT(201, 'Michael Hartstein'), EMPLOYEES_TT(E
MPLOYEE_OT(201, 'Michael Hartstein'), EMPLOYEE_OT(202, 'Pat Fay')))

So What, you say?
Well, if you do a describe of the XMLTYPE (like in SQL*Plus), you will see this:

SQL> desc xmltype
...
METHOD
------
 FINAL CONSTRUCTOR FUNCTION XMLTYPE RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XMLDATA                        UNDEFINED               IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 ELEMENT                        VARCHAR2                IN     DEFAULT
 VALIDATED                      NUMBER                  IN     DEFAULT

...

You can use an Object Type as an input to create an XMLTYPE, pretty nifty.
The structure of the Object Types needs to match the structure of the XML.
Just add the XMLTYPE to the query and presto:

SQL> select xmltype (
  2        all_departments_ot (cast (
  3         collect (department_ot (d.department_name
  4                                ,(select employee_ot (m.employee_id
  5                                                     ,m.first_name||' '||m.last_name
  6                                                     )
  7                                    from employees m
  8                                   where m.employee_id = d.manager_id
  9                                 )
 10                                ,cast ( multiset (select employee_ot (e.employee_id
 11                                                                     ,e.first_name||' '||e.last_name
 12                                                                     )
 13                                                    from employees e
 14                                                   where e.department_id = d.department_id
 15                                                 )
 16                                        as employees_tt
 17                                       )
 18                                 )
 19                 ) as departments_tt)))
 20    from departments d
 21   where d.department_id = 20
 22  /

XMLTYPE(ALL_DEPARTMENTS_OT(CAST(COLLECT(DEPARTMENT_OT(D.DEPARTMENT_NAME,(SELECTEMPLOYEE_OT(M.EMPLOYEE_I

-------------------------------------------------------------------------------------------------------

<ALL_DEPARTMENTS_OT>
  <DEPARTMENTS>
    <DEPARTMENT_OT>
      <DEPARTMENTNAME>Marketing
      <DEPARTMENTMANAGER>
        <EMPLOYEE_ID>201
        <EMPLOYEE_NAME>Michael Hartstein
      </DEPARTMENTMANAGER>
      <EMPLOYEES>
        <EMPLOYEE_OT>
          <EMPLOYEE_ID>201
          <EMPLOYEE_NAME>Michael Hartstein
        </EMPLOYEE_OT>
        <EMPLOYEE_OT>
          <EMPLOYEE_ID>202
          <EMPLOYEE_NAME>Pat Fay
        </EMPLOYEE_OT>
      </EMPLOYEES>
    </DEPARTMENT_OT>
  </DEPARTMENTS>
</ALL_DEPARTMENTS_OT>