Converting Hierarchical Recordsets Into XML, Part 2By Richard Chisholm
On the ASP page you will need to create both the SQL and connection strings used in the component. This allows you to use the component across multiple pages that call different databases. If you don't know how to data shape recordsets or you need to brush up, be sure to read Scott Mitchell's great articles on the subject: Data Shaping and Advanced Data Shaping Techniques. We will be using the Northwind database provided by Microsoft. Let's take a look at the first part of the ASP page:
Note - since I used a
With block, the VBScript scripting engine 5.0 or higher will be needed...
if you are unsure what version of VBScript you are running be sure to check out:
Determining the Server-Side Scripting Language and Version.
First we create an instance of the component and then assign the connection, where
strPath is the
full path to your database, and
cmdSQL is your data shaped SQL statement. Notice the data-shaping
provider is included in the connection string. In the ASP file, I have included 3 SQL statements: parent-child,
parent-child-grandchild, and parent-child-grandchild-great-grandchild. For this article we will use the
following parent-child SQL statement:
Nothing special here, but note I am using the
WHERE clause to limit the data to a few records.
Now we are ready to get into the component and start building the document. First the XML object must be
This is pretty self-explanatory, but there are a few different ways to do this. If you want to validate
against a schema then you can set
validateOnParse to true. Additionally, to take advantage of
the more current XML parsers released by Microsoft (the MSXML3 parser, which we will use on the client side
later), take a look at this recent article from ASPToday,
Monitor Your Web Site Performance with the XMLHTTP
Component, that lists the different ProgID's and ClassID's. Following the "just in time" doctrine the
database connection should not be opened until the last possible moment, meaning we can create the first portion
of the XML document before opening the db connection. First the processing instruction will be created, then
the root element.
XMLInstruct is used here at the top of the document to create, as you can probably guess, the
<?xml version="1.0"?> tag. The
appendChild method on Line 2 is used to attach
new elements/instructions to the XML document. Then we create the root element (Line 3) using the
createElement(YourElementName) method. Again, to add this element to the XML document
(Line 4) use
appendChild, and then we can add the attributes. With the
method, the name of your attribute goes first, then the value. On Line 5, you can see I am using the
Name attribute to hold my data. Line 6 attaches the schema, so that you can ensure your document
is valid if you set the
validateOnParse value to true. That is not necessary, but if you are
planning to include a schema, here is where you do it (I have commented it out). The above code will produce:
Now we can open the database, get our data shaped recordsets and call the recursive function to create the bulk of the document, which we'll look at in Part 3.