Easy XML - Let the Database do the Work

I don’t want to learn XML. I don’t want to learn XPATH. I don’t want to learn XQUERY. I don’t want to learn XSLT. I don’t want to learn XSQL. I don’t want to learn XSU. XDB has some way cool stuff. But to-date, I have been pretty successful in not learning things I don’t want to learn. So you might think that when the time came for my databases to start sharing data via a XML transport, I would be in trouble. Fortunately, God gave me at least a few smarts when he loaded me up with laziness. In this article we will discuss one way to move XML formatted data in and out of Oracle using what we already know: object views, instead of triggers, collections, and PL/SQL packages.

Let me first say, I am not debating the value or merit of any of the XML technologies noted in the opening rants above. They all seem to be gaining ground so somebody must like using them. I am saying that I have a large investment in Oracle, SQL, and PL/SQL and I don’t see why I should have to continually be learning every new data language that comes out. I should be able to repurpose what I already know. Oracle, it seems, agrees. So they have provided a way (several in fact) for lazy people to postpone learning most of these XML oriented technologies till we really have too, allowing us instead to leverage our current investments in Oracle techniques.

The basic components of moving XML without learning lots of new crap are:

1) Oracle packages (DBMS_XMLGEN, DBMS_XMLSAVE)
2) Oracle object types (objects and collections)
3) Oracle object views and Instead of Triggers

First let us establish a working set of tables and data.

create table dept (dno number,dname varchar2(30));
create table emp (eno number,dno number,ename varchar2(30));
create table timesheet (medate date,eno number, hours number);
create table project (pno number,pname varchar2(30),dno number);
create table workassignment (wno number,eno number, pno number);
insert into dept values (1,'D1');
insert into emp values (11,1,'Kevin');
insert into timesheet values (trunc(last_day(sysdate)),11,160);
insert into project values (111,'P1',1);
insert into workassignment values (1111,11,111);

commit;

Given the hierarchical / tree nature of this data, it is well suited to retrieval using nested types available with Oracle. Well, for that matter all data in a relational database is pretty much well suited to this kind of retrieval. We just didn’t have easy ways to get data out of Oracle in nested formats, till recently. A simple example will clarify.

select dept.*
      ,cursor(
              select emp.*
                    ,cursor(
                            select timesheet.*
                            from timesheet
                            where timesheet.eno = emp.eno
                           ) timesheet_list
              from emp
              where emp.dno = dept.dno
             ) emp_list
      ,cursor(
              select project.*
                    ,cursor(
                            select workassignment.*
                            from workassignment
                            where workassignment.pno = project.pno
                           ) workassignment_list
              from project
              where project.dno = dept.dno
             ) project_list
from dept
/

This query gets a Department row and all its nested data. Pay particular attention to the relationships in the data, noting that it forms your typical downward oriented tree. Just like a family tree, with the root at the top, and branches opening and expanding as you go down. Just like a family tree structure, you can create any nested structure you want. Nesting of data via Oracle language constructs has become very easy.

Since our data can be nested to multiple levels, and in this example it is, this query shows how to nest multiple levels deep. Read up on cursor expressions if you are not familiar with them. Basically they are sets of rows correlated to their parent row, retrieved as a column of the parent row with a type of “CURSOR”.

Viewed through SQLPLUS the result of this query is ugly, but this method of retrieval is quite useful, mainly due to its ease of coding, and its nature of being able to query child data as a nested set of rows tied to a parent row. Each row contains, in a nested fashion, all data under it. Here we retrieve one department row, along with its employee and project rows, each of which also contain their nested data of timesheet rows and workassignment rows respectively. If you are not following this, draw a simple data model of the tables based on the obvious FKs. When we run the query on our sample data we get this. Examine the data being dumped, so you understand the nature of the nesting.

       DNO DNAME                          EMP_LIST             PROJECT_LIST
---------- ------------------------------ -------------------- --------------------
         1 D1                             CURSOR STATEMENT : 3 CURSOR STATEMENT : 4

CURSOR STATEMENT : 3

       ENO        DNO ENAME                          TIMESHEET_LIST
---------- ---------- ------------------------------ --------------------
        11          1 Kevin                          CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

MEDATE                     ENO      HOURS
------------------- ---------- ----------
2006-10-31 00:00:00         11        160

1 row selected.


1 row selected.


CURSOR STATEMENT : 4

       PNO PNAME                                 DNO WORKASSIGNMENT_LIST
---------- ------------------------------ ---------- --------------------
       111 P1                                      1 CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

       WNO        ENO        PNO
---------- ---------- ----------
      1111         11        111

1 row selected.


1 row selected.


1 row selected.

SQL>

Using Oracle DBMS_XMLGEN package (or one of several other packages that does similar work), we can pass this query as a string to one of the package’s functions and get back results in XML format. It is a piece of cake.

select dbms_xmlgen.getxml(
 'select dept.*'
||'      ,cursor('
||'              select emp.*'
||'                    ,cursor('
||'                            select timesheet.*'
||'                            from timesheet'
||'                            where timesheet.eno = emp.eno'
||'                           ) timesheet_list'
||'              from emp'
||'              where emp.dno = dept.dno'
||'             ) emp_list'
||'      ,cursor('
||'              select project.*'
||'                    ,cursor('
||'                            select workassignment.*'
||'                            from workassignment'
||'                            where workassignment.pno = project.pno'
||'                           ) workassignment_list'
||'              from project'
||'              where project.dno = dept.dno'
||'             ) project_list'
||' from dept'
                            ) thexml
from dual
/

THEXML
---------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DNO>1</DNO>
  <DNAME>D1</DNAME>
  <EMP_LIST>
   <EMP_LIST_ROW>
    <ENO>11</ENO>
    <DNO>1</DNO>
    <ENAME>Kevin</ENAME>
    <TIMESHEET_LIST>
     <TIMESHEET_LIST_ROW>
      <MEDATE>2006-10-31 00:00:00</MEDATE>
      <ENO>11</ENO>
      <HOURS>160</HOURS>
     </TIMESHEET_LIST_ROW>
    </TIMESHEET_LIST>
   </EMP_LIST_ROW>
  </EMP_LIST>
  <PROJECT_LIST>
   <PROJECT_LIST_ROW>
    <PNO>111</PNO>
    <PNAME>P1</PNAME>
    <DNO>1</DNO>
    <WORKASSIGNMENT_LIST>
     <WORKASSIGNMENT_LIST_ROW>
      <WNO>1111</WNO>
      <ENO>11</ENO>
      <PNO>111</PNO>
     </WORKASSIGNMENT_LIST_ROW>
    </WORKASSIGNMENT_LIST>
   </PROJECT_LIST_ROW>
  </PROJECT_LIST>
 </ROW>
</ROWSET>


1 row selected.

SQL>

At this point we have done the following: constructed a query of arbitrary complexity using the very convenient cursor expression syntax, and instructed Oracle to give us back XML formatted data. You can pretty much pass in any valid query and get back formatted XML. Nice. The cursor expressions make it easy to get whatever level of nested data you want.

One important note to make, Oracle formats XML it produces into what it calls Canonical form. Basically that means it follows a specific set of rules with respect to mapping XML tags to column names, table names, and enclosing tags. Notice the data stream starts with ROWSET implying that the XML will contain 0,1, or many rows. Notice next, that the XML stream has a base rowtype called ROW. Notice also that there is an EMP_LIST rowset that contains on or more EMP_LIST_ROW data constructs. This of course is our cursor mapping. Oracle took the cursor element name we used and constructed the EMP_LIST tag and then appended _ROW to it to construct the ROW tag for EMP_LIST. And so on it goes for each of our nested cursor expressions, names being happily generated along the way using in a consistent manner.

This is all configurable, as well as many other nit picky options of the DBMS_XMLGEN package. The more you want to get into it, the more you can control the formatting of the resulting XML. In the end you will likely learn what you have to, to get your job done.

So getting XML out of the database is wicked easy. How easy is it to put it back? Well, also pretty easy. First, the input XML must be in Canonical form. No problem if the XML is the output of one of Oracle’s packaged procedures, but if not, it must be converted into the proper form. Hmm… maybe we will need to know XSLT after all. It might pay to make friends with a couple of java developers who know XML style sheets.

In any event, we are not going to discuss XSLT transformation and how to do them. Go read that somewhere else. We will assume we have it already. Given XML in Canonical form, what we ideally would want is to do a simple “INSERT INTO X, THIS XML STUFF” and have the database do the work for us. Well, with a little effort, we can get there.

First, we need something into which we can insert a row. Since the SQL that maps to our XML data is complex, it makes sense to create a view based on the original query. That should give us a database rowsource that maps directly to the structure of our XML data. Only one problem, we can’t create a view from SQL containing a cursor expression (at least not yet). But we can get around that by using Objects. We map each cursor expression in our SQL statement to an Oracle collection type. Then we can construct an equivalent query using the object collections instead of cursor expressions. Here it is:

Our objects are mapped to the items found in our query so we need:

Timesheet object
Timesheet collection
Workassignment object
Workassignment collection
Emp object (which contains a timesheet collection)
Emp collection
Project object (which contains a workassignment collection)
Project collection

Notice that we have four cursor expressions in our original query, so we will need four collection types. Each collection type is based on a object type that maps to one of the cursor expressions in terms of column name, column datatype, and column order. Since we selected everything from our tables, we can pretty much just map our object definitions to our table definitions and add in the nested collections as necessary.

create or replace type o_timesheet is object (medate date,eno number, hours number)
/
create or replace type c_timesheet is table of o_timesheet
/

create or replace type o_workassignment is object (wno number,eno number, pno number)
/
create or replace type c_workassignment is table of o_workassignment
/

create or replace type o_project is object (pno number,pname varchar2(30),dno number
                                           ,workassignment_list c_workassignment
                                           )
/
create or replace type c_project is table of o_project
/

create or replace type o_emp is object (eno number,dno number,ename varchar2(30)
                                       ,timesheet_list c_timesheet
                                       )
/
create or replace type c_emp is table of o_emp
/

With object collection types defined, we can change our query slightly to use syntax based on collections rather than cursor expressions. We are going to substitute the phrase CAST(MULTISET(SELECT * FROM (, in place of CURSOR(, making sure we put in all the right close parens as needed.

select dept.*
      ,cast(multiset(select * from (
              select emp.*
                    ,cast(multiset(select * from (
                            select timesheet.*
                            from timesheet
                            where timesheet.eno = emp.eno
                                                 )
                                  ) as c_timesheet
                         ) timesheet_list
              from emp
              where emp.dno = dept.dno
                                   )
                   ) as c_emp
           ) emp_list
      ,cast(multiset(select * from (
              select project.*
                    ,cast(multiset(select * from (
                            select workassignment.*
                            from workassignment
                            where workassignment.pno = project.pno
                                                 )
                                  ) as c_workassignment
                         ) workassignment_list
              from project
              where project.dno = dept.dno
                                   )
                   ) as c_project
           ) project_list
from dept
/

The output of the query is clearly different from the cursor expression. But it is all the same data in all the same basic nesting pattern.

       DNO DNAME
---------- ------------------------------
EMP_LIST(ENO, DNO, ENAME, TIMESHEET_LIST(MEDATE, ENO, HOURS))
----------------------------------------------------------------------------------------
PROJECT_LIST(PNO, PNAME, DNO, WORKASSIGNMENT_LIST(WNO, ENO, PNO))
----------------------------------------------------------------------------------------
         1 D1
C_EMP(O_EMP(11, 1, 'Kevin', C_TIMESHEET(O_TIMESHEET('2006-10-31 00:00:00', 11, 160))))
C_PROJECT(O_PROJECT(111, 'P1', 1, C_WORKASSIGNMENT(O_WORKASSIGNMENT(1111, 11, 111))))


1 row selected.

If we pass this modified query to DBMS_XMLGEN, we get the following output.

THEXML
------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DNO>1</DNO>
  <DNAME>D1</DNAME>
  <EMP_LIST>
   <O_EMP>
    <ENO>11</ENO>
    <DNO>1</DNO>
    <ENAME>Kevin</ENAME>
    <TIMESHEET_LIST>
     <O_TIMESHEET>
      <MEDATE>2006-10-31 00:00:00</MEDATE>
      <ENO>11</ENO>
      <HOURS>160</HOURS>
     </O_TIMESHEET>
    </TIMESHEET_LIST>
   </O_EMP>
  </EMP_LIST>
  <PROJECT_LIST>
   <O_PROJECT>
    <PNO>111</PNO>
    <PNAME>P1</PNAME>
    <DNO>1</DNO>
    <WORKASSIGNMENT_LIST>
     <O_WORKASSIGNMENT>
      <WNO>1111</WNO>
      <ENO>11</ENO>
      <PNO>111</PNO>
     </O_WORKASSIGNMENT>
    </WORKASSIGNMENT_LIST>
   </O_PROJECT>
  </PROJECT_LIST>
 </ROW>
</ROWSET>


1 row selected.

SQL>

It does not look exactly like the original XML but we don’t really care. It contains the same data, and the generated XML Is in Canonical form. Now we can create our view based on the modified query.

create or replace
view dept_xml_vw
as
select dept.*
      ,cast(multiset(select * from (
              select emp.*
                    ,cast(multiset(select * from (
                            select timesheet.*
                            from timesheet
                            where timesheet.eno = emp.eno
                                                 )
                                  ) as c_timesheet
                         ) timesheet_list
              from emp
              where emp.dno = dept.dno
                                   )
                   ) as c_emp
           ) emp_list
      ,cast(multiset(select * from (
              select project.*
                    ,cast(multiset(select * from (
                            select workassignment.*
                            from workassignment
                            where workassignment.pno = project.pno
                                                 )
                                  ) as c_workassignment
                         ) workassignment_list
              from project
              where project.dno = dept.dno
                                   )
                   ) as c_project
           ) project_list
from dept
/

SQL> desc dept_xml_vw
 Name                                Null?    Type
 ----------------------------------- -------- --------------
 DNO                                          NUMBER
 DNAME                                        VARCHAR2(30)
 EMP_LIST                                     C_EMP
 PROJECT_LIST                                 C_PROJECT

SQL>

With this view we can add an instead of trigger to push data through it. This trigger implements a simple insert. You can put whatever logic you need in the trigger. Production logic for updating this view would be a bit more sophisticated, treating an insert as an update mechanism similar to “make the data look like this” rather than “insert a new row”. But for our learning purposes, this trigger will do fine.

Notice how the trigger is just walking the nested collections. This is how we unpack the data so to speak. Once we unpack the data, we can use it. In this case we do inserts with it, into all the tables we have data for.

create or replace
trigger ioiud_dept_xml_vw
instead of insert or update or delete on dept_xml_vw
for each row
begin
   if inserting then
      insert into dept values (:new.dno,:new.dname);
      for i in 1..nvl(:new.emp_list.count,0) loop
         insert into emp values (:new.emp_list(i).eno
                                ,:new.emp_list(i).dno
                                ,:new.emp_list(i).ename);
         for j in 1..nvl(:new.emp_list(i).timesheet_list.count,0) loop
            insert into timesheet values (:new.emp_list(i).timesheet_list(j).medate
                                         ,:new.emp_list(i).timesheet_list(j).eno
                                         ,:new.emp_list(i).timesheet_list(j).hours
                                         );
         end loop;
      end loop;
      for i in 1..nvl(:new.project_list.count,0) loop
         insert into project values (:new.project_list(i).pno
                                    ,:new.project_list(i).pname
                                    ,:new.project_list(i).dno);
         for j in 1..nvl(:new.project_list(i).workassignment_list.count,0) loop
            insert into workassignment values (:new.project_list(i).workassignment_list(j).wno
                                              ,:new.project_list(i).workassignment_list(j).eno
                                              ,:new.project_list(i).workassignment_list(j).pno);
         end loop;
      end loop;
   elsif updating then null;
   else null;
   end if;
end;
/
show errors

The trigger loops through the collections one row at a time. Since the data is neatly nested, our trigger is constructed of a series of correspondingly neatly nested loops.

Our XML extract is now a simple select off the view.

select dbms_xmlgen.getxml('select * from dept_xml_vw') from dual
/

With our view created and an instead of trigger in place we are ready to insert some XML data into the view. To do this we are going to use the package DBMS_XMLSAVE. To use this we have to write some PL/SQL code but if we were doing lots of XML sharing then we would make a generic routine for this.

Here is an anonymous block that is going to select rows off our view and then insert them back to make a second copy of all the data. The thing to get from this isn’t that this is a stupid piece of code cause who would make duplicate rows this way, and who would want to dup them to begin with. Rather, the thing to get from this simple exercise is, the simple point that there is a way to put xml data into the database via an insert into an object view.

alter session set nls_date_format = 'rrrr-mm-dd hh24:mi:ss'
/

declare
   clobv clob;
   rowcountv number;
   workhandlev dbms_xmlsave.ctxType;
   errnov number;
   errmv varchar2(4000);
begin null;
   clobv := dbms_xmlgen.getxml('select * from dept_xml_vw');
   workhandlev := dbms_xmlsave.newcontext('dept_xml_vw');
   dbms_xmlsave.propagateOriginalException(workhandlev,true);
   dbms_xmlsave.setDateFormat(workhandlev,'yyyy-MM-dd hh:mm:ss');
   rowcountv := dbms_xmlsave.insertxml(workhandlev,clobv);
   dbms_xmlsave.closecontext(workhandlev);
exception when others then
   dbms_xmlsave.getExceptionContent( workhandlev
                                   , errnov
                                   , errmv);
   raise_application_error(-20999,errmv);
end;
/

I’ll let you guys do some Google’ing to find out more about DBMS_XMLSAVE (and DBMS_XMLSTORE), but here are some points to note:

1) We insert XML housed in a clob, into the database. We can get this clob from anywhere; we just used a shortcut in this example so that I didn’t have to do lots of typing to build some data.
2) We identify the object to work with when doing things this way. Hence we identify our view through the newcontext call above.
3) ** We set a java date format. The package DBMS_XMLSAVE, actually maps to an underlying java routine automatically loaded when Oracle was installed. As such we have to tell java what date format it should expect dates to be in and when we do we have to use Java date formats not Oracle date formats. Additionally the XML we are inserting must format dates the way java is expecting based on what we told it. So please note that because we are getting our data from Oracle in the first place, we set the corresponding date format in our Oracle session before we run our routine. If you don’t like the date format fiddling, then one alternative would be to convert dates to characters strings in your view and use a corresponding string item in your object types, and then convert them back to dates in your trigger.

In the end, if we get to doing a lot of this, we would pretty quick create a helper package to mask this anonymous block. The call would end up looking something like this

XML_HELPERS.INSERT_CLOB(someclob, targettable, xslstylesheet);

I added a style sheet option because there are some Oracle packages that will accept a style sheet when loading XML data to the database thus making easy to apply an XSLT transform on the fly (assuming you have a developer friend to create one for you). This is handy when you are working with XML formats that you didn’t create (which is almost always) and are thus not in Canonical form.

Let us recap our steps

1) We make a decision on how to format dates: a) in the code, b) in the environment.
2) We create a query using cursor expressions that get data in the XML format of interest.
3) We create object collections for each cursor expression in our query.
4) We modify our query to use collections instead of cursor expressions.
5) We create a view using this modified query.
6) We create an INSTEAD-OF-TRIGGER for the view to handle DML on it.
7) We insert clobs into the view using DBMS_XMLSAVE or more likely a wrapper function of our own that calls DBMS_XMLSAVE (or DBMS_XMLSTORE).

Remember also, DBMS_XMLSAVE works on the assumption that tag names in the XML match column names on the tables. Same for collection names etc. So you don’t have too much play with Oracle’s Canonical format requirement. Thus you will likely have to learn some amount of XSLT (sorry), or make friends with someone who already knows it.

In the end, this is a pretty easy way to get data in and out of an Oracle database. It uses the techniques and methods we already know, in ways we already understand. We can get any data we want from the database, and Canonical form aside, we can create a view/trigger combination that will let us load any data into the database. Especially attractive to this method is that we are not required to alter our already existing tables in order to get our database to be XML capable.

Looking at the original query which used cursor expressions, we should note that the output from this query can also be loaded through the same object view/trigger even though the object view itself is based on a different query. So you can still use the original query to generate data if you want, though if you have the view one would wonder why.

declare
   clobv clob;
   rowcountv number;
   workhandlev dbms_xmlsave.ctxType;
   errnov number;
   errmv varchar2(4000);
begin null;
--   clobv := dbms_xmlgen.getxml('select * from dept_xml_vw');
select dbms_xmlgen.getxml(
 'select dept.*'
||'      ,cursor('
||'              select emp.*'
||'                    ,cursor('
||'                            select timesheet.*'
||'                            from timesheet'
||'                            where timesheet.eno = emp.eno'
||'                           ) timesheet_list'
||'              from emp'
||'              where emp.dno = dept.dno'
||'             ) emp_list'
||'      ,cursor('
||'              select project.*'
||'                    ,cursor('
||'                            select workassignment.*'
||'                            from workassignment'
||'                            where workassignment.pno = project.pno'
||'                           ) workassignment_list'
||'              from project'
||'              where project.dno = dept.dno'
||'             ) project_list'
||' from dept'
                            ) thexml
into clobv
from dual
;
   workhandlev := dbms_xmlsave.newcontext('dept_xml_vw');
   dbms_xmlsave.propagateOriginalException(workhandlev,true);
   dbms_xmlsave.setDateFormat(workhandlev,'yyyy-MM-dd hh:mm:ss');
   rowcountv := dbms_xmlsave.insertxml(workhandlev,clobv);
   dbms_xmlsave.closecontext(workhandlev);
exception when others then
   dbms_xmlsave.getExceptionContent( workhandlev
                                   , errnov
                                   , errmv);
   raise_application_error(-20999,errmv);
end;
/

If all this seems like a lot of work, let me suggest that it isn’t, once you consider how much work you have ahead of you if you opt instead to learn XPATH and XQUERY and XSQL etc. etc. These techniques are well understood Oracle constructs, and this method of XML movement is a repeatable process. And I like the fact that it fits neatly into an existing database, no table mods and no new tables required.

Enjoy, hope it helps. Kevin