Mapping XML to a Relational Database

Currently there are a small number of vendors offering XML extensions to their database products. Both Oracle and Microsoft support SQL-like queries that return XML documents. Microsoft's implementation is completely proprietary and requires both Microsoft Internet Information Server and Microsoft SQL server and will not be discussed here. Oracle's implementation only requires the Oracle database server and we will examine it later in this chapter.

One thing is clear: We need to map relational data to XML. On the surface, this appears somewhat difficult as the structure of XML documents and relational database tables differs. XML documents are completely tree based, containing elements within elements within elements. Database products traditionally contain databases (and views) containing tables, and tables containing fields. However, close examination shows that a relational database is an example of a limited tree and can be mapped as such.

In general, there is a broad spectrum of possible mappings from a relational database to XML. At one end of the spectrum is a completely customized mapping, which requires a large investment in time and resources to develop. On the other end of the spectrum is raw output from a data dumper, which does nothing more then read a table and output its data. In fact, we provide a simple example of such a dumper when we review JDBC and how we can use it to access database.

Let's look at a table that represents a set of real estate listings. Such a table might be created with SQL syntax similar to that which follows.

CREATE DATABASE RELISTINGS;

CREATE TABLE RELISTING (
      KEY INT PRIMARY KEY,
      BROKER INT,
      TYPE CHAR(20) ,
      LISTPRICE INT ,
      ADDR CHAR(50) ,
      DESCRIPTION CHAR(100) );

CREATE TABLE BROKERS (
      BROKERKEY INT PRIMARY KEY FOREIGN KEY,
      NAME CHAR(30)
      ADDR CHAR(124));

The XML representation of this might look something like this:

<REListings>
    <REListing ID="1">
        <BROKER ID="1000">
            <NAME>RE is Us</NAME>
            <ADDR>200 Some Street, Anytown, USA</ADDR>
        </BROKER>
        <TYPE>Residential</TYPE>
        <LISTPRICE>100000</LISTPRICE>
        <ADDR>100 farm lane</ADDR>
        <DESCRIPTION>A Wonderful 4 bedroom farm</DESCRIPTION>
    </REListing>
. . .
    <REListing ID="43214">
        <BROKER ID="012">
            <NAME>Better Homes Inc</NAME>
            <ADDR>12 Pleasant Lane, Somewhere, USA</ADDR>
        </BROKER>
        <TYPE>Commercial</TYPE>
        <LISTPRICE>1000000</LISTPRICE>
        <ADDR>1 Ind. Way</ADDR>
        <DESCRIPTION>100000sq warehouse!</DESCRIPTION>
    </REListing>
</REListings>

However, the generation of such an XML document is not trivial. You will notice that we embedded into the list the broker information where key/foreign key information was stored in the database. Embedding the broker information into a listing followed the rules of XML, but such a mapping from a set of database tables might not always be possible. We also left out a fair amount of information, some of which might have been important. For example, we might have wanted to include

• Data type and precision

• Primary key information, which we loosely encoded using an ID

• Foreign key information

• Other information specific to a table such as column headers

In fact, what we defined might not even always be possible. Null foreign keys, missing data, malformed tables and so on all combine to make such a mapping difficult and problematic.

Although we will not duplicate the work going on in the XML schema group, we can see that under normal circumstances the following rules hold true:

• Tables map to <!ELEMENTS...>, which contain fields as subelements.

• Field information such as database, precision, column headers, and be mapped as attributes.

• A primary key can be mapped to an attribute of the parent as a required element.

• A Foreign key can be mapped to the appropriate key element as an optional attribute.

• Model data type, using a fixed list of attributes, map to simple values such as float, double, string, int, date, and so on.

• The precision of a model is defined by its attribute.

For example, we might have created the following DTD snippet to simulate this:

<!ELEMENT RELISTING (BROKER, TYPE, LISTPRICE,ADDR,DESCRIPTION)>
<!ATTRLIST RELISTING pkey ID #required>
<!ELEMENT BROKER (NAME,ADDR)>
<!ATTRLIST BROKER pkey ID #REQUIRED>
. . .

Our purpose in describing this simple relational-to-XML mapping is to show how complex it can be to translate from one format to another. In the next section, we examine another aspect of XML and databases, XML Queries.