23 December 2010

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
ein
Pat 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>

4 comments:

  1. Hi Alex

    Great post, its quite handy to know we can use object types. Another good alternative for generating XML hierarchy structures without too much pain is DBMS_XMLGEN.newContextFromHierarchy. The following URL is a good reference point: http://awads.net/wp/2007/02/20/an-easy-way-to-convert-a-hierarchical-query-result-to-xml/

    Cheers
    Matt

    ReplyDelete
  2. Hi Matt,

    Thank you for this link, I didn't know that. There are a number of possibilities with the XMLType. One other very powerful way is by passing a Ref Cursor as an argument.

    Alex

    ReplyDelete
  3. Hi Alex,

    Good post and more informative.

    Thanks!

    Oracle Applications

    ReplyDelete
  4. I wasn't aware of this concept but its good to know that we can use Object types for creating XML types. The code given for this seems quite lengthy and bit difficult but I am going to try it as it will make my task easy.

    ReplyDelete