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: Monday, July 03, 2000

Customizing the COMponent to Create an XML Structure, Part 2

By Richard Chisholm

  • Read Part 1

  • In Part 1 we looked at making our first change to the COMponent. This change effects the XML output. The new XML output (of which we looked at a snippet of in Part 1) can be seen in whole at: Ex1.xml. As you can see, the data from each recordset row is now contained within its own XML tag, and when each <Product_Details/> tag is collapsed they can be identified by the ProductID value. However, you may have noticed that this change has uncovered the next problem: repeating data. We can easily solve this minor problem by changing the FOR...NEXT starting parameter from 0 to 1. The result is the first column is skipped inside the FOR loop.

    - continued -

    Remember that one of the principle ideas behind this component is that everything is dependent on the SQL statement. As such, it is important to put significant thought into creating and modifying your SQL statement to produce the desired results. For example, I paid close attention to the order of the columns in my SELECT statement (i.e. having ProductID first) to have distinguishable data. Placing OrderID first in the SQL statement would necessitate checking the child elements to determine the product. Look at this example file and collapse the Product_Details nodes, to see what happens when OrderID is first: Ex2.xml. As another example, by switching the parent-child relationship between the tables, a completely different XML document is produced: Ex3.xml. In this case, there is a significant amount of repetitive data, and unlike the previous document it is not well structured. While getting down and dirty with your SQL statement may seem like a hassle, it makes your job as a whole easier by allowing the component to be very flexible without a lot of code.

    One thing to keep in mind is planning for situations where the data uses one or more of XML's special characters. These include <, >, and &. You may have noticed that the SQL scripts I've been using are very particular in the rows they are selecting, and that is to avoid special characters to keep these examples simple. If these are present in your data and you attempt to insert them into an element, the parser will blow up. However there is an easy way to get around this using the createCDATASection method. The CDATA tag acts similar to a standard element in that you can add text to it, but is different because it encloses the text to allow the inclusion of special characters, or even XML fragments. In part 3, the CDATA element will be used in the XSLT stylesheet to encapsulate all of the JavaScript. To use CDATA in this component, find and comment out the line where text is added, and then paste in the following code:

    '**Put this at the top of the function with the others
    If Not IsNull(objRSet(x).Value) Then
      Set XMLCData = XMLDoc.createCDATASection(CStr(objRSet(x).Value))
      XMLChild.appendChild XMLCData
    End If

    The result is not very pretty, but it is only marginally larger than the file would be without using CDATA (see Ex4.xml). Additionally, as the data per element increases from a few words to whole paragraphs, the addition of the few characters to isolate the CDATA becomes increasingly smaller.

    Finally, there is just one more thing that needs consideration. Occasionally the last child recordset will not have multiple rows. For example, if I needed to include the supplier data in our test case (as will be done in part 3), there is no need to separate each recordset row inside its own node, because there will be only one row. Therefore, when that is known beforehand a simple IF...THEN statement can be placed around certain lines of code to eliminate the extra node. Alternatively, you could use the recordset RecordCount property to determine the number of records. Here is the altered Convert class file (Covert2.cls), and the final result when it is run (Ex5.xml), a full fledged order summary that is easy to read. Note that this example was run using the second of the three SQL statements.

    Now the component is ready to handle any kind of data, using a couple of different techniques to mold the XML. The only drawback I can see to this is the presence of duplicate data caused by the data shaping RELATE command. Because there must be a link between the two tables, we get the same data in both the parent and the child recordsets. However, this is somewhat offset by placing the child data in an attribute. Furthermore, since the links between tables will usually be an identifying integer (as they were here), this inconvenience is small considering the XSL stylesheet does not have to display it. This is a small trade off considering the scalable nature of the component.

    Hopefully now you will find it easy to take advantage of this component and write some SQL scripts that fully utilizes its abilities. I'll be back in a week or so with part 3, applying an XSLT stylesheet. The stylesheet will take advantage of the XML document structure and provide a sleek interface to view and manage the data.

    Happy Programming!


  • Download the VB Project files in ZIP format

    Related Articles:

  • Customizing the COMponent to Create an XML Structure
  • XML Article Listing
  • Data Shaping
  • Advanced Data Shaping Techniques

    Richard Chisholm is the Webmaster of a large California law firm, as well as an independent developer. He runs a website dedicated to blues music in his spare time.

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