Get Your Data on Board: Creating XML Data Sources from Relational Databases

Charles Heinemann
Program Manager, XML
Microsoft Corporation

March 9, 1998

The following article was originally published in the Site Builder Network Magazine "Extreme XML" column (now MSDN Online Voices Extreme XML).

Editor's note: Ask anyone who tried to squeeze into the standing-room-only talks at Microsoft's recent Web Tech·Ed conference: XML is Topic A. Putting data to work on your Web site via the structure of Extensible Markup Language solves many limitations associated with HTML -- and it's the subject of SBN Magazine's newest monthly column. We welcome Charles Heinemann to our family of columnists and hope you'll put his advice to work on your pages. Let us know how you like the column.

As the new kid on the Site Builder Network block, I first thought I'd tread a little lightly, feel things out, hang back. But then I reconsidered. Why not just jump in and give the people what they want: XML, and lots of it. So here's how to create XML data sources from data you already have stashed away in a relational database.

What You Already Have and Where You Have It

The great thing about database applications such as Microsoft Access is that they make it easy to organize, store, and view data. For instance, you can create a "TAPES" database and catalog your New Kids on the Block tape collection with relative ease. Within that database, you can then, for example, create a HotBSides table consisting of data about your favorite mix, NKOTB: Hot B-Sides.

HotBSides table

TITLE	CD	YEAR
Didn't I (Blow Your Mind)	New Kids on the Block	1986
What'cha Gonna Do (About It)	Hangin' Tough	1988
Funny Feeling	Step by Step	1990
Are You Down?	New Kids on the Block	1986

The drawback to such applications, however, is that other applications cannot easily access the data within such databases -- nor, more germane to the goals of Web authors, can HTML pages.

Where XML Comes In

XML is valuable in that it is a universal data format that is text-based, is easily parsed, and enables interoperability. Once converted into an XML data source, the data within the Access-generated database can be easily accessed and manipulated by other applications and by HTML pages.

If you could simply transform the data within your HotBSides table into XML-based data, you would then have a data source that you could access through script or through a Data Source Object (DSO). This would make the data within the table available to others on the World Wide Web or an intranet who share your musical tastes (however suspect those tastes may be).

Utilizing ASP Pages

Using Active Server Pages (ASP) technology, you can easily transform the above table into an XML data source. This source will still be an ASP file. However, like an ASP file that outputs an HTML page, it will output accessible XML on the server.

This method of creating an XML data source offers two benefits. First, your XML data source can be populated using script, meaning that transforming even large chunks of data can be pretty painless.

Second, the XML data source is generated dynamically from the data within the database, and as the database is updated, so too is the XML data source. This means you can continue to maintain your database using a tool such as Access, while simultaneously maintaining your XML data source with no additional effort.

(To walk through how script and the XML DSO can be used to access an XML data source generated from an ASP file, see the XML Auction Demo.)

Creating XML Data Sources on the Server

ASP files can be used to create XML data sources in the same way that they can be used to create HTML pages. The only major difference is that instead of the page's basic structure being that of an HTML page, its basic structure is that of an XML data source.

The ASP file begins with a declaration of the scripting language and the version of XML being used, followed by the start tag of the top-level element:

   <%@LANGUAGE = VBScript%>
   <?xml version="1.0">
   <HotBSides>

Populating the XML Data Source

Before you are able to access the TAPES database, it will need to be named. To name a database, go to the Windows control panel and click the ODBC icon. Add the database in question (here, the TAPES database) to the list.

At this point, you can connect to the named database using an Active Data Object (ADO). The following script creates an object out of the TAPES database, accesses the HotBSides table, and initiates the loop that will recursively populate the data source:

<%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open "TAPES"
   Set Tape = Conn.Execute("select * from HotBSides")
   Do While Not Tape.EOF
   %>

Now you must construct the basic structure of the elements that will contain the data from your table. XML is all about hierarchies, all about sets and subsets. How you group the data is important to the soundness and usefulness of your data source. Fortunately, your HotBSides table offers you a structure that makes good logical sense when rebuilt in XML.

Clearly, the table has four groupings, each of which could be characterized as a song. Each of these groupings or elements could then be said to contain three subelements: title, CD, and year. The following XML represents the basic framework of one such song element.

<song>
      <title></title>
      <cd></cd>
      <year></year>
   </song>

Examining the data in the HotBSides table, it quickly becomes obvious that you will need to create an XML data source that contains four song elements, each of which contains one title, one CD, and one year element.

The loop in the above script enables you to populate each of these four song elements. Consequently, all you need to do is create the framework of the song element, then populate that framework with variables that represent the different fields in your table:

   <song>
      <title><%=Tape("TITLE")%></title>
      <cd><%=Tape("CD")%></cd>
      <year><%=Tape("YEAR")%></year>
   </song>

With the song element constructed, you need only tell the ASP page to keep moving to the next entry in the table, complete the loop, and close the top-level element.

   <% Tape.MoveNext
   Loop
   %>
   </HotBSides>

The above ASP page will create the following XML data source.

   <?xml version="1.0">
   <HotBSides>
<song>
         <title>Didn't I (Blow Your Mind)</title>
         <cd>New Kids on the Block</cd>
         <year>1986</year>
      </song>
      <song>
         <title>What'cha Gonna Do (About It)</title>
         <cd>Hangin' Tough</cd>
         <year>1988</year>
      </song>
      <song>
         <title>Funny Feeling</title>
         <cd>Step by Step</cd>
         <year>1990</year>
      </song>
      <song>
         <title>Are You Down?</title>
         <cd>New Kids on the Block</cd>
         <year>1986</year>
      </song>
   </HotBSides>

Summary

Having turned the data within your relational databases into XML-based data, you can now make your stored data available to any and all with a mind to view it.

Before signing off, let me just note that in addition to the walk-through demo mentioned earlier, there are also how-to's in the XML section that might be of use to you. One more thing: I was doing that pseudo-tough-guy thing before Donnie Wahlberg could trim his own goatee. And don't let anyone else tell you differently.

Charles Heinemann is a program manager for Microsoft's Weblications team. Coming from Texas, he knows how to think big.

More 'X'pertise

CMP.NET's TechWeb has a nifty, in-depth package of articles on XML in its Tech Tools section: Xpand Your Site With XML by William Gee and John Gartner. Highly recommended.

XML: The basics

For a primer on XML, read Robert Carter's article, Elementary XML.

Visit the XML pages of MSDN Online Web Workshop for an up-to-date FAQ, data spec, how-to articles, XSL style sheets, the all-important parsers, demos, and links to XML-related conferences and events.

For technical how-to questions, check in with the Web Men Talking, the Web Workshop's answer pair.