|
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)>
|
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.
-
Adjust the object-relational model -- a modifiable object-relational view can
be constructed to accomplish multi-table modifications; or,
-
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.
|
