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.
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
Dim XMLCData As IXMLDOMCDATASection
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!
Attachments:
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.
