Creating an XMLType based on Object Types

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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:

      &lt;department_manager>Steven King</department_manager>
            &lt;name>Michael Hartstein</name>
            &lt;name>Pat Fay</name>

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  /

anager>Michael Hartstein</department_manager><employees><employee>Michael Hartst
ein</employee><employee>Pat Fay</employee></employees></department></departments

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> 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('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
 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  /



        &lt;EMPLOYEE_NAME>Michael Hartstein</employee_NAME>
          &lt;EMPLOYEE_NAME>Michael Hartstein</employee_NAME>
          &lt;EMPLOYEE_NAME>Pat Fay</employee_NAME>
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author


  1. alex, great blog, as usual. thanks. one question remains. i can’t appreciate the last definition of the type called all_departments_ot.
    in the query without the xmltype you don’t use it. in the one that includes the xmltype you do. can you explain that difference, and the need for the use of the all_departments_ot?
    much obliged,

  2. @Marco,

    Personally I find the Object Types (with the CASTs and COLLECTs) a lot easier to read (and write) than using XMLType (with all the XMLElement, XMLAgg and so on).

    Thank you for your comment.

  3. The problem with object types is that complex nested structures will have very fast the same issue as your first XML/SQL example.

    Based on your needs the easiest way to achieve this would be to use your query as input for XMLTYPE which can handle CURSOR constructs to for example:

    select XMLTYPE(CURSOR( select d.department_name, d.manager_id, e.first_name, e.last_name, e.manager_id from departments d join employees   e on d.department_id = e.department_id where d.department_id = 20)) FROM DUAL;

    Be aware that the “pretty print” format is not needed and creates overhead for the XML parser. Use XMLSerialize to bring it back to one big string without all the CR/LF and whitespace, if needed (this is the default in Oracle 11g and onwards)