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>Marketing 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>
![DBPITR with a rman archival backup]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "DBPITR with a rman archival backup")
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,
Â
-t-
Oh, I agree, just pointed out that in this case it might also be solved via a simple select within XMLTYPE(CURSOR())
@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.
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)
Â