Working with XML Recordsets

In last week's column, I introduced XML and ADO integration. In that column, I explained that ADO provides the ability to automatically persist recordsets in XML. The Recordset object exposes the Save method, which can serialize to disk the in-memory representation of the recordset. The following Visual Basic (VB) code shows how to save a recordset to disk using XML as the persistence format:

Dim oRS as New ADODB.Recordset' Now populates the recordsetoRS.Save "file.xml", adPersistXML

The adPersistXML constant equals 1. The XML file that ADO creates contains more than the XML counterpart of the records' content. In fact, the XML data stream comprises two parts: schema and actual content. The schema is the full description of the XML schema used to describe the columns' values. This information block equates to the column metadata information, which is specific to a database server.

Below is the typical XML file header that ADO generates:

The code declares four namespaces, each with its own Uniform Resource Name (URN). The s namespace is the data schema, the dt namespace is the data types for table columns, the rs namespace is the recordset data, and the z namespace defines the tags that contain the actual data--column by column and record by record.

Below is an example of the actual XML content:

The XML snippet above represents a recordset that has four columns: title, author, issue, and keyword. The field information is stored within the tag and includes name, type, and other column attributes--such as its maximum length and whether it can contain null values.

You can refer to the MSDN documentation for more information, or you can investigate the structure of the XML recordset yourself by creating a simple piece of VB code that saves a recordset to a file:

Dim oRS As New ADODB.RecordsetoRS.Open "select * from employees", _            "uid=sa;Provider=SQLOLEDB;" & _            "Initial Catalog=Northwind;"oRS.Save "c:myfile.xml", adPersistXML

All you need to run this code is any version of SQL Server with the standard sample Northwind database.

As I mentioned in my last column, there are some tricky aspects to XML and ADO integration. Before you call the Save method to persist a recordset to XML, you should ensure that the file you're about to write to doesn't already exist. If it does exist and you want to overwrite it, you must delete it first. To do so in VB, include the following statements:

If Dir(strFile) <> "" Then     Kill strFileEnd If

By design, the Save method doesn't close either the ADO recordset or the destination file, so you can continue working with the recordset and save your most recent changes. The destination file remains open until you close the recordset. The destination element is in a read-only state for other applications during this time. If Save is called while an asynchronous operation (fetch, update, execute) is in progress, the Save method waits for the operation to complete. Because the destination file is open, when you subsequently call the Save method to update the persisted recordset, avoid specifying its name unless you want to run into a nasty "File already exists" runtime error.

Records are saved starting from the first row. When the method terminates, the current row position moves to the first row of the recordset. If the recordset has an active filter, only the rows visible through the filter are saved to disk.

To reload a previously saved recordset, use the following VB code:

oRS.Open "file.xml",,,, adCmdFile

The adCmdFile constant equals 256 and informs the ADO runtime about the nature of the first argument (256 specifies a disk file).

You can modify, add, and delete records from an XML-based recordset. What's interesting to notice, though, is that the XML stream always retains the value of the original record. This fact is important because it lets you properly manage and resolve possible conflicts when the recordset is submitted to the server to update the database.

Because a recordset can be created from a disk file, you can create the recordset on a different platform or with a non-Windows application. An XML file, in fact, is a universal object, manageable from a number of different environments.