When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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:

    - continued -

    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"

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article