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).

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

4 comments:

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/

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.

About Me

Self-employed under the name allAPEX, mainly in The Netherlands. Presented at National and International Conferences. Oracle ACE Director for Database Development. Trainer for SQL and PL/SQL. Married, two children, likes to Barbecue.