Implementation and analysis of XML transformation between heterogeneous databases (turn)

xml| Data | database | Transformation XML realization and analysis of heterogeneous database transformation

Type: Xml/biztalk Collection date: 2002-4-8 9:20:00


An Extensible Markup language in XML that has a good extensibility tag. In this paper, the definition of different databases is realized through XML, and the access to XML database and the exchange of heterogeneous databases are realized.
Keywords: XML Heterogeneous database Information Exchange database access
1 Introduction
XML (extensible Markup Language) It was organized by the consortium in February 1998
A common language specification developed, a simplified subset of SGML, designed specifically for WEB applications. XML, as an Extensible markup language, makes it very suitable for data exchange between different applications, and this exchange is not premised on the definition of a set of data structures in advance. The biggest advantage of XML is that it has a strong openness to data description and data transfer. In order to make xml-based business data exchange possible, it is necessary to implement XML data access for the database and integrate XML data with the application.  And make it a combination of existing business rules. The development of xml-based dynamic applications, such as dynamic information publishing, Dynamic data exchange, and so on, provided that support for XML is required.  XML provides standard formats that describe different types of data-for example, appointment records, purchase orders, database records, graphics, sounds, and so on-and can decode, manage, and display information consistently and correctly. XML was first constructed on top of Unicode (unified code), providing support for multiple languages and universal in the world.

2 combination of XML and database
There are a variety of XML data sources, according to the specific application, can be divided into the following three kinds: one is the XML plain text document; The second one is the relational database; The third comes from other kinds of application data, such as mail, catalog list, Business Report and so on. One of the first sources, That is, XML plain text documents are the most basic and simple, the greatest advantage of storing data in a file is that it can be read directly, or that the style information is displayed in the browser, or that it is connected to other applications through a DOM interface. The second data source is an extension of the first source, The goal is to facilitate the development of dynamic applications, the advantage is to manage the data through the database system, and then use the server-side applications (such as ASP, JSP, Servlet) to dynamically access. This approach is best suited to the current most popular application development based on three-tier architecture. The third type of data is widely available , which requires specific treatment. The analysis of this paper is mainly based on the first two data sources.
For XML documents, you can read the nodes in the XML document through DOM (document Object Model), which is the most basic and low-level XML access technology.  DOM is a technical standard for the consortium, and it actually provides a set of APIs to access XML data. Dom can be implemented by scripting programs such as Javascript,vbscript, or through high-level languages such as C + + and Java.
Second, you can also use the DSO (Data Source Object) for XML data binding to easily bundle XML nodes with HTML tags, from XML documents read or write data, like access to Microsoft Access or Microsoft SQL Server, and Http+sql is at the heart of Microsoft's newly proposed SOAP solution, the rationale is to access the SQL Server database directly through a URL based on the HTTP protocol and return documents in XML or HTML data format.
The XML database is divided into two types: a database stored in the original XML format we call the "Native-xml database", the other is stored and exported in XML format, and it is itself a database, we call it "xml-enabled database".
2.1 Native-xml Database
For access to the Native-xml database, we can do it through the XQL language. By XQL we can query one or more XML files, which returns a set of nodes encapsulated in the root element (Sql:result), which is also an XML file.
2.2 xml-enabled Database
Because the data is stored and exported in XML format, and it is a database itself, this involves the exchange of information between heterogeneous databases. XML provides an access technology between relational and object-oriented databases and other database systems, which means that we can access relational and object-oriented databases and other database systems, then generate XML files, and then save the files to a database or other processing.

3 using JSP to connect a database and query to generate an XML document
There are a number of ways we can connect to a database and query it to generate an XML document. The following are search and extract the XML documents stored by SQL Server through JSP technology, and then pass them on to the user. (The SQL Server database natively supports XML formatting. For databases that do not support XML, you can store XML documents as large character objects (CLOB) and retrieve documents as text blocks.) The developer of the Java language application provides a common SQL database access and storage structure, or JDBC, an application programming interface (API) for database access, which can be accomplished by a JDBC-ODBC bridge, one of the JDBC product components: Connection to the database, Send statements, process results, and so on. Before you make a connection to the database, you must add the bridge driver class Sun.jdbc.odbc.JdbcOdbcDriver to the Java.lang.System property named Jdbc.drivers. or explicitly loaded with the Java class loader. The explicit loading code is as follows:
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
This driver is available for Oracle, Sybase, and so on. We can use a simple example to illustrate how to implement a database connection with a JDBC interface, execute a query (the result is a set of XML documents), analyze the query results, and writes the parsed data to the output stream. The following is the most closely related code section:
Note that after you install the database, you need to complete the following three things to make this code work:
First, change the Dbowner, Dbuserid, and dbpasswd variables to fit the system's appropriate values.
Be sure to change these three strings correctly, or the servlet will not work.
Dbuserid = "xxxxxx";
DBPASSWD = "xxxxxx";
Dbowner = "xxxxxx";
<!--using SQL database-->
<!--first import some of the necessary packages-->
<!--start importing packages-->
<%@ page info= "Package Dbxml.sax"%>
<%@ page import= "org.xml.sax.*"%>
<%@ page import= "Org.xml.sax.InputSource"%>
<%@ page info= "Database handler"%>
<%@ page import= "java.io.*"%>
<%@ page import= "java.util.*"%>
<%@ page import= "java.sql.*"%>
<%@ page import= "javax.servlet.*"%>
<%@ page import= "javax.servlet.http.*"%>
<!--import End-->
<%
Try
{
Res.setcontenttype ("Text/xml");
Load using a driver that is appropriate for your system
The following code loads the JDBD-ODBC driver
Try
{
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (Exception e)
{
System.out.println ("Can ' t get the driver!");
E.printstacktrace ();
}
Establish a connection
The second step is to connect to the DBMS with the appropriate driver, assuming that the source database test has been established, and that table name has been established in test
String url= "Jdbc:odbc:test";
Connection con=drivermanager.getconnection (URL, "Administrator", "password");
"Administrator", "password" is the username and password
Then create a JDBC declaration
Statement stmt = Con.createstatement ();
Executes the declaration, displaying the result set. We remove the XML document from each row,
Analyze it, and then print the DOM tree. Rs.next () returns False when there are no more rows.
ResultSet rs=stmt.executequery (SQL);
while (Rs.next ())
{
String Nextorder = rs.getstring (1). Trim ();
Document doc = null;
StringReader sr = new StringReader (Nextorder);
InputSource isrc = new InputSource (SR);
Try
{
Parser.parse (ISRC);
doc = Parser.getdocument ();
}
catch (Exception e)
{
System.err.println ("Sorry, an error occurred:" + e);
}

if (Doc!= null)
Printdomtree (Doc, out);
}

Rs.close ();
Stmt.close ();
Con.close ();
%>
The returned XML document can be defined by the specified XML schema, with three modes: RAW, AUTO, EXPLICIT.  You can also use the add for XML in a SELECT statement to return XML format data as a complement to the XML schema by specifying the DTD or XML in the FOR XML Schema to format the returned XML document. In addition, we use Xml-based Update statements to update records in the database, and SQL Server supports database update operations based on XML inserts, deletes, modifications, and so on.

4 The transformation between XML schema and relational schema
In order to complete the communication between heterogeneous databases, RDMS→XML→RDMS transformations must be implemented. A specific XML schema to relational schema data transformation needs to involve more complex XML format analysis processing and corresponding database operation and validation. By comparing the characteristics of the XML data model and the relational model, we think that the essence of the two transformations is  (1) data from XML (exist in a way that can be content, property values, element names, and so on) to the mapping of fields in relational schemas; (2) The relationship between tuples and tuples in relational schemas and the mapping between tuples and fields is related to the location of data in XML. The core and essence of the transformation of the XML schema and the relational schema is the interpretation method of the transformation rules and the transformation rules written by XPDL, and then the components and execution methods of the transformation rules from XML Schema to relational schema are introduced, respectively, And the components and execution methods of the transformation rules from relational schemas to XML schemas. Transformation rule scripts from XML schemas to relational schema conversions: the definition of relational schemas and the definition of XML schemas to relational schema conversion rules; Transformation rule scripts from relational schema to XML Schema conversion: By definition and relational schema of relational schemas to XML schema conversion The definition of a rule is composed.
4.1 XML Schema to relational schema conversion
(1) Relationship pattern definition
The following is an example of a relational schema definition
<DataSchemaDefinition>
<datasource id= "Icategorydata" type= "ODBC" sourcename= "icatsrc" username= "sa" password= ""/>
<DataSchema>
<entity id= "category" Datasource= "Icategorydata" >
<Structure>
<field id= "Nodeid" type= "char" length= "no" cannull= "yes" primary_key=
...
</Structure>
<Constrain>
<reference field= "Fathernodeid" refentity= "category" Reffield= "Nodeid" >
<exceptionvalue value= "0"/>
</Reference>
...
</Constrain>
</Entity>
...
</DataSchema>
</DataSchemaDefinition>
The definition of a relational schema is divided into two parts: data source definition and definition of entity (including relational entity). The definition of a data source is nothing more than Odbc/jdbc/bde, and the purpose of defining it is the versatility of the cross platform of the conversion program. An entity definition consists of two parts: a structure definition and a constraint definition. A structure defines a table definition similar to a relational database, including definitions of the type, length, and so on for each field. A constraint definition consists primarily of a constraint definition for a foreign key and reference to a field.
(2) Definition of XML Schema to relational schema conversion rules (X2rconversion)
The goal of the transformation of the XML schema to the relational schema, is to convert the content part of an XML document defined by an XML schema, the attribute value part into the value of a field in a tuple in the relational schema, and convert the position relationship between them into an application relationship such as a foreign key in the relational schema.
<X2RConversion>
<Entrance>
<record entity= "category" Id= "St_category" path= ". Department" >
<default field= "Fathernodeid" value= "0"/>
<node path= "#id" field= "Nodeid"/>
...
<reference field= "Rootnodeid" reftype= "entity" refvalue= "Category[nodeid]" refpos= "0"/>
<record entity= "category" Id= "Run_category" path= ". Department" >
<node path= "#id" field= "Nodeid"/>
...
<reference field= "Fathernodeid" reftype= "entity" refvalue= "Category[nodeid" "refpos="-2 "/>"
...
</Record>
...
</Record>
</Entrance>
</X2RConversion>
The basic algorithm for conversion is to traverse the DOM tree of the XML document waiting to be converted, and for each node, the path from the root to the node is used as its transformation identity. Each conversion entry is a transformation identifier suffix, when a transformation entry converts the identity suffix to match the current node's transformation identifier, The conversion rule that corresponds to the current conversion entry is started. All conversion portals are defined within the entrance element. Each conversion rule starts with a record element, and the underlying element of a recording element represents the creation of a new records for its corresponding tuple. The conversion rules for data and the creation of new records for their associated tuples, and the rules for the conversion of data, that is, the record element can be nested.
In addition, the conversion will also follow the following guidelines: The conversion locations of any two record conversion instances are not the same, but the elements that are calibrated by node are not subject to this restriction, that is, the execution location of each record instance is remembered only in the conversion algorithm, and in the same sequence of rule execution. is in the order of the default element, node element, reference element, and record element, and an order is an effective operation that can overwrite the order in the previous operation.
4.2 Relational mode to XML Schema conversion
(1) Definition of transformation Rules (r2xconversion) of relational schemas to XML schemas
The transformation from relational mode to XML Schema is essentially a predefined view that runs and converts the current view content into an XML format display. In concrete implementations, we combine the format and view definitions. In the transformation of relational schema to XML Schema, the entity in the definition section of the relational schema can correspond to an SQL query statement, which is to use the SQL statement instead of the table name previously indicated in the ID, and all the data is prepared for the basic The relationship pattern definition segment has been completed. The following is an example of a relational schema to the XML Schema conversion rule, which is performed by branching out to create through a depth traversal transformation rule, And finally complete the creation of the entire XML document DOM tree. Similarly, it only supports the correlation transformation of the relative position in the subtree of a path extension, and its specific structure is similar to that in the preceding section.

5 concluding remarks
At present, the database is no longer limited to the storage of data, but to a deeper, more diversified development, it has been widely used in OLAP, OLTP, data warehousing, data mining, mobile computing, embedded computing and Web applications, and many other aspects. Now, database technology has also gone deep into all walks of life, especially Finance, transportation, commercial and other fields have been widely used. This paper focuses on the access technology of XML database and the exchange of mutual visits between heterogeneous databases through conversion to XML. We have reason to believe that the combination of XML and database will achieve more powerful functions with the development of XML technology.

Reference documents
[1] http://www.xmlsoftware.com/
[2] Yang Yu, Lu Banjuan, Zhaohao Tong, JSP Network development technology. People's post and telecommunications press
[4] Http://www.microsoft.com/xml/xsl/msxsl-f.htm
[5] Http://www.51xml.com/forum/search_forum_xml.xml
[6] Michael Morrison,et al.  XML Discovery---Getting Started-application-proficient. Tsinghua University Press, 2000.8