December 1999

Use XML to Transfer Data

by Jeffrey P. McManus

What you need: Visual Basic 5.0 or 6.0 Microsoft XML Document Object Model Component Access 2000 or SQL Server 7.0 ActiveX Data Objects (ADO) 2.1

Extensible Markup Language (XML) is the natural choice as the mode of data transfer for this application. XML is an open Internet specification, so other developers don't have to learn a proprietary API when they want to customize the look and behavior of the data. Equally important, vendor support for XML is nothing short of phenomenal; most vendors who handle data involve themselves with XML in some way (including Microsoft).

In this column, I want to show off a couple tips and tricks I've learned while working on this project. First and foremost, you don't need Internet Explorer 5.0 (IE5) to use the Microsoft XML parser COM component. You can download and install the component from the Microsoft XML site at msdn.microsoft.com/downloads/tools/xmlparser/xmlparser.asp. (If you already have IE5 on your machine, don't bother installing the download—it won't install. This free, redistributable component is designed to target machines that need the XML component but don't already have IE5 installed.) Understand that although Microsoft makes some extensions to the proposed XML Internet standard, these extensions don't affect the way the XML data is stored or parsed—just because you're using the Microsoft XML parser doesn't mean your stuff won't work with non-Microsoft browsers.

	Figure 1 Slow it Down. Click here.

My original plan was to have multiple configurations of the system that display in different ways, depending on the user's browser configuration. Specifically, I planned to support generic HTML as well as IE5, which displays Extensible Stylesheet Language (XSL)-formatted XML directly in the browser (see Figure 1).

Light prototyping demonstrated that IE's new XSL-formatted XML features aren't nearly as fast as converting the XML data into formatted HTML tables on the server side and sending the HTML to the brow-ser. Compatibility is another issue: Although IE5 supports XML in the browser today and IE4 supported it with a client-side Java applet, the much-delayed Netscape 5.0 browser won't support XSL-formatted XML at all. Instead, Netscape plans to support XML formatted with Cascading Style Sheets in the browser—an older and less powerful technique. Support for XML in the browser is probably something you want to wait on if you're planning an application for the public Internet; if you're writing an intranet app and can standardize on IE5, you might want to look into XSL-formatted XML.

Because I couldn't afford the incompatibility or the speed hit, I restructured the application around server-side script that stores user information in XML, sends it to script, and uses Active Server Pages (ASP) to render it in pure HTML. I might need to port the application to some other technology in the future, so I didn't want to take advantage of any exotic tricks, particularly on the client side—everything needed to be standards-based and straightforward.

Transfer Information
I ran into another interesting design consideration when building this application; it doesn't have direct bearing on XML, but it has a potential impact on applications that utilize it. XML is designed to transfer information generically; for example, it can conduct business-to-business data transfer. So what happens when your business needs to send my business information on a product with a particular ID number, but our company already has a product with that ID number and changing or generating a new ID for the product isn't an option?

The answer is to identify every database object using GUIDs: 128-byte random numbers guaranteed to be unique across all applications. You can find a VB algorithm for building GUIDs using the CoCreateGuid API function in the Microsoft Knowledge Base. You can download a class I wrote called CGuid that uses a modified version of this code here. Use the uniqueidentifier datatype to create a GUID field in SQL Server; use a numeric datatype with the field size set to "Replication ID" in Access 97 or Access 2000 (this doesn't have anything to do with replication; it's just the dumbed-down Access term for a GUID). There are some drawbacks to GUIDs: The long numbers are difficult to read and enter, and the data field consumes more space than the long integers traditionally used as primary keys. However, there are also some advantages to GUIDs: For example, the standard practice of retrieving the identity value from the database engine after insertion becomes a nonissue when you insert a new record into the database, because you can generate GUID identity values for new records in the middle tier.

Note that both Access and SQL Server require particular formatting when you express a GUID: You must put hyphens between groups of digits, and you must delimit the GUID with single quotes and curly brackets. My CGuid class takes care of that formatting.

Figure 2 Browse However You Want. Click here.

Build the XML
The user navigates to scripted Web pages that kick off requests to a data transfer component in my project's architecture (see Figure 2). The data is saved in a traditional format (in the form of fields in a record) when the user saves it to the database, but it's also converted into XML format. I envisioned the information would be inserted and updated far less frequently than retrieved; for your applications, you might want to consider converting to and from XML at the time the data is requested rather than when it's saved. The Save method of the CPerson object demonstrates how to do this (see Listing 1).

The procedure uses straightforward SQL Data Manipulation Language to do its work, except for the part that builds the XML. You can use a simple private routine called FormatXML to build the XML. This routine returns an XML representation of the properties found in the CPerson object:

Private Function FormatXML() As String
' Provides an XML representation of 
' the person.
Dim strReturn As String

   strReturn = "<PERSON>" & vbCrLf
   strReturn = strReturn & _
      "<FIRSTNAME>" & FirstName & _
      "</FIRSTNAME>" & vbCrLf
   strReturn = strReturn & _
      "<LASTNAME>" & LastName & _
      "</LASTNAME>" & vbCrLf
   strReturn = strReturn & _
      "</PERSON>" & vbCrLf

   FormatXML = strReturn

End Function

This is a simplistic example; you could probably envision more sophisticated XML representations of hierarchical data (such as multiple phone listings or timesheet entries per employee). Nothing very sophisticated is going on here, aside from the fact that you must remember to include opening and closing tags for fields with XML data. XML is picky, unlike HTML, which doesn't always require closing tags.

Translate Data
You merely need to whip out the contents of the XMLData field (exposed publicly in the CPerson object through the XMLData property), then use the Microsoft XML parser component to translate the data into HTML when you retrieve data from the database. You can write an ASP script to do this task:

<%

   ' Get data for this user
   Set MyPerson = CreateObject( _
      "xperson.CPerson")
   MyPerson.GetData "{92A20576-5AD2- _
      11D3-86D6-6006008BD812}"

   ' Now that you've got the XML data, 
   ' display it in the browser.

   ' Create the XML document
   Set xd = CreateObject( _
      "microsoft.xmldom")
   xd.loadXML MyPerson.XMLData

   ' Kill the user obj; don't need it 
   ' anymore
   Set MyPerson = Nothing

   ' Get the top-level node  
   Set ndPerson = _
      xd.selectSingleNode("PERSON")

   ' Get the individual properties  
   Set ndCurrent = ndPerson. _
      selectSingleNode("FIRSTNAME")
   Response.Write "First Name: " & _

      ndCurrent.Text & "<BR>"
   Set ndCurrent = ndPerson. _
      selectSingleNode("LASTNAME")
   Response.Write "Last Name: " & _
      ndCurrent.Text

%>

You can't assume the data will be in any particular order when you parse the document. This is why I use the selectSingleNode method of the XML DOM Node object to pick the nodes out by name instead of iterating through them under the PERSON node one node at a time.

Although this method works, it's not as interactive as you might like and it doesn't allow users to pick which customer they want to select. You need a way to get a list of customers out of the database. Depending on how much data is stored in the XMLData field, it might be simplest to use a SQL statement:

SELECT XMLData
FROM tblPerson

You never want to expose the person's ID in the user interface directly; instead, embed it in the script's URL as a QueryString:

http://localhost/scripts/ _
   xmlperson.asp?ID={92A20576-5AD2- _
   11D3-86D6-6006008BD812}

When you call xmlperson.asp, it uses the expression Request.QueryString("ID") to determine which person to look up. This expression becomes the parameter passed to the GetData method of the CPerson object.

Avoid writing code that accesses the database directly—instead, always use a single set of reader and writer functions (in this case, the GetData and Save methods of the CPerson object) to get and retrieve data. You might wind up with a difference between the true value of each person's data and the XML representation of that person's data if you make changes to the database fields directly. However, there's no chance of this happening because the Save method saves both representations at once.

XML has a lot of potential. The XML-related technologies available to Visual Basic developers, including the ability to send XML data streams to the browser in ADO 2.5, have promise. But it's nice to know you can write your own code easily to store and retrieve XML today with a minimum of fuss.


Jeffrey P. McManus is the president of Insero Corp., a provider of online information-sharing software and services. He is the author of Database Access with Visual Basic 6 (Sams), a how-to guide demonstrating the database techniques available to the VB developer. E-mail Jeffrey at jpm@insero.com, or reach him on the Web at www.redblazer.com.