Generating XML Pages Using Basic SQL

So how does one now apply what was learned in the first three chapters of this book about XML and all the SQL code learned in this chapter so far? How does one use SQL in a relational database to create XML documents?

First, there are various different tools and toys that allow direct access between a relational database, SQL coding, and XML documents. Some of these toys and tricks are covered in subsequent chapters. What you need to discover now is how simple XML documents can be managed using simple SQL statements and a relational database. This will help tie together all you have learned so far.

The very last section in Chapter 2 contained a brief example showing how to generate an XML document using an ASP script, while connected to a relational database. You dont need to use ASP. You cannot use basic SQL commands to create XML documents because relational database tables are two-dimensional structures, whereas XML documents are three-dimensional. XML is more of an object structure, rather than a relational structure. Let me demonstrate how you can go about creating XML documents using SQL.

This query creates a simple two-dimensional XML structure:

   SELECT '<region><name>'REGION'</name>',    '<population>'POPULATION'</population></region>' FROM REGION;   

The result could look something like this:

   <region><name>Africa</name><population>789548670</population></region> <region><name>Asia</name><population>47382633</population></region> <region><name>Australasia</name><population>24340222</population></region> <region><name>Caribbean</name><population>40417697</population></region> <region><name>CentralAmerica</name><population>142653392</population></region> <region><name>Europe</name><population>488674441</population></region> <region><name>FarEast</name><population>2100636517</population></region>   

There are two problems with the preceding output: There is no XML tag, and there is no root tag. You can try adding them like this:

   SELECT '<?xml version="1.0" ?><regions><region><name>'REGION'</name>',    '<population>'POPULATION'</population></region></regions>' FROM REGION;   

The result is this, duplicating both the XML tag and the root tag for every line (a two-dimensional structure), and is not a valid XML document:

    <?xml version="1.0" ?><regions><  region><name>Africa</name> <population>789548670</population></region>  </regions>  <?xml version="1.0" ?><regions><region><name>Asia</name> <population>47382633</population></region></regions> <?xml version="1.0" ?><regions><region><name>Australasia</name> <population>24340222</population></region></regions> <?xml version="1.0" ?><regions><region><name>Caribbean</name> <population>40417697</population></region></regions> <?xml version="1.0" ?><regions><region><name>Central America</name> <population>142653392</population></region></regions>  <?xml version="1.0" ?><regions>  <region><name>Europe</name> <population>488674441</population></region>  </regions>    

The problem can possibly be solved using something like this, given the ability in the database you are using to retrieve a string from your query tool without actually reading any tables:

   SELECT '<?xml version="1.0" ?><regions>' FROM DUAL; SELECT '<region><name>'REGION'</name>',    '<population>'POPULATION'</population></region>' FROM REGION; SELECT '</regions>' FROM DUAL;   

This way, you do get a three-dimensional structure where the root node occurs once, and contains all the regional data:

   <?xml version="1.0"?><regions> <region><name>Africa</name><population>789548670</population></region> <region><name>Asia</name><population>47382633</population></region> <region><name>Australasia</name><population>24340222</population></region> <region><name>Caribbean</name><population>40417697</population></region> <region><name>CentralAmerica</name><population>142653392</population></region> <region><name>Europe</name><population>488674441</population></region> <region><name>FarEast</name><population>2100636517</population></region> <region><name>MiddleEast</name><population>294625718</population></region> <region><name>NearEast</name><population>1499157105</population></region> <region><name>NorthAmerica</name><population>331599508</population></region> <region><name>Oceania</name><population>9133256</population></region> <region><name>RussianFederation</name><population>258037209</population></region> <region><name>SouthAmerica</name><population>375489788</population></region> </regions>   

My SQL coding tool for my database did not preserve the space in the XML tag between the strings <?xml and version="1.0"?> . I had to manually edit the XML text file to put the space character back in.

The preceding script is a valid XML document and looks like that shown in Figure 4-1, when executed in a browser.

Figure 4-1: Generating a single-layer XML document using SQL

There is one thing that is really, really wrong with using SQL to generate an XML document. It goes back to the relational two-dimensional versus the XML three-dimensional object hierarchy. Getting data from multiple tables and placing it into multiple layers in an XML document is more or less impossible with simple SQL because SQL returns two dimensions. This is one possible solution:

   SELECT '<?xml version="1.0" ?><regions>' FROM DUAL; SELECT  '<region><name>'R.REGION'</name>'  '<population>'R.POPULATION'</population>'  '<country><name>'C.COUNTRY'</name>'  '<population>'C.POPULATION'</population>'  '</country>'  '</region>' FROM REGION R JOIN COUNTRY C ON (C.REGION_ID = R.REGION_ID) WHERE R.REGION_ID IN (9,10); SELECT '</regions>' FROM DUAL;   

The preceding query is restricted to REGION_ID values 9 and 10 (Near East and North America) to make the output a little easier to follow.

As you can see in the preceding query, things are getting a little complicated. The XML output is shown here:

   <?xml version="1.0" ?><regions> <region><name>Near East</name><population>1499157105</population><country><name>Bangladesh</name><popu lation>147365352</population></country></region> <region><name>Near East</name><population>1499157105</population><country><name>India</name><populatio n>1095351995</population></country></region> <region><name>Near East</name><population>1499157105</population><country><name>Pakistan</name><popula tion>165803560</population></country></region> <region><name>Near East</name><population>1499157105</population><country><name>Sri Lanka</name><population>20222240</population></country></region> <region><name>Near East</name><population>1499157105</population><country><name>Turkey</name><populati on>70413958</population></country></region> <region><name>North America</name><population>331599508</population><country><name>Canada</name><popula tion>33098932</population></country></region> <region><name>North America</name><population>331599508</population><country><name>Greenland</name><pop ulation>56361</population></country></region> <region><name>North America</name><population>331599508</population><country><name>United States</name><population>298444215</population></country></region> </regions>   

The preceding XML output looks as shown in Figure 4-2, showing countries contained within regions.

Figure 4-2: Generating a multiple-layer XML document using SQL

You can use multiple SQL statements to contain countries within regions more distinctly, something like that shown here, which is getting really complicated:

   SELECT '<?xml version="1.0" ?><regions>' FROM DUAL; SELECT  '<region><name>'REGION'</name>'    '<population>'POPULATION'</population>' FROM REGION WHERE REGION_ID=1; SELECT  '<country><name>'COUNTRY'</name>'    '<population>'POPULATION'</population>'   '</country>' FROM COUNTRY WHERE REGION_ID = 9; SELECT '</region>' FROM DUAL; SELECT  '<region><name>'REGION'</name>'    '<population>'POPULATION'</population>' FROM REGION WHERE REGION_ID=2; SELECT  '<country><name>'COUNTRY'</name>'    '<population>'POPULATION'</population>'   '</country>' FROM COUNTRY WHERE REGION_ID = 10; SELECT '</region>' FROM DUAL; SELECT '</regions>' FROM DUAL;   

The output of the preceding script is identical to the previous example except that it might have new lines in more places to break up the XML document a little better:

   <?xml version="1.0" ?><regions> <region><name>Africa</name><population>789548670</population> <country><name>Bangladesh</name><population>147365352</population></country> <country><name>India</name><population>1095351995</population></country> <country><name>Pakistan</name><population>165803560</population></country> <country><name>Sri Lanka</name><population>20222240</population></country> <country><name>Turkey</name><population>70413958</population></country> </region> <region><name>Asia</name><population>47382633</population> <country><name>Canada</name><population>33098932</population></country> <country><name>Greenland</name><population>56361</population></country> <country><name>United States</name><population>298444215</population></country> </region> </regions>   

The point I am trying to make in this section is that you need more of what is called program control. SQL is a scripting language. SQL sends a single command to a database, where there is no dependence between one command and the next . A proper programming language allows more control, using things such as loops and other programming control structures. In a programming language like C, you can embed the SQL statements shown in the previous example and have better control. However, you would still have individual SQL statements.

There is a better way. Most relational databases contain some kind of procedural language that is intended to create stored procedures. A stored procedure is a procedure that is stored in a database and intended to operate on database data. Some relational databases have very simple stored procedure languages; others allow a lot of sophistication. The code that follows is a specialized stored procedure language (in this case pseudocode), allowing looping constructs to be built for each record retrieved from a parent table. In the case of the demographics database, the REGION table is a parent table of the COUNTRY table, COUNTRY is a parent of STATES, and STATES is a parent of CITIES.

Pseudocode is code that will not work anywhere , in any database or programming language, or for XML. Pseudocode is conceptual in nature and intended to explain or describe a concept, without the nitty-gritty details of actually making coding work in any specific environment.

   BEGIN    Regions = SELECT * FROM REGION;    FOR R IN Regions         Countries = SELECT * FROM COUNTRY WHERE REGION_ID = R;       FOR C IN COUNTRIES          States = SELECT * FROM STATE WHERE COUNTRY_ID = C;          FOR S IN STATES             Cities = SELECT * FROM CITY WHERE STATE_ID = S;          END LOOP       END LOOP    END LOOP END;   

Now using the same pseudocode example, I have added some of the XML tags and code into the pseudocode script that follows:

   BEGIN    print '<?xml version="1.0" ?><regions>'    Regions = SELECT * FROM REGION;    FOR R IN Regions         print '<region><name>'REGION'</name><population>'POPULATION'</population>'       Countries = SELECT * FROM COUNTRY WHERE REGION_ID = R;       FOR C IN COUNTRIES          print '<country><name>'REGION'</name><population>'POPULATION'</country>'          States = SELECT * FROM STATE WHERE COUNTRY_ID = C;          FOR S IN STATES             print '<state><name>'REGION'</name><population>'POPULATION'</population>'             Cities = SELECT * FROM CITY WHERE STATE_ID = S;             FOR CI in CITIES                print '<city><name>'CITY'</name><population>'POPULATION'</city>'             END LOOP             print </state>'          END LOOP          print </country>'       END LOOP       print </region>'    END LOOP    print '</regions>' END;   

The preceding script is pseudocode. This means it will not function as is in any programming lan guage, in any database, or on any computer.

Assume in the preceding script that the print statement dumps whatever literal string is indicated directly to the output.

There are easier ways to merge relational database and XML technologies, which you will read about in subsequent chapters.