Download the code (7KB)

XML Database Applications

ust as with HTML, in the next few years almost every programmer will need a solid knowledge of XML. Last month (July 1999) I showed you how to build Microsoft® Internet Explorer 5.0-based Active Server Pages (ASP) that use XML and XSL, creating a powerful Web site design.     This approach has several potential disadvantages. For one thing, it requires a XML/XSL-enabled browser, which currently limits your audience to Internet Explorer 5.0. And while sending pure XML code back to the browser sounds exciting, what really matters is how you can employ XML at the server to design the content of your Web site. Any XML parser can help to produce HTML pages. You can use server-side components, ASP script code, or a XSL processor to actually create the HTML code for the browser. In fact, this is what I've been showing you—take a look at the June and July 1999 installments of Cutting Edge for details.     XML is a metalanguage that helps you describe just about any kind of content. But XML's uses extend past the Web to areas like database content. This month, I'll dig around the XML support built into ActiveX Data Objects (ADO) 2.1 and turn my attention to XML's capabilities in desktop applications. I'll demonstrate how you can develop a Visual Basic® 6.0-based application that provides different views of the same data with a single click. Stylesheets and Data Views       The concept of the Extended Stylesheet Language (XSL) is akin to the idea of a view. Given a set of XML data, an XSL file describes a way to render it through HTML. This means that you could associate many XSL files to the same XML document to provide different HTML-based representations of the same set of data. Figure 1 shows how a Win32®-based application can get data displayed in various viewable formats. If you're familiar with MFC development and the document/view model, this should be nothing new. Today, you can make the user interface of your desktop applications XML-driven.

Figure 1: ADO to XML Conversion

There are two major advantages to this approach. First, you can manipulate data in a language- and platform-independent manner. Second, you get an extensible and mostly codeless way to provide different views of the same data. Features like this are always appreciated by users.     In Figure 1, I depict a general layout that doesn't bind you to any product of any vendor. I've used ADO and recordsets as an example. After all, you just need a tool to convert native data to an XML format, plus a way to represent it in HTML. However, if you choose to convert XML to HTML using code (no matter whether you choose JScript®, Visual Basic, or C++), you bind the solution to a particular language and reduce its extensibility. If you instead choose to exploit the support for XSL in Internet Explorer 5.0, you can use the built-in XSL processor to produce the final output from the XML stream. Building the application in a parametric way gives you real codeless extensibility; you can write a new XSL script at any time, have the program access it, and immediately have the embedded XSL processor process it.     Need a tool that converts data into XML? Choose ADO as your database access technology and you get XML for free. As of ADO 2.1 (see http://www.microsoft.com/data/ado), you can persist your recordsets to an XML format:

If Dir(XML_FILE) = "" Then rs.Save XML_FILE, adPersistXML End If

The constant adPersistXML tells the Save method to save either the format or the content of a recordset using an XML schema. If you indicate an existing file, an error is raised. Displaying XML Data       Support for XSL in Internet Explorer 5.0 is based on the latest working draft produced by the World Wide Web Consortium (W3C). Microsoft has announced its commitment to track the evolution of the spec and to update the processor as warranted. (See http://www.w3.org/TR/WD-xsl for late-breaking news about the XSL document standard.) Even though Internet Explorer implements a draft version of the XSL spec, this isn't a big problem. You can design XML/XSL-enabled code today and have it continue working even if the syntax of XSL changes over time. As long as you keep XSL files separate from programs, as distinct files or as embedded resources, you should be okay. I'll demonstrate this technique in the rest of this column.     Let's build an application in Visual Basic that accesses some data through an ADO interface. The recordset that's returned will be available for display in a number of different formats. The program supports a datagrid view and the standard Internet Explorer 5.0 XML view. By writing XSL files you can add as many new views as you want. While describing the architecture and the implementation of this application, I'll take a close look at the XML schema ADO utilizes to persist recordsets. This month's source code archive (available from the link at the top of this article) contains two files, table.xsl and tree.xsl, that provide a tabular and hierarchical view of the data. The Architecture of the Program        Figure 2 illustrates the architecture of the program. It executes a query via ADO and obtains a recordset. This simple behavior is typical of most programs that do data access that involves retrieving a recordset or an equivalent data structure—much like a resultset in the Remote Data Object (RDO). If you have a program that needs to display the content of a recordset, then look at what XML and XSL lets you obtain with minimal effort.

Figure 2: Architecture of the Sample App

Once you retrieve a recordset, displaying it through a datagrid control such as the Microsoft DataGrid Control 6.0 is as easy as the following code:

Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient queryString = "select * from Employees" rs.Open queryString, "NorthWind" rs.ActiveConnection = Nothing Set DataGrid1.DataSource = rs

The result is a typical screen (like Figure 3) that shows employee names from the NorthWind database.

Figure 3: Retrieved Recordsets

In most cases, such a view isn't sufficient; you need tighter control over the formatting of the fields. For example, you might want to ignore the EmployeeID field. A more user-friendly way of presenting the same data might combine employee ID, first name, and last name in a unique string like this:

1 - Ms. Nancy Davolio

In addition, the full table of records might be shown in a tabular or hierarchical format. Providing two or more views can only make people happier. In other words, your users might prefer a view that's similar to a smart datagrid, or they might want a hierarchical structure where only the name is initially visible. To see the rest of the information, the user must click on the name and expand the node—the data shown remains the same. The importance of ad-hoc data reporting is also well emphasized by Johnny Papa and Charles Caison in their article "Ad Hoc Web Reporting with ADO 2.0" (MIND, December 1998). From Recordsets to XML       While different views of the same data are a feature that users notice and appreciate, the same can't be said for the programmers who actually have to code it! You need a way to describe the desired output that is somewhat independent from the program itself. XML and XSL combine to make a good solution that's very handy and easy-to-implement, if you can take advantage of the Internet Explorer 5.0 WebBrowser component and its direct browsing capability.     Before going any further with the integration of XSL and Visual Basic-based programs, let me discuss how ADO recordsets are converted to XML. The key function is the Save method, which is exposed by the ADODB Recordset object. It has the following prototype:

Sub Save([FileName As String], _ [PersistFormat As PersistFormatEnum = adPersistADTG])

Both parameters are optional, but you need to specify the file name the first time you save a recordset to disk. If a filter is active when the method executes, then only the visible records will be saved. The method leaves both the recordset and the file open. The file is closed automatically when you close the recordset through the Close method. This is designed to let you easily save the most recent changes to the recordset. At present there are only two choices for the format: adPersistADTG and adPersistXML. Once a recordset is saved to disk you can read it back using the Open method:

rs.open "c:\myrs.xml"

Of course, not all XML files match up well with recordsets. Whatever the extension of the file, the Open method attempts to load it by matching the bytes it reads with the structure of the two formats it supports: ADTG (Advanced Data Table Gram, a proprietary binary format) and XML. Figure 4 shows a valid XML schema for loading recordsets from disk. It must contain the description of the recordset structure followed by the records themselves. The <s:Schema> tag defines the various fields by name, type, and other attributes. The <rs:data> tag collects all the rows in the recordset, and <z:row> is the actual record. The field values are attributes of this tag. These features give you another option when you need to create custom recordsets. You can exploit the Append method of the ADO Fields collection to create connectionless recordsets, or you can write an XML file that follows that schema and then call Open to load it.

Figure 5: Recordset-based XML

If you create an XML file from a recordset, then attempt to load it with Internet Explorer 5.0, you get a view similar to the one shown in Figure 5. Admittedly, this output is not what a typical user expects. Still, while not very practical when deploying real applications, this capability of Internet Explorer turns out to be very helpful during XML development to quickly test the validity of documents. To make users happy you need to add another layer of processing to the XML source code through extensible stylesheets. From XML to HTML       The ADO library doesn't add any XSL directives to the XML files it produces. This doesn't mean you can't write a generic XSL file to change the way data are displayed. Figure 6 illustrates a simple XSL document; it transforms XML data into an HTML table, with the result shown in Figure 7. To automatically visualize any XML version of an ADO recordset in a tabular format, just add the following line at the top of the file:

<?xml-stylesheet type="text/xsl" href="Simple.xsl"?>

The simple.xsl file shown in Figure 6 must be available in the same path as the XML document. It is composed of two nested loops. The first one enumerates all the attributes of the element type called row. As you might expect, a row renders a row of data; its attributes are nothing more than the fields. The names of these attributes (identified by @type) are displayed as the caption of any column.     The second loop adds as many lines as needed to output the various records.

<xsl:for-each select="xml/rs:data/z:row"> <tr> <xsl:for-each select="@*"> <td class="stdText" valign="top"> <xsl:value-of match="@*"/> </td> </xsl:for-each> </tr> </xsl:for-each>

Notice the syntax necessary to enumerate all the attributes of a given node. I need to catch all the attributes of any <z:row> element. The string @* denotes any attribute (an attribute's name is always preceded by @). To use the value, the <xsl:value-of> node must also set its match attribute to @*.     By design, such a file is simple, but it would have been hard to write it to be any more complex. It is meant to render a generic and self-describing recordset. There's no assumption you can reasonably make about its format and the role of its fields. At most, you could decide to employ a different CSS style according to the declared type of the field. For example, you might want to put all the memo fields in an expandable structure (like a combobox) and draw their text in italic.     If you want to render a recordset in a user-friendly way, you must assume something about each field. In other words, you must create application-specific XSL files that understand what each field represents and the best way for the application to render it. For example, if you're using the NorthWind database, you might assume that EmployeeID, TitleOfCourtesy, FirstName, and LastName are related fields that can be displayed as a whole. The Skeleton of the Application        Figure 8 shows my sample application, which offers a number of different views. The Report view defaults to the DataGrid Control. In addition, the program includes a WebBrowser component. The two are switched on and off according to the type of view you select.

Figure 8: Available Views

If strMode = "Report" Then grid.Move x, y, w, h webb.Visible = False grid.Visible = True Else ' all the other XSL-based views webb.Move x, y, w, h grid.Visible = False webb.Visible = True End If

The Internet Explorer 5.0 Default view creates a local recordset file in XML format and then forces the WebBrowser component to navigate to it. The Simple view creates a slightly different recordset file that links to the simple.xsl stylesheet. More precisely, it first creates the standard XML file and then edits it by adding an XSL processing instruction:

Set f2 = fso.CreateTextFile(strFile, True) f2.WriteLine "<?xml-stylesheet type=" & _ Quote("text/xsl") & _ " href=" & _ Quote(strXSL) & _ " ?>" f2.Write strOrigFile f2.Close

The application automatically loads all the .xsl files that it finds in a specified path (say, C:\Styles).

Sub LoadAvailableStyles(ByVal strPath As String) Dim file As String Dim displayName As String file = Dir(strPath & "*.xsl") Do While file <> "" displayName = Left(file, InStr(1, file, ".") - 1) cboView.AddItem displayName file = Dir Loop End Sub

Each style's name is added to the combobox; when you select them, the loaded recordset is converted to XML and linked to the appropriate stylesheet. Adding a new view is as easy as writing a new XSL file for the XML representation of the recordset and putting it in the given path. The application automatically navigates to it, and the direct browsing capability of Internet Explorer 5.0 does the rest:

' Converts to XML and add the proper XSL link strFile = SaveDataToXML(rs) webb.Navigate strFile

If you find that the default Internet Explorer 5.0 XML format for recordsets is a bit more complex than you need, you can simplify things by using a more compact, custom XML format. The difference is that the simpler format will assume a given recordset schema and therefore has no need to repeat that information internally. This may be a perfectly reasonable assumption in the context of a real application. Figure 9 shows a single record rendered in the simpler format I've chosen. A Couple of Custom Views       The views available in Figure 8 include a Table view and a Tree view. The only difference between table.xsl and simple.xsl is that they assume a different XML schema and, maybe more importantly, table.xsl organizes the data better (see Figure 10). All the fields except for personal notes are compacted in a single column that presents data to the user in a friendlier manner. The use of a specialized view—an XSL script that provides an application-oriented view of the recordset—also has the advantage of letting you employ different fonts and apply particular formatting styles to the various fields. This happens because the author of the XSL file now knows which role each specific field plays in the context of the application.

Figure 10: A Table View of the XML

To demonstrate the power of the XML/XSL pair in designing document/view, data-oriented applications, let me explain yet another XSL document capable of providing a completely new data representation (see Figure 11). As you can see, the same information is now presented in a simple, hierarchical format. All the names are rendered as links that expand and collapse to show additional information. The links are tied directly to script code through the javascript: protocol.

Figure 11: A Hierarchical View of the XML

<a href="javascript:Expand('a1')">

The argument passed to Expand is the ID of the block to show or hide. The additional information is maintained in a <DIV> section whose display state is toggled as the result of the user's clicks:

<script language="jscript"> function Expand(id) { state = document.all(id).style.display; if (state == "") document.all(id).style.display = "none"; else document.all(id).style.display = ""; }

Figure 12 presents the full source code for the tree.xsl file. A XML-driven Document/View Model       With the Internet Explorer 5.0 WebBrowser control as the engine for viewing your data, you can use XML and XSL to separate content from presentation. If you aren't using the WebBrowser component, you can still obtain the same result. What you'll need is an XSL processor to produce HTML code and some component to display HTML pages (either an older version of WebBrowser or any commercially available component that does this).     In this scenario, the XSL role is that of a tagged language (not a programming language) that describes how the document, which is expressed through XML data, must be rendered. To add a new view, just add a new file written in that tagged language to your application. The result is exciting: a Web-oriented version of the document/view model that MFC developers are familiar with. XSL and CSS       What's the difference between XSL and CSS, you're probably asking? Both acronyms include the word "stylesheet," so they're clearly related in some way. They are similar because both define styles that apply to certain elements. The particular elements, however, are far different. XSL applies to entire XML documents, while CSS affects just the typographical style of HTML tags.     XSL is a markup language that processes a file, while CSS are a set of attributes that qualifies an element in a markup language (like HTML). Furthermore, XSL may use CSS styles while producing the output HTML code from a stream of XML data. Although XSL and CSS seem well-suited to complement each other (perhaps CSS support could be built into XSL in the future), they are at present two independent technologies. What You Need to Run the Code       This month's code includes a Visual Basic 6.0 project that executes a query on the Microsoft Access 97 NorthWind database. (I've used a DSN of NW.) It assumes that you're running Internet Explorer 5.0 and ADO 2.1. Once you've run the program, any .xsl files in the C:\Styles directory (or whatever directory you specify) are detected and used to provide additional views of the data. It goes without saying that those XSL files must be compliant with the XML recordset formats discussed in this column.

From the August 1999 issue of Microsoft Internet Developer.