Storing XML in RELATIONAL DATABASESESS (1)
From http://www.xml.com
Translating by bjcfr
Email: bjcfr@sohu.com
introduction
There are many ways to solve the problem of effective mutual conversion between XML data and relational database data, database system suppliers such as IBM, Microsoft, Oracle, Sybase, etc., have developed some tools to help convert XML documents into relational data sheets. The following is a few different solutions:
1. Oracle XML SQL Utility simulates many elements of the XML document into some nested tables, and the corresponding elements use the Oracle Objict data type to simulate. "SQL-TO-XML" conversion is created by a relationship between the Reference By Object DataType and the nested element to create an XML document. "XML-TO-SQL" conversion requires modification of the data model (converting it from the relationship to object type) or rebuilding the original XML document.
2. IBM DB2 XML Extensions Allows the XML document as an object similar to the BLOB (binary large-to-object name) or decomposes them into a series of tables for storage. The latter's conversion is defined in the syntax of XML 1.0.
3. Microsoft solves this problem by extending SQL-92 and introducing OpenXML rows.
4. Sysbase Adaptive uses Server to introduce Java class ResultSetXML as the basis for this bidirectional conversion process.
In this article, we analyze the details of these supplier solutions first, then we try to add the following questions:
• Can we re-construct a problem?
• What is the correct solution in the heterogeneous database environment?
The following DTD is the example of this article:
E-Dsize NMToken #fixed "3">
E-Dsize NMToken #fixed "3">
Oracle XML-SQL Utility (XSU)
Map of SQL to XML
Oracle converts a range of objects to the hierarchy of the database into an XML element. Table FXTrade field account is used as analog to the type AccountTYPE object reference.
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)
}
The following is a corresponding XML document generated from a given object-relational model using statement "Select * from fxtrade":
Extract XML from the database
The following example is taken from the Oracle's XSU document, replacing the corresponding SQL statement and uses Oracle's pure Java JDBC thin driver.
First, create an instance of OracleXmlQuery, then execute a query, the result is the XML document mentioned above. Similarly, the XML document can also be taken from the DOM, in which case qry.getxmldom () 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.Orcledriver ());
// 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, use OracleXmlsave to store our XML document as an object-relational model INSERTXML method for insertion 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 ();
}
...
}
If the relational object model in the XML and database is synchronized, if it is not synchronized? In this case, there is usually two options:
1. Adjust the relational object model, by creating a changeable relationship object view and completing the modification of the multi-table.
2. You can break the XML document into a series of simple sub-documents. We can use XSLT to complete this decomposition process.
The XSU does not allow storage attribute values, and it is recommended to convert the attribute to the element and then processed.
Oracle XSU Summary
An XML to SQL mapping can be simulated by a relational object model, and its creation rules are as follows:
Each nested XML element is mapped to an appropriate object reference. The mapping rules are already embedded in the database model.
The related Java API includes two classes of OracleXmlQuery and OracleXmlsave. (Not continued)
