XML Data as a Relational Structure

In Chapters 5 and 6, I examined various aspects of both Oracle and SQL Server databases for handling XML data generation from relational tables. For the purposes of this argument, lets assume you have none of any of the tricks and tools demonstrated in those two chapters. In other words, to build XML data from relational tables, you have to write the XML structure from scratch.

So, the only way to read relational tables and convert them into XML document data would be to read the tables, and manually code in the XML structure, such as elements and values. This is not a problem as long as the relationships between the tables remain one-to-many in nature. A one-to-many relationship can technically be described as one of containment. A collection of many records (in a table) is contained in (or related to) a single record in the related parent table. The parent table is on the one side of the one-to-many relationship. This is all fine and good for XML, until a many-to-many relationship appears. An XML document hierarchical structure means that many (child nodes) are contained within one (parent node). And thus many cannot be contained within many because it contradicts the collection containment hierarchy.

So already the relational model falls short of the hierarchical requirement of an XML document. As a result, the conversion of relational table data to that of the XML document equivalent has to be drastically simplified. As already mentioned, lets assume that we dont have access to any of the types of XML functionality described in Chapters 5 and 6. The only way to resolve relational table data in XML documents is to flatten out the structure. This flattening is much like a data warehouse or a join query, denormalizing a normalized relational table structure.

The following query retrieves three fields from the REGION table in the demographics database (see Appendix B):

   SELECT REGION, POPULATION, AREA FROM REGION;   

The result of the preceding query is shown here:

   REGION                   POPULATION       AREA ------------------------ ---------- ---------- Africa                    789548670   26780325 Asia                       47382633     657741 Australasia                24340222    7886602 Caribbean                  40417697     268857 Central America           142653392    2360325 Europe                    488674441    4583335 Far East                 2100636517   15357441 Middle East               294625718    6798768 Near East                1499157105    4721322 North America             331599508   18729272 Oceania                     9133256     536238 Russian Federation        258037209   21237500 South America             375489788   17545171   

Now lets create some simple XML using only simple SQL functionality. The following single table query does not produce a root element containing records within that root node. Without using anything but simple SQL functionality, I cant add in the opening and closing root elements:

   SELECT '<record region="'REGION'" population="'    POPULATION'" area="'AREA'"></record>' FROM REGION;   

This is the result:

   <record region="Africa" population="789548670" area="26780325"></record> <record region="Asia" population="47382633" area="657741"></record> <record region="Australasia" population="24340222" area="7886602"></record> <record region="Caribbean" population="40417697" area="268857"></record> <record region="Central America" population="142653392" area="2360325"></record> <record region="Europe" population="488674441" area="4583335"></record> <record region="Far East" population="2100636517" area="15357441"></record> <record region="Middle East" population="294625718" area="6798768"></record> <record region="Near East" population="1499157105" area="4721322"></record> <record region="North America" population="331599508" area="18729272"></record> <record region="Oceania" population="9133256" area="536238"></record> <record region="Russian Federation" population="258037209" area="21237500"></record > <record region="South America" population="375489788" area="17545171"></record>   

The preceding query and all queries in this chapter, together with the rest of this book, are tested using an Oracle Database. There is no use of SQL Server Database outside of Chapter 6 .

I actually cannot get the root element tags into the preceding query unless I do so manually.

The next query joins multiple tables together, again flattening the result into a report-like, single-layered structure:

   SELECT R.REGION, R.POPULATION, R.AREA,    C.COUNTRY, C.CURRENCY FROM REGION R JOIN COUNTRY C ON(C.REGION_ID = R.REGION_ID) WHERE C.CURRENCY IS NOT NULL;   

This is a partial result:

   REGION      POPULATION       AREA COUNTRY      CURRENCY ----------- ---------- ---------- ------------ ----------------- Africa       789548670   26780325 Tunisia      Dinars Africa       789548670   26780325 Uganda       Shillings Africa       789548670   26780325 Zambia       Kwacha Africa       789548670   26780325 Zimbabwe     Zimbabwe Dollars Australasia   24340222    7886602 Australia    Dollars Australasia   24340222    7886602 New Zealand  Dollars Caribbean     40417697     268857 Bahamas      Dollars Caribbean     40417697     268857 Barbados     Dollars Caribbean     40417697     268857 Bermuda      Dollars Caribbean     40417697     268857 Costa Rica   Colones Caribbean     40417697     268857 Cuba         Pesos   

And now the same join query but including coding to allow for XML tags:

   SELECT '<record region="'R.REGION'" population="'    R.POPULATION'" area="'R.AREA'" country="'C.COUNTRY    '" currency="'C.CURRENCY'"></record>' FROM REGION R JOIN COUNTRY C ON(C.REGION_ID = R.REGION_ID) WHERE C.CURRENCY IS NOT NULL;   

The partial result, which is a single layer containing one record for each country, is shown here. This is exactly the way that a simple SQL-based report would produce join query data, all flattened into a single record for each member of the join:

   <record region="Africa" population="789548670" area="26780325" country="Tunisia" cu rrency="Dinars"></record> <record region="Africa" population="789548670" area="26780325" country="Uganda" cur rency="Shillings"></record> <record region="Africa" population="789548670" area="26780325" country="Zambia" cur rency="Kwacha"></record> <record region="Africa" population="789548670" area="26780325" country="Zimbabwe" c urrency="Zimbabwe Dollars"></record> <record region="Australasia" population="24340222" area="7886602" country="Australi a" currency="Dollars"></record> <record region="Australasia" population="24340222" area="7886602" country="New Zeal and" currency="Dollars"></record> <record region="Caribbean" population="40417697" area="268857" country="Bahamas" cu rrency="Dollars"></record> <record region="Caribbean" population="40417697" area="268857" country="Barbados" c urrency="Dollars"></record> <record region="Caribbean" population="40417697" area="268857" country="Bermuda" cu rrency="Dollars"></record> <record region="Caribbean" population="40417697" area="268857" country="Costa Rica" currency="Colones"></record> <record region="Caribbean" population="40417697" area="268857" country="Cuba" curre ncy="Pesos"></record>   

Again, there is no root element in the query shown in the preceding code. Additionally, there is no hierarchy, which is expected for XML. The hierarchy would contain countries within each region where the result would have two layers (in addition to the root node layer). One layer would be regions where each region contains a collection of countries. That hierarchy would look something like this:

   <region name="Africa" population="789548670" area="26780325">    <country name="Tunisia" currency="Dinars"></country>    <country name="Uganda" currency="Shillings"></country>    <country name="Zambia" currency="Kwacha"></country>    <country name="Zimbabwe" currency="Zimbabwe Dollars"></country> </region> <region name="Australasia" population="24340222" area="7886602">    <country name="Australia" currency="Dollars"></country>    <country name="New Zealand" currency="Dollars"></country> </region> <region name="Caribbean" population="40417697" area="268857">    <country name="Bahamas" currency="Dollars"></country>    <country name="Barbados" currency="Dollars"></country>    <country name="Bermuda" currency="Dollars"></country>    <country name="Costa Rica" currency="Colones"></country>    <country name="Cuba" currency="Pesos"></country> </region>   

Lets go a little further with the partial result shown next. This is a properly formed XML document with four hierarchical layers (including a manually added root node) instead of the two layers in the previous result:

   <planet>    <name>earth</name>    <region>       <name>Africa</name>       <population>789548670</population>       <area>26780325</area>       <country>          <name>Tunisia</name>          <currency>Dinars</currency>       </country>       <country>          <name>Uganda</name>          <currency>Shillings</currency>       </country> ...    </region> </planet>   

Figure 8-1 shows the browser representation of the partial XML document shown in the preceding code, with the <xml> tag processing instruction included in the figure to make it function properly.

Figure 8-1: The different hierarchical layers in an XML document

What have these two examples demonstrated? That simple SQL queries executed against tables in a relational database do not suffice for generation of XML documents. Of course, you already know that from previous chapters. However, it should be clear from all those tricks and toys demonstrated in Chapters 5 and 6 that specialized functionality is needed to map between the relational structure of Oracle or SQL Server database and the hierarchical-object structure of an XML document.