Customizing the COMponent to Create an XML Structure

Introduction: This is the second 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: - continued - 1.) Create an XML document by converting data shaped recordsets with a recursive component. [Read the Article!] 2.) Getting a more customized XML document by tweaking the SQL statement and the code for the component. (This 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.

In my previous article I showed how data-shaped (hierarchical) recordsets could be converted into an XML document using a recursive component. However, there are a few problems that need to be addressed before moving on to displaying the data in a browser. These hurdles include:

• The data has no state, so each recordset row needs to have its own node
  
• Repeating data
  
• Special characters need to be taken into consideration

Additionally, the SQL statement will also need special attention to ensure the proper XML structure will be attained. Almost all of this work will be done in the Create_XMLNode() function, with examples for each step of this article. For most of this article I will again be using a parent-child SQL statement. To refresh your memory, here is the end result in the first article: cxml.xml). This article explains some of the intricacies of the relationship between ADO/SQL and XML, and how very small changes can have a significant impact on the end result.

But before going into the code, I want to once again put forth the reasons for using this structure. First and foremost it is the simplicity. A clearly defined XML document is much easier to parse and/or display than a complicated one. In this case, the parent-child-grandchild relationship, combined with what I will call "generic tag" philosophy will result in a very clear XSL template structure in articles 3 and 4. XSLT is extremely powerful, but the XSLT stylesheet, composed entirely of templates, can get very confusing quickly. Here I will show that leveraging the power of XSLT through simplicity is the best course to creating a scalable application (and fewer headaches!).

All right, let's get going. The first major issue is maintaining the integrity of the data. The main problem with the initial implementation of the COM component is that the data from each recordset row is mingled into the given node. If you look at the first XML file, in the <Product_Detail> tags there are several instances of each attribute. While this may not be a problem for simple display with XSL, any type of change to the document structure (such as a sort) would throw everything out of whack. Therefore the VB code must be changed so that for each recordset row a new XML node is created to encapsulate the data. This can be accomplished with very similar code to what is used to create the parent nodes in the Create_XMLNode() function, placed above the IF...THEN statement of said function. Here's what the basic structure looks like:

Do Until objRSet.EOF Set XMLNode = XMLDoc.createElement(arrXMLElementName(ElementName) & "s") XMLCurrentElement.appendChild XMLNode XMLNode.setAttribute "Name", objRSet(x).Name If Not IsNull(objRSet(x).Value) Then XMLNode.setAttribute "Value", CStr(objRSet(x).Value) End If For x = 1 To count1 - 1 If objRSet(x).Type = adChapter Then 'Get the child rs, create an element, and call 'Create_XMLNode to attach the child Else 'Create and attach an element with no child nodes. End If Next objRSet.MoveNext Loop

Take note that the element name created in the second line uses the current arrXMLElementName value, but adds an s to it. Why? Because it needs to be distinguishable from the rest of the data, but should not be too different that it causes confusion. If you do not add the s, the node on the document will not be added and instead you will have just another of the same element. Moreover, the first column of the recordset row is added as attributes so that the data contained within is distinguishable without looking at the rest of the node. Finally, since the current recordset data must now be attached to XMLNode, you must replace XMLCurrentElement.appendChild XMLChild with XMLNode.appendChild XMLChild in both places inside the IF...THEN statement. If you don't do it in both places, you will get some screwy results. The resulting XML code creates:

<!--parent elements --> <Product_Details Name="ProductID" Value="11"> <Product_Detail Name="ProductID">11</Product_Detail> <Product_Detail Name="OrderID">10248</Product_Detail> <!-- etc --> </Product_Details> <!-- more <Product_Details/> child elements.... -->

In Part 2 we'll look at some more customizations for the COMponent, including a more flexible SQL statement.