As Published In September/October 2005

XML to Relational: Bridging the Gap
By Sean Dillon

Storing XML in traditional relational storage

XML is a great way to share data between systems, organizations, and disparate technologies, but once you've received that XML, what do you do with it? In this column, I review how you can store the contents of your XML documents in relational tables.

Introducing DBMS_XMLSTORE

The DBMS_XMLSTORE PL/SQL package was introduced in Oracle Database 10g Release 1. This package performs DML operations on relational or object tables inside the database, based on the contents of an XML document.

Note that before Oracle Database 10g, this functionality existed in another PL/SQL package, called DBMS_XMLSAVE.

Canonical XML

In order to use DBMS_XMLSTORE, you need to format your XML documents in Oracle's "canonical XML format." This format is very straightforward; each element in the XML document will map to a column, and the element name will be the column name. Elements that make up a row in the XML document are placed under a <ROW> element, and all of the <ROW> elements are placed inside a <ROWSET> element. If you take two rows from the standard EMP table and represent them in Oracle canonical format, you get the following XML document:

<ROWSET>
  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>20-FEB-81</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>22-FEB-81</HIREDATE>
    <SAL>1250</SAL>
    <COMM>500</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
</ROWSET>

Employing the DBMS_XMLSTORE Package

You can perform the basic INSERT, UPDATE, and DELETE operations with DBMS_XMLSTORE. The DBMS_XMLSTORE package also offers a variety of procedures for customizing these operations. Let's walk through and discuss how to use each of the operations available.

Inserts. DBMS_XMLSTORE.INSERTXML is the function used to insert rows into the database. In this first example, you create a new table that resembles the EMP table called SALES_EMP:

SQL> create table sales_emp
  2  as select * from emp where 1=0
  3  /
Table created.

Now you create an XML document that contains all of the employees from the SALES department and insert these employees into the new SALES_EMP table. Lines 7 through 18 of Listing 1 create the XML document from the EMP and DEPT tables.

Code Listing 1: INSERT using DBMS_XMLSTORE

SQL> declare
  2    l_sales_emp xmltype;
  3    l_ctx         dbms_xmlstore.ctxtype;
  4    l_rows       pls_integer;
  5  begin
  6    -- get all the sales employees into an xml document
  7    select xmlelement("ROWSET",
  8             xmlagg(
  9               xmlelement("ROW",
 10                 xmlforest(e.empno,e.ename,e.job,e.mgr,
 11                           e.hiredate,e.sal,e.comm,e.deptno)
 12               )
 13             )
 14           )
 15      into l_sales_emp
 16      from emp e, dept d
 17     where d.deptno = e.deptno
 18       and d.dname='SALES';
 19  
 20     -- setup our dbms_xmlstore context
 21     l_ctx := dbms_xmlstore.newcontext('SALES_EMP');
 22     l_rows := dbms_xmlstore.insertxml( 
 23               L_ctx, l_sales_emp.getClobVal());
 24 
 25   -- how many rows were inserted?
 26   dbms_output.put_line(l_rows || ' rows inserted into SALES_EMP.');
 27 
 28   -- clean up
 29   dbms_xmlstore.closecontext(l_ctx);
 30 end;
 31 /
6 rows inserted into SALES_EMP.

PL/SQL procedure successfully completed.

In the anonymous PL/SQL block in Listing 1, you can see calls to the DBMS_XMLSTORE package to accomplish this insert. Line 21 uses NEWCONTEXT() to create a new context using the new table name (SALES_EMP). Line 22 calls the INSERTXML function, which by default inserts data into every column of the table and returns the number of rows inserted. Finally, output that information using the DBMS_OUTPUT package (line 26) and clean up the context in the DBMS_XMLSTORE package using the CLOSECONTEXT() procedure (line 29).

The following query shows the six rows inserted in SALES_EMP by Listing 1:

SQL> select empno, ename, job, sal
  2    from sales_emp;

EMPNO	      ENAME	 JOB          SAL
---------     --------   ----------   --------
7499	      ALLEN	 SALESMAN     1600
7521	      WARD	 SALESMAN     1250
7654	      MARTIN	 SALESMAN     1250
7698	      BLAKE	 MANAGER      2850
7844	      TURNER	 SALESMAN     1500
7900	      JAMES	 CLERK	       950

Updates. Using this example, update a couple of the SALES_EMP records to indicate those employees who have received promotions and raises. Here is the XML that will be used to perform the update:

<ROWSET>
  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7698</MGR>
    <SAL>2600</SAL>
  </ROW>
  <ROW>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7698</MGR>
    <SAL>2250</SAL>
  </ROW>
</ROWSET>

When performing updates using DBMS_XMLSTORE, you need to use other procedures in the package to help the database understand what columns you are updating and what you are using for the update key(s). Listing 2 shows the process for updating the two rows listed above (ALLEN and WARD).

Code Listing 2: UPDATE using DMBS_XMLSTORE

SQL> declare
  2    l_sales_emp xmltype;
  3    l_ctx          dbms_xmlstore.ctxtype;
  4    l_rows        pls_integer;
  5  begin
  6    -- simulate the updates to make
  7    l_sales_emp := xmltype('<?xml version="1.0"?>
  8  <ROWSET>
  9    <ROW>
 10      <EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>MANAGER</JOB>
<MGR>7698</MGR><SAL>2600</SAL>
 11    </ROW>
 12    <ROW>
 13      <EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>MANAGER</JOB>
<MGR>7698</MGR><SAL>2250</SAL>
 14    </ROW>
 15  </ROWSET>');
 16  
 17    -- setup our dbms_xmlstore context
 18    l_ctx := dbms_xmlstore.newcontext('SALES_EMP');
 19  
 20    -- setup the columns to be updated
 21    dbms_xmlstore.clearupdatecolumnlist(l_ctx);
 22    dbms_xmlstore.setupdatecolumn(l_ctx, 'ENAME');
 23    dbms_xmlstore.setupdatecolumn(l_ctx, 'JOB');
 24    dbms_xmlstore.setupdatecolumn(l_ctx, 'MGR');
 25    dbms_xmlstore.setupdatecolumn(l_ctx, 'SAL');
 26  
 27    -- setup the key columns to update by
 28    dbms_xmlstore.setkeycolumn(l_ctx, 'EMPNO');
 29  
 30    -- perform the update
 31    l_rows := dbms_xmlstore.updatexml(l_ctx, l_sales_emp.getClobVal());
 32  
 33    -- how many rows were updated?
 34    dbms_output.put_line(l_rows || ' rows updated in SALES_EMP.');
 35  
 36    -- clean up
 37    dbms_xmlstore.closecontext(l_ctx);
 38  end;
 39  /
2 rows updated in SALES_EMP.

PL/SQL procedure successfully completed.

In line 21 of Listing 2, the first procedure, CLEARUPDATECOLUMNLIST, makes sure no columns are already identified in the package to be used as key columns in the update. In lines 22 through 25, SETUPDATECOLUMN indicates which columns in the table will be updated. There's no reason to have to update every column, so this function gives you granular control of the columns that get modified during the operation. Here you are only updating the

Next Steps READ more on DBMS_XMLSTORE Oracle XML DB Developer's Guide

ENAME, JOB, MGR, and SAL columns. Next, tell the database which column(s) to use for the WHERE clause using the SETKEYCOLUMN procedure in line 28, and in this case use EMPNO as the key column. Finally, use the UPDATEXML function in line 31 to execute the UPDATE command and return the number of rows updated.

After you update the table, you can see that Allen and Ward have been promoted to managers and both employees have received their raises:

SQL> select empno, ename, job, sal
  2    from sales_emp;

EMPNO	      ENAME	 JOB          SAL
---------     --------   ----------   --------
7499	      ALLEN	 MANAGER      2600
7521	      WARD	 MANAGER      2250
7654	      MARTIN	 SALESMAN     1250
7698	      BLAKE	 MANAGER      2850
7844	      TURNER	 SALESMAN     1500
7900	      JAMES	 CLERK	       950

Deletes. After a careful review by upper management, it has become apparent that WARD is clearly the only manager needed, so the next operation will be to delete the unnecessary managers, ALLEN and BLAKE. Let's take a look at the calls to DBMS_XMLSTORE used to accomplish this task in Listing 3.

Code Listing 3: DELETE

SQL> declare
  2    l_sales_emp xmltype;
  3    l_ctx        dbms_xmlstore.ctxtype;
  4    l_rows       pls_integer;
  5  begin
  6    -- simulate the updates to make
  7    l_sales_emp := xmltype('<?xml version="1.0"?>
  8  <ROWSET>
  9    <ROW>
 10      <EMPNO>7499</EMPNO>
 11    </ROW>
 12    <ROW>
 13      <EMPNO>7698</EMPNO>
 14    </ROW>
 15  </ROWSET>');
 16  
 17    -- setup our dbms_xmlstore context
 18    l_ctx := dbms_xmlstore.newcontext('SALES_EMP');
 19  
 20    -- setup the key column to delete by
 21    dbms_xmlstore.setkeycolumn(l_ctx, 'EMPNO');
 22  
 23    -- perform the delete
 24    l_rows := dbms_xmlstore.deletexml(l_ctx, l_sales_emp.getClobVal());
 25  
 26    -- how many rows were deleted?
 27    dbms_output.put_line(l_rows || ' rows deleted fm SALES_EMP.');
 28  
 29    -- clean up
 30    dbms_xmlstore.closecontext(l_ctx);
 31  end;
 32  /
2 rows deleted fm SALES_EMP.

PL/SQL procedure successfully completed.

And after running the block in Listing 3, you can see the following results:

SQL > select empno, ename, job 
  2    from sales_emp;

EMPNO	      ENAME	 JOB          
---------     --------   ----------       
7521	      WARD	 MANAGER     
7654	      MARTIN	 SALESMAN          
7844	      TURNER	 SALESMAN     
7900	      JAMES	 CLERK       

Storing XML Data Relationally

DBMS_XMLSTORE offers one way to take the content from XML documents and store it in (and remove it from) relational tables. (If you are not yet using Oracle Database 10g, you might try DBMS_XMLSAVE to do the same.)