Original: Igor Dayenjune 20, 2001 Translation: Han 蝉 蝉 退 (MHSS) translator statement: The translator does not do any guarantee, the translation does not have any rights and does not afford any responsibility and obligation. Original: http://www.xml.com/pub/a/2001/06/2Abases.html
Introduction to resolve the problem of effective, automatic conversion and entry related databases, have various ways. Database vendors, such as IBM, Microsoft, Oracle, and Sybase have developed auxiliary tools in the XML to the database table. Various solutions are as follows.
Oracle XML SQL Utility models XML document elements into a set of nested tables. Modify the elements of the set by using the Oracle object data type. "SQL-TO-XML" Conversion uses an XML document using a table referenced by the object data type, and an association between nested elements. "XML-TO-SQL" may require improvements to the data model (from the relationship to the object-relationship) or recombine the initial XML document. IBM DB2 XML Extender allows storage of XML documents to BLOB objects or decomposition into a set of tables. The latter has the transformation called XML collection, defined in XML 1.0 syntax. Microsoft solves the problem by extending SQL-92 and intervening OpenXML rows. Sybase Adaptive Server intervenes the RESULTSETXML JAVA class as the basis for processing XML documents in both directions. In this article, we will discuss these manufacturers' solutions in detail. Since then, we will try to answer the following questions:
Can we adjust and simplify the problem? What is the correct way to the heterogeneous database environment? I will use the following terms as an example.
Oracle XML-SQL Utility (XSU) SQL to XML mapping Oracle converts the object reference chain from the database to the hierarchy of the XML document. In the object-relational database, the field account in the table fxtrade is modeled as an object reference for 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)} From a given object-relational model generates the corresponding XML document (using "Select * from fxtrade") as follows
gbp jpy 10000 20010325 < / Settlement> 812 0036588 extract XML from the database The following example is taken from the XSU document of Oracle, with an appropriate replacement of the SQL statement and uses Oracle's pure Java JDBC thin driver. First, an instance of OracleXmlQuery is established, and thereafter, an query is executed and the result is represented as the form of the XML document above. Similarly, the XML document to the DOM can be extracted; in this case, QRY.GETXMLDOM () can be called GetXmlString ().
Import oracle.jdbc.driver. *; import oracle.xml.sql.Query.OraclexmlQuery; import java.lang. *; import java.sql. *; // Test XML document Become a String class class testXMLSQL {public static void Main (String [] args) {try {// Established connection connection conn = getConnection ("scott", "tiger"); // Establishing query class OraclexmlQuery QRY = New OracleXMLQuery (conn, "select * from fxtrade"); / / Get XML string string string str = qry.getxmlstring (); // Print XML Output System.out.Println ("The XML Output IS: / N"; // Always close QRY to release all resources QRY. Close ();} catch (sqlexception e) {system.out.println (e.tostring ());}} // Get a given user name and password connection private stataint connection getConnection (String Username, String Password) Throws SQLEXCEPTION {// Register JDBC Driver DriverManager.RegisterDriver (new oracle.jdbc.driver.Oracledriver ()); // Establish an OCI8 driver connection Connecti On Conn = DriverManager.getConnection ("JDBC: Oracle: Thin: @ DLSUN489: 1521: ORCL", Username, Password); Return Conn;}} Storage XML Use OracleXmlsave in the database to store our XML documents to objects In the relationship model; insertxml method performs actual data insertion.
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 ();} ...} XML and objects in the database - the relationship model is synchronized, but if it is different? In this case you have two options. Adjustment Object-Relation Model - You can construct a modified object-relational view to complete multi-table modification; or, as an alternative, use XSLT, you can decompose the XML document into a "flat" sub-document. The XSU does not allow storage of attribute values; it suggests that you convert attributes into elements. Oracle XSU Summary The following object-relational model constructs rule modeling XML to SQL mapping: map each nested XML element to an object reference to the appropriate type. The mapping rules are embedded in the database model. The Java API consists of class OracleXmlQuery and OracleXmlsave. IBM DB2 XML Extendersql to XML mapping IBM XML Extender provides two access and storage methods for using DB2 as an XML repository: XML column: Storage and Retrieve the entire XML document to DB2 column data XML collection: Decompose the XML document into a set of relationships Table, or synthesize an XML document from a set of relationship tables. DTD is stored in a DTD repository called a DB2 table of DTD_REF; its mode name is "db2xml". Each DTD has a unique ID in the DTD_REF table. The mapping between the database table and the XML document is defined by the way of the Data Access Definition (DAD) file. DAD references a bridge between the XML document, its DTD, and the mapping rules over the database table. Here is an example DAD.
Fxtrade.dtd YES ? XML Version = "1.0"? ! docty fxtrade fxtrade.dtd
fxtrade.account = account.id
<
RDB_Node>
< Element_node name = "bancode">
<
"- End of account element -> DAD is defined in XML documentation by using element_node to rdb_node association Mapping between relational database columns. The top-level Element_Node Fxtrade is defined as a connection between table fxtrade and account, with field IDs in the Account table as primary keys. The child element Currency1 is mapped to the field Currency1 in the table FXTRADE to push. Note that Account elements do not contain any RDB_Node - this is not required because the connection between Account and Fxtrade has been defined in front. The sub elements of Account, Banccode, and BankAct are defined in the corresponding columns in the Account table. Atomic XML elements are marked in DAD as text_node. In the above example, all elements other than fXTRADE and Account are atoms. Extract XML from the database to process the synthesis and decomposition of XML documents by stored procedures: stored procedures DXXGENXML () extract XML documents from the database; stored procedure DXXSHREDXML () stores XML documents into the database. The main input parameters of dxxgenxml () are DAD: Store in the form of DB2 Clob data type; Resume Name: Constructed XML document is forwarded to this table. Other input parameters Specify the maximum number of returns, and the Element Abandon Options option. The output parameters include the actual number of returns, return code, and returns messages. In a C program in IBM DB2 Universal Database XML Extender Administration and Programming, Version 7, a detailed example of the stored procedure can be found. Storage XML puts the XML document in the database in the database is done by stored procedure dxxshredXML (). DXXSHREDXML () input parameters are
DAD: Store in the form of DB2 Clob data type; Enter an XML document: Store in the form of DB2 XMLClob data types. Its output parameter is a return code and returns a message. Summary The XML-SQL mapping rule is specified by the Data Access Definition (DAD) file, which is an XML document. DB2 XML Extender Management facilities include a means of constructing a DAD file for each lasting DTD. Further enhancement will include a new XML-SQL conversion syntax, which will use XML conversion language, which is a subset of W3C XSLT. Microsoft SQL Server 2000SQL to XML mapping SQL Server SQL-TO-XML and XML-to-SQL mapping rules use different syntax. Discuss the details of the mapping in the extraction and storage paragraphs below. Extracting the XML between the database columns and XML elements or properties Definition by way of the AS alias in Select: AS [Element Name! Nestage Level! Attribute Name! Indicator] Same as the same below, The top layer of the document is given level 1. By default, mapping column data is on attribute values. You can use the indication "element" to change this default setting. There are two steps from the process of generating XML from the database. step 1. Create an AS-alias that you want to output atomic elements in XML; alias define the parent / sub-connection between the elements. The table below shows the alias for our example document.
FXTRADE / * LEVEL = 1 * / CURRENCY1 [FXTRADE! 1! CURRENCY1] CURRENCY2 [FXTRADE! 1! CURRENCY2] AMOUNT [FXTRADE! 1! AMOUNT] SETTLEMENT [FXTRADE! 1! SETTLEMENT] ACCOUNT / * LEVEL = 2 * / BANKCODE [ Accent! 2! Bankcode] BankAcct [Account! 2! BankAcct] Step 2. Define the output tree structure in SQL. Clean each level of the tree through the SELECT statement, after which the level is combined into the tree by means of the UNION ALL statement. Level-1 SELECT statement intervenes atomic element names on all levels. Each SELECT statement is involved in a tree level tag and its parent mark. There is a single record in the result set corresponding to the root, as defined in the first SELECT statement below. SELECT 1 As Tag, Null As Parent, Null As [Fxtrade! 1! Currency1], NULL AS [Fxtrade! 1! Currency2], NULL AS [Fxtrade! 1! Amount], NULL AS [Fxtrade! 1! Settlement], NULL AS [ACCOUNT! 2! BANKCODE], NULL AS [ACCOUNT! 2! BANKACCT] FROM FXTRADEUNION ALLSELECT 2, 1, FXTRADE.CURRENCY1, FXTRADE.CURRENCY2, FXTRADE.AMOUNT, FXTRADE.SETTLEMENT, ACCOUNT.BANKCODE, ACCOUNT.BANKACCTFROM FXTRADE, Accountwhere fxtrade.account = account.idorder by [Account! 2! Bankcode], [Account! 2! BankAcct] for XML Explicit, ElementsFOR XML constructs XML documentation by analyzing tags in a combined rowset and an AS-alias. Keyword Explicit Select the most flexible, user-defined mode for constructing the XML document. Another mode AUTO constructs an XML document by applying a default rule. Keyword Elements modeling the SQL column at the element level; otherwise, the default is to model the SQL column at the attribute level. Storage XML uses OpenXML in the database to store XML documents, which is a new rowset function, similar to table or view. You can use OpenXML to insert or update or select the SELECT INTO target table. OpenXML Simplified Syntax Syntax is shown as follows: OpenXML (, , ) with (Mode | Table) Store the XML document process three steps.
Use the stored procedure sp_XML_PREPAREDocument to get an XML document handle by compiling the XML document into internal DOM. Constructs a pattern by associating an atom XML element to the pattern field. The XML element is defined by the path Pattern (absolutely based path) plus the relative element path. Indicates the element-centric mapping through the flag value 2. You can use an existing table to replace a pattern with an equivalent of the field name of the XML name. Use the stored procedure sp_xml_removedocument to remove the compiled XML document from the memory. These steps are shown in the following examples. Declare @idoc intende @doc varchar (1000) set @doc = ' GBP jpy 10000 20010325 812 00365888 '- Establish an internal DOM representation of an XML document. EXEC SP_XML_PREPAREDocument @IDOC OUTPUT, @ DOC - A SELECT statement using the OpenXML rowset provider. Select * from OpenXML (@idoc, '/ fxtrade / account', 2) with (currency1 char (3), '../@currency1', currency2 char (3), '../@currency2', Amount Numeric 18, 2), '../@amount', settlement datetime, '../@settlement', Bankcode Varchar (100), '@bankcode', BankAcct varchar (100), '@bankAcct') Exec sp_xml_removedocument @IDOC Summary For Microsoft SQL Server 2000, the extraction of the XML document, and the symmetric syntax is not used. Extract extends a SELECT-clause by using the for XML construct. Storage involves a line set function OpenXML, which is more than one table or view. The extraction mapping rule is based on (a) Intervention Specical Tree Level Tag and (b) on the parent / sub-association between the table's fields on the XML document element. Store the XML document to a flat mode or table; use the XPath representation to define the "field-element" association. Sybase Adaptive Serversql to XML mapping Sybase uses an XML document type Resultset to describe both XML document metadata (element name, size, etc.) and both actual row data. The following excerpts fxtradeSet.xml:
... ); fileutil.string2file (" fxtradeSet.xml ", RSX.GETXMLText ()); Storage XML's ResultSetXML table constructor in the database also accepts an XML document as a parameter. The method TOSQLScript then generates the sequence of the SQL statement to insert / update to the specified table from the result set.
String xmlString = FileUtil.file2string ( "FxTradeSet.xml"); jcs.xml.resultset.ResultSetXml rsx = new jcs.xml.resultset.ResultSetXml (xmlString); String sqlString = rsx.toSqlScript ( "FxTrade", ) Summary The extraction and storage of XML documents is normally symmetrical. Storage does not allow modifications to more than one table. Extract the result of the SQL query to the document with a flat structure. Manufacturer compare
Vendor mapping rules single table / multiple table transformation means symmetrical extract / storage Oracle implicit; through constructing object-relational model multiple specified Java class symmetry, if the XML document and object-relational model match IBM data access definition file Multiple specified stored procedures symmetrical MicrosoftSQL extensions; multi-tables for rowset functions are used to extract; single table is used to store the DTD single table with SQL constructuring for XML and rowset OpenXML asymmetric Sybase results set DTD single table; query can contain multiple tables The public feature between the Java symmetrical manufacturer is:
In particular, XML persistence is based on special basis. It is said that there is no general facility to store any XML document); if the XML document uses a new grammax, special maps are required; storage often requires data processing, such as the region used To reformat value / date; you can use XSLT to make XML data finishing. An alternative policy XML document stored in the database can be divided into phases? First, store XML in a normal database structure without applying any mapping rules; secondly, provide a framework for subsequent processing? This method The advantage is that there is a common acquisition agent for any XML document in fact. The remaining parts of this article only offer this solution. In this strategy, the XML document is saved as normal tree structure - raw tree data - for further application processing. The framework for further data processing is a set of database objects (stored procedures), similar to the DOM API, used to manipulate data through conventional SQL processing. The benefits of using the framework of the fully automated manner are: database groups are typically controlled by distribution in "pure" application code, transaction, database layer (Layer) and stored procedures, and do not have to Referral to some tables may reside in the remote database. In addition, in a heterogeneous database environment, there is a unified tool to make the same task for different databases. The author's solution, ObjectCentric Solutions X-Persistence Utility, implements the policies discussed here. The original tree structure is defined by the following table. 1) Tree Node Defining Create Table Nodedef (NodeID UDD_ID NOT NULL, // Unique Node ID Dimid UDD_ID NOT NULL, / / Tree Type: XML Name UDD_NAME NOT NULL, // Node NULL, // Node Value_Type UDD_TYPE NULL, // Value Type Power UDD_Power Null, // Node Tree Level ISROOT UDD_LOGICAL NOT NULL, // Top Node Sign Descriptor UDD_DEScriptor NULL, / / DOM Node Type Color UDD_COLOR NULL / / Informal Data) 2) Father between nodes - sub-relations
CREATE TABLE NODELINKS (Parentid UDD_ID NOT NULL, / / Parent UDD_ID NOT NOT NULL // Subpoint) Use an example store XML document to call XML2SQL programs in the database: XML2SQL Using the following stored procedures to extract XML The document is a tree structure:
GET_TREE_ROOTS - Extract all documents of a given forest ID GET_TREE - Extract all nodes Get_Children - Extract a given node Level All child nodes implementation details:
