Storing XML in Relational Databases

Introduction

There are various ways to solve the problem of effective, automatic conversion of XML data into and out of relational databases. Database vendors such as IBM, Microsoft, Oracle, and Sybase have developed tools to assist in converting XML documents into relational tables. The various solutions are as follows.

• Oracle XML SQL Utility models XML document elements as a collection of nested tables. Enclosed elements are modeled by employing the Oracle Object datatype. The "SQL-to-XML" conversion constructs an XML document by using a one-to-one association between a table, referenced by Object datatype, and a nested element. "XML-to-SQL" might require either data model amending (converting it from relational into object-relational) or restructuring the original XML document.

• IBM DB2 XML Extender allows storing XML documents either as BLOB-like objects or as decomposed into a set of tables. The latter transformation, known as XML collection, is defined in XML 1.0 syntax.

• Microsoft approaches the problem by extending SQL-92 and by introducing OPENXML row set.

• Sybase Adaptive Server introduces the ResultSetXml Java class as a base for processing XML documents in both directions.

In this article, we will explore in detail these vendor's solutions. Thereafter, we will attempt to answer the questions:

• Can we restructure the and simplify the problem?
• What is the right approach in a heterogeneous database environment?

I'll use the following vocabulary as an example.

<!-- Primitive Types -->

<!ELEMENT CURRENCY1   (#PCDATA)>
<!ATTLIST CURRENCY1    e-dtype    NMTOKEN   #FIXED  "string"
                        e-dsize    NMTOKEN   #FIXED  "3">

<!ELEMENT CURRENCY2   (#PCDATA)>
<!ATTLIST CURRENCY2    e-dtype    NMTOKEN   #FIXED  "string"
                        e-dsize    NMTOKEN   #FIXED  "3">

<!ELEMENT AMOUNT      (#PCDATA)>
<!ATTLIST AMOUNT       e-dtype    NMTOKEN   #FIXED  "decimal">

<!ELEMENT SETTLEMENT  (#PCDATA)>
<!ATTLIST SETTLEMENT   e-dtype    NMTOKEN   #FIXED  "date">

<!ELEMENT BANKCODE    (#PCDATA)>
<!ATTLIST BANKCODE     e-dtype    NMTOKEN   #FIXED  "string">

<!ELEMENT BANKACCT    (#PCDATA)>
<!ATTLIST BANKACCT     e-dtype    NMTOKEN   #FIXED  "string">

<!-- Derived Types -->

<!ELEMENT ACCOUNT  (BANKCODE, BANKACCT)>

<!ELEMENT FXTRADE  (CURRENCY1, CURRENCY2, AMOUNT, SETTLEMENT, ACCOUNT)>

Oracle XML-SQL Utility (XSU)

SQL to XML Mapping

Oracle translates the chain of object references from the database into the hierarchical structure of XML elements. In an object-relational database, the field ACCOUNT in the table FXTRADE is modeled as an object reference of type AccountType:

CREATE TABLE FXTRADE 
{
     CURRENCY1      CHAR (3),
     CURRENCY2      CHAR (3),
     AMOUNT         NUMERIC (18,2),
     SETTLEMENT     DATE,
     ACCOUNT        AccountType // object reference
}

CREATE TYPE AccountType as OBJECT
{  
     BANKCODE       VARCHAR (100),
     BANKACCT       VARCHAR (100) 
}

A corresponding XML document generated from the given object-relational model (using ";SELECT * FROM FXTRADE") looks like

<?xml version="1.0"?>
<ROWSET>
     <ROW num="1"> 
        <CURRENCY1>GBP</CURRENCY1>
        <CURRENCY2>JPY</CURRENCY2>
        <AMOUNT>10000</AMOUNT>
        <SETTLEMENT>20010325</SETTLEMENT>
        <ACCOUNT>
   <BANKCODE>812</BANKCODE>
   <BANKACCT>00365888</BANKACCT>
        </ACCOUNT>
    </ROW>
    <!-- additional rows ... -->
 </ROWSET>

Extracting XML from the database

The example below is taken from Oracle's XSU documentation with appropriate substitution of SQL statements and using Oracle's pure Java JDBC thin driver.

First, an instance of OracleXMLQuery is created; second, a query is executed, and the result is represented in the form of an XML document as above. Similarly, an XML document can be extracted in the form of DOM; in this case qry.getXMLDOM() would be invoked instead of getXMLString().

import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;

// class to test XML document generation as String
class testXMLSQL {

   public static void main(String[] args)
   {
     try {
      // Create the connection
      Connection conn  = getConnection("scott","tiger");

      // Create the query class
      OracleXMLQuery qry = new OracleXMLQuery(conn,
         "SELECT  * FROM FXTRADE");

      // Get the XML string
      String str = qry.getXMLString();

      // Print the XML output
      System.out.println("The XML output is:\n"+str);

      // Always close the query to get rid of any resources..
      qry.close();
     } catch(SQLException e) {
      System.out.println(e.toString());
     }
   }

   // Get the connection given the user name and password.!
   private static Connection getConnection(String username,
        String password)
        throws SQLException
   {
      // register the JDBC driver..
       DriverManager.registerDriver(new 
          oracle.jdbc.driver.OracleDriver());

      // Create the connection using the OCI8 driver
       Connection conn =
        DriverManager.getConnection(
           "jdbc:oracle:thin:@dlsun489:1521:ORCL",
           username,password);

      return conn;
   }
}

Storing XML in the database

In this example OracleXMLSave is employed to store our XML document in an object-relational model; the insertXML method performs the actual insertion of the data.

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testXMLInsert
{
   public static void main(String args[])
             throws SQLException
  {
    Connection conn = getConnection("scott","tiger");
    OracleXMLSave sav = new OracleXMLSave(conn, "scott. FXTRADE");
  // Assume that the user passes in this document as 0-arg
      sav.insertXML(args[0]);
      sav.close();
   }

  ...
}

That's fine if the XML and object-relational model in the database are synchronized, but what if they aren't? You have two options in that case.

Involved in similar projects, or have comments, clarifications or questions about this article? Leave feedback using our forum.

Post your comments

1. Adjust the object-relational model -- a modifiable object-relational view can be constructed to accomplish multi-table modifications; or,
2. alternatively, the XML document can be decomposed into a collection of "flat" subdocuments, using XSLT.

XSU does not permit storage of attribute values; it's recommended that you transform attributes into elements.

Summary of Oracle XSU

An XML to SQL mapping is modeled by an object-relational model construction rule as follows: each nested XML element is mapped onto an object reference of appropriate type. Mapping rules are implicitly embedded in the database model.

The Java API consists of the classes OracleXMLQuery and OracleXMLSave.