Storing XML in RELATIONAL DATABASESESESS (2)
From http://www.xml.com
IBM DB2 XML extension
Map of SQL to XML
When using DB2 as an XML repository, IBM's XML extension provides two ways to access and store:
1. XML Column: Store and get the entire XML document from a column.
2. XML Collection: Decompose the XML document into a series of relational tables or recombine into an XML document from a series of relationship tables.
DTDS is stored in the DTD library, which is a table called DTD_REF in DB2. Its pattern name is "DB2XML". Each DTD in DTD_REF has a unique identifier ID, a mapping between the data table, and the XML document structure defined by a Data Access Definition (DAD) file. DAT references a handled document DTD, so it is in XML Document, this document provides a bridge between the DTD of this document and the rules that map to the database table.
The following is an example of a DAD:
FXTRADE.ACCOUNT = Account.id
TYPE = "VARCHAR (100)" />
TYPE = "VARCHAR (100)" /> DAD defines the image between XML elements and relational database columns by ELEMENT_NODE (Element Node) to RDB_Node (relational database node). The top-level element node fxtrade is defined as an association between the table Fxtrade and Account. Account has an ID list as a primary key. The sub-element Currency1 is mapped to the field currency1 in the table Fxtadek table, and the other similar. Microsoft SQL Server 2000 Map of SQL to XML The two-way mapping rules for SQL Server have different syntax, which will be described below. Extract XML document from the database The mapping between the database columns and XML elements or properties is defined by the AS alias in the SELECT statement: The topmost of the document is designated as level, as shown below. By default, the columns are mapped to the properties, indicating that "element" can change the default setting. Processing process from database data generates an XML document is divided into two steps: 1. Create as-aliases for the primary elements of the XML document. The alias defines the father and child relationship between the elements. The following is an alias for our sample document: FXTRADE / * level = 1 * / Currency1 [fxtrade! 1! Currency1] Currency2 [Fxtrade! 1! Currency2] Amount [fxtrade! 1! Amount] SETTLEMENT [FXTRADE! 1! Settlement] Account / * level = 2 * / Bankcode [Account! 2! Bankcode] BankAcct [Account! 2! BankAcct] 2. Define the structure of the output tree in SQL, each layer of the tree is defined by a SQL statement, and then all SQL statements are combined with all the SQL statements in the tree through Union. The Level-1 SELECT statement first defines the name of all atomic elements on other layers. Each SELECT statement has a layer of tag and its parent mark, corresponding to the root of the tree, has a record in the result set, as follows first SELECT Statement: 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 FXTRADE Union all SELECT 2, 1, FXTRADE.CURRENCY1, FXTRADE.CURRENCY2, FXTRADE.AMOUNT, FXTRADE.SETTLEMENT, Account.bankcode, Account.bankAcct From FXTRADE, Account WHERE FXTRADE.ACCOUNT = Account.id Order by [Account! 2! Bankcode], [Account! 2! BankAcc] For XML EXPLICIT, ELEMENTS For XML constructs an XML document by analyzing tag and an AS alias in a row, keyword explicit selects more flexible user-defined mode to construct an XML document, and the AUTO mode constructs an XML document according to the default rule. Keyword Elements simulates the columns in SQL as an element, otherwise, the default is to simulate column simulations as attributes. Database storage XML Use OpenXML to store XML documents, new features of a rowset, similar to table or view, OpenXML can be used to insert or update, or as a target table for SELECT INTO, its simple syntax is as follows: OpenXML ( With (Schema | Table) The process of storing the XML document is divided into the following three steps: 1. The general parsing XML document obtains its handle for the DOM, which can use the stored procedure sp_XML_PREPAREDocument. 2. Create a mode by the fields and atomic elements in the associated mode. The definition of the XML element is to add a relative element path through a path mode (absolute base path). Taking the patron-centric mapping specified by the spectrum. The existing table can be used for alternative mode, field name, and XML name. ] 3. Remove the parsed XML document from memory with stored procedure SQ_XML_REMOVEDOCUMENT. Below is an example: Declare @IDOC INT Declare @doc varchar (1000) Set @doc = ' ' - CREATE INTERNAL DOM REPRESENTATION OF THE XML Document. EXEC SP_XML_PREPAREDocument @idoc output, @doc - Execute A Select Statement Using OpenXML ROW Set 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 to sum up Using Microsoft SQL Server 2000, extracting, and storage XML documents are not symmetric syntax, extract is extended SELECT clause for XML. Storage XML is an introduction of rowset function OpenXML, similar to table or view, extracting mapping rules, based on: a) for a particular tree level introduction tag, b) is associated with the parent sub-relationship between the fields in the table and XML document elements. Storage XML is to rebuild the XML document as a simple mode or table, "Field-Element" association is defined by XPath expression. SYSBASE Adaptive Server SQL TO XML mapping Sybase uses an XML document type ResultSet to describe the metadata of the XML document (such as element name, type, size, etc.) and actual row data. Here is an excerpt of fxtraDeset.xml documents: ... getColumnlabel = "currency1" getColumnName = "currency1" getColumnType = "12" ... /> ... ... ResultSet's DTD does not allow definitions of nested elements. Extract XML from the database Java class ResultSetXML has a constructor that uses a SQL query as a parameter, then the getXMLLText method extracts the XML document from the result set: JCS.XML.ResultSet.ResultSetXML RSX = new jcs.xml.resultSet.ResultSetXML ("Select * from fxtrade", Fileutil.String2File ("fxtradete.xml", RSX.GETXMLText ()); Database storage XML Java class RESULTSETXML can also be used as a parameter for its constructor data, and then the TOSQLScript method generates a sequence of SQL statements to insert or update a specific table. String Xmlstring = fileutil.file2String ("fxtradeSet.xml"); JCS.XML.ResultSet.ResultSetXML RSX = new jcs.xml.resultSet.ResultSetXML XmlString; String sqlstring = rsx.tosqlscript ("fxtrade", to sum up Here, the extraction and storage XML document is essentially symmetrical, and the storage does not allow for more than one table, and the extraction converts the result of a SQL query into a simple structure document. Comparison of suppliers: Supplier mapping rules single table / multi-table conversion method symmetrical extraction / storage Oracle Indicate multi-tables in the constructive object data model Indicates multi-table having corresponding Java classes If the XML document and relational object model match, it is symmetrical IBM DAD (Data Access Definition File Multi-table built-in stored procedure symmetrical Microsoft SQL extension, row set function extraction is multi-table, storage is a single table with SQL statement for XML and rowset OpenXML asymmetric Sysbase result set DTD single table, but the query can contain multiple tables Java class symmetrical Suppliers common feature: 1. The continuation of XML has a specific principle, that is, the storage of any XML document is stored and there is no convenient way. 2. The XML document needs to be pre-processed when storing, such as the format of the digital / date conversion, and the XSLT can be used for this processing.
