Published: Saturday, June 10, 2000
Converting Hierarchical Recordsets Into XML
By Richard Chisholm
Introduction:
This is the first of a series of articles I am writing that deals with using XML and the latest in XSLT
technology to create a dynamic web application. My tentative plan for these articles are the following:
1.) Create an XML document by converting data shaped recordsets with a recursive component.
2.) Getting a more customized XML document by tweaking the SQL statement and the code for the component.
[Read the Article!]
3.) Using XSLT to get a viewable HTML interface in IE5.
[Read the Article!]
4.) A case study using XSLT to create an application that dynamically filters and sorts the XML data.
That may seem like a lot, but that's because there is a whole lot to cover. These articles are designed for
ASP programmers with limited XML/XSLT experience, so if you are already familiar with some of this bare with
me. I don't think most ASP developers have gone too far in XML, but you can do some really slick things with
it, some of which you'll see here. To brush up on your XML, be sure to check out the
XML Section on 4Guys!
For parts 1 and 2, you will need IE5 (with the 2.0 MSXML parser), and Visual Basic 6 if you want to edit the
source yourself. For parts 3 and 4, you will need IE5 and at least the March preview release for MSXML3. This
can be found at http://msdn.microsoft.com/downloads/webtechnology/xml/msxml.asp
and you may want to visit the news group news://msnews.microsoft.com/microsoft.public.xml.msxml-webrelease
if you have problems getting it to work properly. I found a nifty script that tests if your installation was
performed correctly, as well as the info to get MSXML3 working. For the record, the component was developed and
tested on a NT Workstation with the IE5 MSXML processor, while the XSLT used in parts 3 and 4 was created on a
Win98 machine loaded with IE 5.01 and the March release of MSXML3, using the Athens XML Editor.
|
Part I: Converting Hierarchical Recordsets into XML
XML can be a very powerful too for ASP programmers. Although Microsoft has enabled ASP & VB programmers to save
recordsets in XML format quickly and easily through the ADO Save command, doing so has several shortcomings.
First, all the fields in the recordset are saved as attributes, making the document difficult to read and any
XSL transformation can become overly complicated. Second, you cannot specify the tag names you would like to
use, meaning your data can become harder to manipulate and understand (the opposite of what XML is intended for!).
To see what I mean, take a look at this XML file created with the
ADO adPersistXML method. It is in text format for those without IE5.
However, if you have similar data in different tables with different field names, instead of having to write
multiple XSL documents, it would be easier if you could create nested, generic tag names such as <product>
or <sport> and use an attribute like name= or value= to assign
your data. This becomes an important issue when designing efficient style sheets. Third, and most importantly
for this exercise, if you want to use data shaping and create hierarchical recordsets, you cannot use the
Save method because ADO doesn't seem to know how to handle the child recordset field.
To solve these problems, I wrote a COM component that converts shaped recordsets into an XML document that
uses a recursive function to generate the XML. It takes three parameters, and the whole program can be easily
customized to create the type of XML document you require. The beauty of this component is in its simplicity:
you can transform basic and complex database queries into XML with very little hassle. And by the end of this
article, we will have an XML document that is much easier to read than the ADO method.
Wait, I can hear you ask, why should we use hierarchical (or data shaped) recordsets? The advantage of using
them in this case is that your data comes out in a format much like most XML documents: with nested, parent-child
relationships that define the structure of the data. Because this component is recursive, the structure of
the XML will be entirely dependent on the structure of your SQL statement. This way, you can use the component
to build different data structures simply by changing the SQL statement. For now you'll have to take my word
for it, but in the next article I will show how a simple change to the SQL statement can produce a major change
in the structure of the XML document. Additionally, because your data already has a discernable structure, the
component spends less time (i.e. there are fewer lines of code, doesn't do a lot of detecting, and it runs
faster) transforming the data.
In my tests, this component created parent->child->g-child->gg-child, as well as siblings of each, with no
hassle. There is one snag however. Because in this example we want to create generic tag names using a
recursive function, we need to have those values stored somewhere, in this case an array. Then when the
function is called the proper numeric value is passed. I am going to assume basic knowledge of creating and
using components, and skip right on to the code. Don't worry, the methods in the XML DOM are easy to
understand even for those who have never seen them.
Read Part 2 of "Converting Hierarchical Recordsets into XML"
