XML Tips & Tricks

Downloads
15867.zip

Editor's Note: Welcome to Rich Rollman's XML Q&A column, which you'll now find every month in SQL Server Magazine's XML Explorer section. Send your XML questions to Rich at [email protected].

As the author of XML Q&A, I'll use my extensive experience with SQL Server's XML technologies—and XML in general—to answer your questions about using XML with your database to build robust, extensible applications. Whether you're using XML now, planning to use XML, or simply curious about how XML technology can help you build applications, I hope you'll find this column interesting, informative, and fun!

Our company uses SQL Server 7.0 and 6.5 for our largest databases and Microsoft Access 2000 for our smaller databases. Using Visual Basic (VB) applications, we've been successfully exchanging data between SQL Server-based databases and Access-based databases. How can we achieve this required heterogeneous connectivity by using ADO with XML?

At the root of your question lies the real challenge: determining whether XML is an appropriate solution for your situation. XML is the industry buzz of the moment, and companies are using it for everything from modeling business processes to building Web pages. But most important, companies are using XML as a data-transfer format so that applications can leverage XML's simplicity, flexibility, and extensibility. Here are some guidelines that will help you decide whether XML is appropriate for your application.

If your application requires moving data between enterprises, XML is a good solution. XML lets you send data across the Internet and through firewalls by using the standard HTTP protocol. XML is also a good choice if your application needs to move data between hardware or software platforms (OSs). XML is not machine- or OS-specific. Finally, XML is a good choice if you simply want to ensure that your application or data source is robust even if the data schema changes. XML enables your application to be extensible because you access the XML-formatted data by using element and attribute names instead of offsets, which structured programming languages use. Note that using element and attribute names to access data in XML is similar to accessing fields by name within a SQL Server table. If you have one or more of these application requirements, then XML is a good solution for you.

Next, you need to determine the best place to generate or consume XML within your application, which is an important decision because using XML incurs processing overhead. This overhead manifests itself in different ways depending on whether you're consuming or producing XML. For XML consumers, you need—at minimum—a method to parse the XML. You'll likely also need an object model to access the parsed data. For XML producers, converting native data formats to XML incurs overhead. On the middle tier, the processing overhead is crucial. If your middle-tier program manipulates, performs computations on, or reformats the data and your database is inside the firewall, XML shouldn't be your first choice. In this case, requesting a normal result set from the database and using traditional data-access methodologies to perform application processing will be more efficient. After processing is complete, the middle-tier application can generate the XML output. Using traditional data-access methodologies avoids the overhead of generating XML in the database as well as the overhead of parsing the XML and building an object model on the middle tier. The only potential benefit from generating XML on the middle tier is that you can loosely couple your middle-tier application and your database, but the cost is significant.

Now, let's apply these usage guidelines to the scenario you describe in your question. You don't seem to have a requirement to move data between enterprises, across the Internet, or through firewalls. So, unless you're trying to make your applications more extensible, XML isn't a good choice for your scenario. Traditional data-access technologies will meet your needs. But to demonstrate the value of XML, let's assume that you need to make your application extensible. You can upgrade to SQL Server 2000 and use its integrated XML support. This is your best option because it provides the most flexibility. If you must access your data from SQL Server 7.0 or 6.5, then check out the SQL Server XML technology preview at http://msdn.microsoft .com/downloads/samples/internet/xml/sqlxml/default.asp. This preview provides functionality similar to the XML support in SQL Server 2000, but the preview works with SQL Server 7.0 and 6.5. (For information about the differences between SQL Server 2000's XML integration and Microsoft's XML technology preview, see Bob Beauchemin, "The XML Files," September 2000.)

You have fewer options for obtaining XML from Access databases. You can use traditional methods of building XML, such as script or C++, that work well but are labor-intensive and fragile with respect to schema changes. But these methods are nothing new. An alternative is to use the ADO persistence provider, included with ADO 2.1 and later versions. Let's look at using the persistence provider. The ADO persistence provider outputs XML in the ADO persistence format, which is a fixed schema for representing data within an ADO Recordset object. You can write code that generates the XML automatically from an ADO Recordset object by calling the Save method on the Recordset object. For example, Listing 1, page 75, shows the VB code to select the first two customer records from the Northwind database and return the results in XML format, which Figure 1 shows.

Using the ADO persistence provider has a limitation: Because the ADO persistence provider is a fixed schema, you can't define your own element and attribute names or define how the elements nest within one another. But if you can live with the schema that the ADO persistence provider generates, then using this provider is a simple solution that works for many databases. If you can't live with this format, your alternatives are limited to writing the XML conversion yourself or using Extensible Style Language Transformations (XSLT) to transform the ADO persistence format to the grammar of your choice. Listing 2 shows a simple XSLT. Figure 2 shows the XML output that the XSLT in Listing 2 produces. The downside to using XSLT with the ADO persistence provider is that you'll incur the double overhead that I described in the middle-tier processing discussion. (For more information about using ADO with XML, see Dan Fox's article "ADO and XML," page 69.)