Roll Your Own XML Generator

What you need: Visual Basic 6.0 SQL Server 6.5 or later ActiveX Data Objects 2.1 (to test new XML extension to ADO; not used in the project itself)

Consider what you must do to update information on a typical Web site. You might use text to handle importing and exporting information, ActiveX Data Objects (ADO) for data communication, and Active Server Pages (ASP) to generate HTML dynamically. Learning and keeping abreast of these technologies—not to mention all the other stuff you might need to know—is time-consuming. XML enables you to do all three of these tasks in essentially the same way.

In this article, we're going to forget about the hype and concentrate on what XML can do for you right now. For example, you can use it to perform validation without returning to the database, handle all types of message exchange between applications, and update your Web site dynamically with new information as your data changes. Unfortunately, the primitive state of the tools you use to create and work with XML often complicates implementing solutions that depend on it. So, we'll explain how to create the XML Project, which contains apps that generate, parse, and display XML data. Then we'll show you how to use it.

	Generate XML From VB. Click here

XML isn't difficult to use—the solution described in this article lets you take advantage of XML without understanding XML at the syntax level—but it does require acquainting yourself with a raft of new acronyms and terms (see the sidebar, "Glossary of Terms"). Note that this article's value extends far beyond what you see in the magazine (see the sidebar, "What You Get"). The downloadable source code associated with this article includes the source for and compiled versions of the XML Project, including the XML generator (XMLCreator.vbp), the VB desktop XML viewer (VBXMLTree.vbp), the DHTML thin-client XML viewer (XMLViewer.vbp), and the Cascading Style Sheet (CSS) designer for creating style sheets visually (CSS.vbp).

You can create XML documents in one of three ways. First, you can create them by hand, writing tags and code to show the data and the content of the document. Second, you can create them using an appropriate programming tool. VB doesn't include anything like this, so we'll explain how to roll your own XML generator. Finally, you can generate XML documents directly from ADO. ADO 2.1's SaveRecordset method includes a new parameter that lets you save a recordset as an ADO proprietary format or as an XML document format.

Creating the XML generator and viewers is only part of the battle; you also need to use the information the XML Project generates for you. Fortunately, this doesn't require a huge learning curve. The XML Project lets you grab data from any SQL database and create an XML document using your existing database schema. This makes building dynamic XML documents quick and easy. You can then send these documents to desktop or Web clients, where users can interact with and manipulate them. It also means you don't need to re-create an HTML document with new data every time you add data to your database; simply rerun your query to bind and display the new data.

Let the Generator Do the Work
Building an XML document by hand is like ignoring the VB IDE and creating VB forms in Notepad. The XML Project provides an easier and more elegant way to do this.

Combining the project's generator and two viewer apps gives you the power to generate and view XML documents. We created the first part of the project, the generator, using straight VB. You can find the full source for the generator (XMLCreator.vbp)—and the rest of the project—here.

The second part, the pair of XML viewers, requires a straight-VB app for the desktop client (VBXMLTree.vbp) and a VB DHTML app for the Web client (XMLViewer.vbp). You might wonder why you need to build two different clients; after all, you could use the Web client locally or remotely. We chose to implement the project this way for a couple reasons. First, a traditional desktop client still gives you better performance where appropriate. Second, we thought it might be fun to compare and contrast traditional VB and VB DHTML apps that accomplish essentially the same task, but from different perspectives. The implementation also illustrates an important point: You can view XML documents equally well from a traditional desktop client or a thin Web client. XML's promise for Internet applications garners most of the attention, but XML holds a lot of promise in traditional client/server programming as well, especially when it comes to sharing data between applications.

	Figure 1 Generate XML From VB. Click here

The XML generator uses your existing database schema to identify table relationships, constructing a query by setting field values for each table from your existing table schema (see Figure 1). The generator then creates an XML document for you, absolving you of the need to understand XML tags and other low-level details. Note that you can study the XML generator's logic for building an XML document to acquire a better understanding of how XML works; one interesting aspect of this approach is that you can learn as much or as little about XML as you want. Understanding XML well can help you eke out better performance, but the barrier to getting started with and benefiting from XML is quite low.

This example assumes you're using SQL Server, but you can substitute another database with a little effort. The article's example also employs SQL-DMO, which defines all aspects of Microsoft SQL Server's database engine and services. This technology provides more than 60 objects and more than 1,000 properties and methods. The sample project employs only a handful of objects, but they prove sufficient to illustrate how to use the database tables, column information, and table-relationship validation.

Connect to the database using the SQL-DMO's SQLServer object, then iterate through the database collection to retrieve all the available databases for the selected server (see Listing 1). After you get a list of available databases, select the database you want to generate XML documents from. Next, iterate through the collection of tables within the database, select the table you want, and perform a final iteration on it to obtain the fields within the table. Finally, loop through the end of the collection to obtain all the information you need from the database.

Figure 2 Create a DTD and an XML Document. Click here

Let the Magic Begin
The XML Project lets you build an XML DTD and document from table relationships visually (see Figure 2). When you display a table in the XML generator, it shows you a list of all the fields that belong to that table. You can select the fields you want to make available in an XML document once you select a table. Use the system stored procedure sp_fkeys to prompt the XML generator to retrieve the table relationships from the database. Select the list of fields and the criteria to generate the query, then click on the Create DTD button to build the XML DTD and document (see Listing 2). The XML Project creates the DTD by identifying the participating tables and their relationships according to the proper DTD format. It constructs the XML document by iterating through the treeview and reading the attributes of every table and the participating fields.

You now have an XML document and its DTD. You can use these documents in a variety of ways, depending on your needs. However, you probably want to take a closer look at these items before you set about putting them to work. Do this using a cascading style sheet (CSS), which tells a browser how to format and display your XML data. The source for this article includes a CSS designer app you can use to visually create a style sheet for use with an XML document.

You can also display an XML document by binding an HTML table to the data source provided by the Microsoft XML DSO. The MSDN library includes several examples that illustrate this technique. Your real goal is to use an XML document in conjunction with VB. You can do this from either of two project types: a standard EXE or a DHTML project. We'll explain how to create the two viewer apps using each of these project types.

XML's ability to generate its own tags makes it much more flexible than HTML and its static tags, but you need a way to describe what these tags do before someone else can manipulate your data. Document Type Definitions (DTDs) solve this problem. DTDs accompany XML documents and incorporate information about the allowed sequence and nesting of tags, formats, and attributes, including their types and defaults. DTDs and XML make it possible for the user manipulating XML to understand the data in XML. Fortunately, the XML generator creates the necessary DTDs that accompany your XML documents when you lay out your documents in the app's CSS visual editor.

	Figure 3 Parse and Display an XML Document. Click here

The source for this article includes a standard EXE project (VBXMLTree.exe) to display the contents of an XML document using a simple treeview (see Figure 3). Use this app to parse the XML document and create a tree that represents the XML data hierarchy. Don't worry—you don't have to write your own XML parser because Microsoft provides you with an object model you can program against: the Microsoft XML 1.0 library (msxml.dll). This DLL uses a URL to load the XML document, then exposes the document through a standard hierarchical object model that is the same as the XML object model for IE4. Parsing an XML page requires three key interfaces (see Table 1).

Commence Parsing
The parsing process begins when you click on the desktop viewer's Build Tree button (see Listing 3). Create a new MSXML.XMLDocument and set its URL property to the URL the user supplies. Next, pass the document root object to the BuildTree function, which builds the treeview recursively. Its parameters include the XML element you want to add and a string ID that represents the element's parent ID, which is an empty string for the root of the tree. An XML element object can represent different types of XML components, not just the XML element. For example, it can represent text inside an XML element:

<TITLE>The Autobiography of Benjamin 
   Franklin</TITLE>

MSXML exposes this text as an XMLElement (of type ELEMENT) called TITLE with one child XMLElement (of type TEXT) that contains this text:

The Autobiography of Benjamin Franklin

The BuildTree code accounts for this by checking the element type to determine how it should treat the element. It adds a tree node for each XMLElement, setting its label to the element's tagName property and appending a name=value list of the element's attributes:

BOOK: (GENRE=autobiography)

BuildTree then calls itself recursively for each element in the children collection of the processed XMLElement as soon as you add a node to the tree. If the type of XMLElement is XMLELEMTYPE_TEXT, the treeview appends the parent node's text label to the text's contents. For example, the treeview appends this information to the title:

TITLE: The Autobiography of Benjamin Franklin

This gives you a treeview where each node represents an element in the original XML document. This simple yet powerful technique lets you view the structure and contents of any XML document. At this point, you can create a simple or hierarchical recordset and save it as an XML document using the adPersistXML option—assuming you've already downloaded the latest ADO 2.1 libraries.

Figure 4 Display XML Documents Inside a Browser. Click here

So far, you've built an XML generator application and a VB desktop XML viewer application. Next, you want to create a DHTML Web-client XML viewer (XMLViewer.vbp) that asks you for an XML document, then parses and displays its contents in a hierarchy of nested lists. VB6 lets you create VB-controlled DHTML pages, and you create the Web client using a VB DHTML app (see Figure 4). This powerful mechanism lets you manipulate the contents of a DHTML document dynamically with VB's syntax.

Use the innerHTML property of a SPAN element to add lists and list items to the DHTML document dynamically. Note that changes to the innerHTML property are parsed and processed immediately. This means you must make all the changes to an element's innerHTML in one VB statement. For example, look at the innerHTML property of this statement after it executes:

SomeElement.innerHTML="<UL>" 

SomeElement doesn't equal <UL>, but <UL></UL>. The browser discovers that innerHTML is a list with a missing </UL> tag when it parses the statement, so it kindly appends the tag for you. This can cause some awkward results if you attempt to compose a list over several VB statements. You can work around this by composing the new HTML in a string variable called sNewHTML. Remember to set this value when you finish building your lists:

XMLView.innerHTML=sNewHTML

	Figure 5 Design Style Sheets Visually. Click here

Format XML Content in a Browser
The Web-client viewer does its job, but doesn't present the information as neatly as the app's desktop version. So, we looked for mechanisms that let you display XML as attractively in a browser as you can on the desktop, settling on CSS's style sheets. Style sheets give you great control over the look-and-feel of your Web client. They let you change the fonts, spacing, borders, size, colors, and almost anything else on your Web page. The VB-authored CSS designer included with the source enables you to manipulate the look-and-feel of your Web client visually (see Figure 5). For example, the CSS designer shows a style sheet's available styles in a simple, easy-to-use format. It also tells you the valid values for each element. Save your style sheet to a file, then embed or link it to a Web page. You can load existing style sheets by parsing them. Note that the designer has a few limitations: It does not support style containment, IDs, shorthand tags, or pseudo-classes. It also performs best with style sheets you create using the CSS designer, but does load a variety of layouts.

One important step remains: examining and testing the code itself. The Web-client viewer's default file (XMLViewer.htm) initially displays an input field for typing a URL to an XML document. When you click on the Load XML button, the Web-client viewer calls the LoadXMLPage sub. This sub creates an MSXML.XMLDocument and assigns its URL property to the URL you supplied. It then starts at the topmost list and calls BuildTree to add items to this list. BuildTree takes only one parameter: the XML element to be added. This differs from the desktop viewer app, which uses a second parameter to represent the parent node ID. You cannot add the second parameter to BuildTree because the items you add are stored sequentially in the order you add them. Therefore, you cannot say "add this item and make it a child of that parent item."

You get around this limitation by examining all children of the current XML element (called oElement in the code) and searching for children with this type while processing oElement itself:

type= XMLELEMTYPE_TEXT 

If you find a child of type XMLELEMTYPE_TEXT, append its text to oElement's label. Next, add this child to the HTML page as a list item with this description:

class="node if oElement has node children"

Otherwise, add the child with this statement:

class="leaf"

You define this pair of classes in the DHTML document's <STYLE> section. The difference between the two classes is in the pointer shape and text color. This distinction lets you convey visually that the user can expand the list items of a Node class, but not those of a Leaf class. Call BuildTree recursively for each child of type XMLELEMTYPE_ELEMENT if oElement has children. BuildTree creates the list item's label the same way the VBXMLTree desktop viewer creates the treeview node's text: It appends a name=value list of the element's attributes to the element's tag name. This produces results similar to VBXMLTree's, with one exception: The XML data is represented as a set of nested HTML lists.

That's about it. The DHTML example illustrates how to manipulate XML documents dynamically to deliver a rich, interactive experience for the user. For example, you can let the user search on certain keywords or certain element values, and you can implement these searches in VB in conjunction with the MSXML object model. Exposing the XML document to your VB code lets you take XML far beyond the realm of simply displaying your XML data.

You can put this and other techniques described in this article to work in many ways (see the sidebar, "Put XML to Work"). The XML Project apps and source will get you off to a good start; displaying and manipulating your data is as simple as modifying the source to fit your circumstances.

So far, you have seen how you can work with XML by building applications such as the ones we provided in this project. However, you might wonder how XML fits into big picture, and what this means to your future development. XML appears to be the missing link between various flavors of integration, whether business-to-business or application-to-application. The problem is that XML cannot provide all the needed functionality by itself. If the development world intends to push for XML as the standard for integration, other services and servers must facilitate the interaction between the various DTDs and XML documents.

We suspect the continued popularity of XML will force Microsoft to adopt new extensions and native XML support in its tools, as in the recent ADO enhancements. The next step might be to incorporate similar enhancements in VB's Data Environment Designer (DED), through a mechanism like the one described in this article. For example, the DED might enable you to generate XML documents rather than VB forms. Of course, we have neither heard from nor communicated with Microsoft about its overall XML direction, but the company wouldn't find it difficult to incorporate XML support into future versions of its tools. We also wouldn't be surprised to see a new type of XML server similar to MTS or Message Queue to handle application integration using XML. Because we're feeling generous, we'll even offer Microsoft a name for it: Enterprise XML Integration and Translation Server—EXIT Server, for short.


Ash Rofail is a principal engineer and architect at Best Software. He is also the author of Building N-Tier Applications with COM and Visual Basic 6.0 (John Wiley & Sons), as well as an instructor at George Washington University. Reach him at Ash_Rofail@bestsoftware.com.

Yasser Shohoud is a senior software engineer at Best Software. He is responsible for building enterprise intranet solutions using Visual Basic, Visual InterDev, Visual C++, and SQL Server. Reach Yasser at Yasser_Shohoud@bestsoftware.com.