As Published In
Oracle Magazine
September/October 2005

TECHNOLOGY: XML Exchange


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


Sean Dillon (sean.dillon@oracle.com) is a principal technologist at Oracle, where he has worked since 1996. The lead author of Beginning Oracle Programming (Apress, 2003), Dillon specializes in core database, XML, and Web services technologies.


Send us your comments