Published: Saturday, June 10, 2000
Converting Hierarchical Recordsets Into XML, Part 2
By Richard Chisholm
Read Part 1
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:
Set objCvtXML = Server.CreateObject("cXML.Convert")
With objCvtXML 'pass the db info
.Connection = "Provider=MSDataShape; Data Provider=" & _
"Microsoft.Jet.OLEDB.4.0; Data Source=" & strPath
.SQL_String = cmdSQL
End With
strXMLCode = objCvtXML.Convert
|
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:
cmdSQL = "SHAPE {SELECT * FROM Orders WHERE OrderID > 10261 " & _
"AND OrderID < 10264} As Order " & _
"APPEND ({Select ProductID, OrderID, UnitPrice, Quantity, Discount From " & _
"[Order Details] WHERE OrderID > 10261 AND OrderID < 10264} As Details" & _
" RELATE OrderID TO OrderID)"
|
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
initialized:
Set XMLDoc = Server.CreateObject("Microsoft.XMLDOM")
With XMLDoc
.async = False 'We do not want asynchronous downloads
.validateOnParse = False 'don't want to validate
.preserveWhiteSpace = False 'do not preserve white space
.resolveExternals = False 'do not resolve external DTDs, etc
End With
|
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.
1: Set XMLInstruct = XMLDoc.createProcessingInstruction("xml", " version=""1.0""")
2: XMLDoc.appendChild XMLInstruct
3: Set XMLRoot = XMLDoc.createElement("Root") 'Set the value
4: XMLDoc.appendChild XMLRoot 'And then attach it to the document.
5: XMLRoot.setAttribute "Name", "Root" 'The name of the tag, then the value
6: 'XMLRoot.setAttribute "xmlns", "x-schema:schema.xml"
|
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 setAttribute
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:
<?xml version="1.0"?>
<Root Name="Root" xmlns="x-schema:schema.xml"/>
|
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.
Read Part 3 of "Converting Hierarchical Recordsets into XML"
