XML Schema and Relational Databases | The Official XMLSPY Handbook

XML is clearly not meant as a replacement for relational databases both because relational databases are so pervasive throughout the software industry and because they are extremely efficient at storing and indexing large volumes of tabular data. Still, XML and native XML servers and repositories, such as Software AG’s Tamino server and others listed in Appendix A, offer considerable benefits when it comes to storing, indexing, and retrieving prose-oriented content such as structured documents. So I view relational databases and XML technologies as being highly complementary going forward. It is most likely that future information storage systems will employ either both relational and native XML databases or simply use traditional relational databases, which have in the past year implemented considerable support for storing XML documents. To make XML documents work with relational databases, XMLSPY implements both relational database-to-XML Schema and XML Schema-to-database conversion utilities. These utilities are the focus of this section.

Converting relational databases into XML Schemas

Because your organization likely already has considerable infrastructure in the form of existing relational-database schemas, and considering the fact that an XML Schema will likely contain similar data elements, it makes sense to use the database schemas that someone has already created to jump-start the XML Schema design process. XMLSPY can convert relational databases into XML Schemas. Virtually all databases are supported, including Microsoft SQL Server 2000, Oracle 8i/9i, Microsoft Access—or any database that supports programmatic access via ODBC or ADO.

To perform a database-to-XML Schema conversion, follow these steps:

Choose Convert → Create Database Schema.
With the Microsoft Access button selected, click the Choose File button and select the altova.mdb file located in the Program Files\Altova\ xmlspy\Import\Altova directory. In this example, connect to a Microsoft Access database because it is part of the default Microsoft Office installation. However, you would use the same process for any other database, except that you would have to type the database connection string or build a connection string using the integrated ODBC and ADO connection builder.
Click OK and XMLSPY reads the database’s schema, generates global elements corresponding to the different tables, maps SQL types to XML Schema built-in simple types, and preserves any predefined relationships. Your final XML Schema may look different because a generated XML Schema is only an approximation. You may wish to make further refinements. Still, it’s not a bad idea to use the autogenerated XML Schema as a starting point for speeding up XML Schema design.

Converting XML Schemas into relational databases

XMLSPY can also convert an XML Schema into a relational database. Again, the database generation uses either ODBC or ADO, so most relational databases are supported. The conversion algorithm used by XMLSPY is quite simple. It converts any global elements defined in the XML Schema into relational database tables and maps XML Schema built-in simple types to corresponding SQL data types. As an example, try adding additional global elements to the XML Schema that was generated from the Microsoft Access Database in the previous section. To convert the modified XML Schema to a relational database, follow these steps:

Switch to Schema Design view and choose Convert → Create DB Structure Based on Schema.
There are several options and utilities for specifying or building a database connection string; but for this example, select Create a New Microsoft Access Database Structure
Click OK.

It is likely that you will need to perform some additional development work on the resulting relational database, although it is a great starting point for representing an XML Schema in a relational database.

Using XML Schema extensions

Both Oracle9iR2 and Microsoft SQL Server 2000 databases implement support for XML Schemas. Oracle has a complete native XML database implementation called Oracle XML DB, and SQL Server 2000 has SQLXML 3.0, which contains some additional add-on programming APIs for working with SQL Server and XML. Both Oracle XML DB and SQL Server 2000’s XML features use a special mapping schema to instruct the database in how to persist an XML document in the database. A mapping schema is simply an XML Schema with additional elements and attributes, appearing under a separate namespace, which contain additional data type information needed in order to persist an XML document conforming to a particular XML Schema, to the underlying data store. Although the additional metadata includes a proprietary set of tags, these do not interfere with XML document validation and processing outside of a database-specific application. These tags belong to a separate namespace and can be ignored by an XML Schema validator if need be. To enable support for editing XML Schema extensions, in Schema Design view, choose Schema Design → Enable Oracle Schema Extensions, and/or Schema Design → Enable SQL Server Extensions. XMLSPY is currently the only schema editor to support mapping of an XML Schema to a database using mapping schemas. This will add two additional tabs to the Details window. These tabs have additional attribute inspectors used to specify type mappings, as shown in Figure 5-20.

Figure 5-20: Annotating XML Schemas to work in conjunction with relational databases.

An annotated XML Schema would potentially look like the following code listing:

 <xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema” xmlns:xdb=”http://xmlns.oracle.com/xdb”>    <xs:complexType name=”PersonType”>       <xs:sequence>          <xs:element name=”First” type=”xs:string”               xdb:SQLName=”FIRST” xdb:SQLType=”VARCHAR2”/>          <xs:element name=”Middle” type=”xs:string” minOccurs=”0”             xdb:SQLName=”MIDDLE” xdb:SQLType=”VARCHAR2”/>          <xs:element name=”Last” type=”xs:string”              xdb:SQLName=”LAST” xdb:SQLType=”VARCHAR2”/>          <xs:element name=”Age” type=”xs:integer”                           xdb:SQLName=”AGE” xdb:SQLType=”NUMBER”/>       </xs:sequence>    </xs:complexType> </xs:schema>

Notice the presence of an additional http://xmlns.oracle.com/xdb namespace and additional attributes, SQLType and SQLName.

Cross-Reference See Appendix A for a complete listing of add-on programming APIs.