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. 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.
