XML Support in ADO 2.5

[Previous] [Next]

Because the XML support in ADO 2.5 is so important, Microsoft is making ADO 2.5 available for downloading from a Web site in addition to including it in the Windows 2000 box. Presumably this is the last time a system software package such as ADO will be made available in such a way. In the future, you'll probably have to install a new version of Windows to get access to a new version of ADO. Microsoft really goes for XML and wants you to be able to take advantage of the XML functionality in ADO, even if you don't upgrade to Windows 2000 right away.

Converting a Simple Recordset to XML

Let's begin the exercises of this chapter by converting a very simple ADO recordset to XML. We'll ask ADO to fetch a recordset containing one record only, identified by a unique ID value. Then we'll have the recordset converted to XML, saving the XML document in a Visual Basic string variable. This simple exercise is all it takes to get a grip on XML conversion, and we're going to prove it by repeating the exercise using less simple recordsets.

Just to avoid any misunderstandings, the exercises performed in this chapter require version 2.5 of ADO. Version 2.1 has simple XML functionality, allowing you to save a nonhierarchical recordset to a file after first converting it to an XML document or, as it's sometimes called, an XML data set. We're going far beyond that in this chapter, saving hierarchical as well as nonhierarchical recordsets not only to files but also, which is more interesting, to string variables. You can't, however, save an ADO recordset as an XML data set directly to a string variable: you must first save it to an instance of the new Stream object that's part of ADO 2.5. The Stream object can hold any kind of byte stream, be it a stream of characters or a stream of binary data, for example, that represents a picture.

Let's see what you have to do to fetch a recordset from the database and convert it to an XML document when saving it in a Visual Basic string variable. We won't leave the horse racing business; let's start by selecting data about a single trainer from the database. You've already seen that our architecture defines specific fetching classes and that most of these classes have a GetById method. Let's use such a method to fetch data about trainer Michael Kahn. This means we must call the GetById method, sending it Michael's ID, which is 16, as a call argument. Assuming that the objFetcher variable refers to an object of the TrainerFetcher class and that the value of the lngTrainerId variable is 16, we'll call the TrainerFetcher object using the following call syntax:

 strXML = objFetcher.GetById(lngTrainerId) 

Naturally, we expect this call to return an XML document, and we're saving that document in the strXML variable, declared as a Visual Basic string. The following code snippet shows the signature of the GetById function and also the private variable declarations made by the method:

 Public Function GetById(lngTrainerId As Long) As String Dim rs As Recordset, strXML As String Dim strSQL As String, st As Stream 

As you might expect, the first variable declared is an ordinary recordset variable. Also as expected, the second one is a string, and the name clearly implies that it's going to hold the XML version of the recordset, just as the third variable holds the SQL statement that's going to be sent to the database. The fourth variable is declared as a Stream, the new object included with ADO 2.5.

The next block of code defines the SQL statement and saves it in the strSQL variable. You'll find no surprises in this part either; it's exactly the same as it would have been if we hadn't contemplated XML at all. A string constant is used to define the main part of the SQL statement; this constant is concatenated with the content of the lngTrainerId variable received as the method's only call argument:

 strSQL = "SELECT TrainerId, Firstname, Lastname, " & _ "Amateur FROM Trainers WHERE TrainerId = " & lngTrainerId 

The next code block establishes, configures, and opens an ADO recordset. We could have used an ADO Command object to do this, but to make the code as simple as possible we decided not to. Notice that this code block, like the ones we've already seen, is exactly the same as it would have been without XML:

 Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockReadOnly rs.Source = strSQL rs.ActiveConnection = _ "PROVIDER=SQLOLEDB.1;SERVER=<Servername>;UID=sa; _ PWD=;DATABASE=MSPress_ADBRacing" rs.Open 

After the execution of this code segment, we should have a recordset containing data about the selected trainer. The next task is to convert that recordset to XML. The goal is to assign the XML document to a string variable and to return that string variable to the method's client. But as an intermediate step, we must first save the XML document in a Stream object: to that end, we have the st variable declared as a reference to such a Stream object. Here's the code that first creates the Stream object and then saves the content of the recordset to it as XML:

 Set st = CreateObject("ADODB.Stream") rs.Save st, adPersistXML 

All that remains is to read the Stream object's text in order to return the XML text to the client. As you would expect, the Stream object exposes a ReadText method. The following code uses that method to read the XML text saved in the object into the strXML variable. The last step is to save the XML text into an implicit local variable bearing the same name as the method itself. In Visual Basic, this is the same as saying to the method, "Here's what you return." To be sure that all the text is saved to the GetById variable, which represents the function's return value, you need to set the Stream object's current position to the beginning of the text. You do that by setting the object's Position property to 0. Here's the code that finishes the work of the method:

 st.Position = 0 strXML = st.ReadText GetById = strXML 

You could simplify this code block somewhat by just skipping the use of the strXML variable. Once you get used to it, the following version of the same code block is just as clear as and a trifle faster to execute than the preceding version.

 st.Position = 0 GetById = st.ReadText 

Putting all this code together renders the full code for the method. The parts of the code that achieve the conversion to XML appear in boldface type. As you see when you look at this code, very little extra work is needed to create the XML document:

 Public Function GetById(lngTrainerId As Long) As String Dim rs As Recordset Dim strSQL As String, st As Stream strSQL = "SELECT TrainerId, Firstname, Lastname, " & _ "Amateur FROM Trainers WHERE TrainerId = " & lngTrainerId Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockReadOnly rs.Source = strSQL rs.ActiveConnection = _ "PROVIDER=SQLOLEDB.1;SERVER=<Servername>;UID=sa; _ PWD=;DATABASE=MSPress_ADBRacing" rs.Open  Set st = CreateObject("ADODB.Stream") rs.Save st, adPersistXML st.Position = 0 GetById = st.ReadText End Function 

Figure 21-1 shows, in a text box belonging to a test application written in Visual Basic, the complete XML document that's rendered by calling the GetById method, asking for the ID, first and last names, and amateur/pro status of trainer Michael Kahn. Notice the XML-Data schema that precedes the data in the document.

Take a close look at the element structure of the document shown in Figure 21-1. The overall structure is interesting because it allows the schema and the data described by the schema to live in the same document. Recall from Chapter 20, "An XML Overview," that XML won't allow an XML-Data schema and the data to live in the same document because that would create a situation in which the document has two root elements. The first root element would be the schema element, and the second would be the element containing the document's data. Microsoft has solved this problem by introducing a wrapper root element, the <xml> element. Here's an overview of the structure of the document shown in Figure 21-1:

 <xml> <s:schema> </s:schema> <rs:data> <z:row...attributes of row.../> </rs:data> </xml> 

Figure 21-1. The complete XML document rendered by the GetById method.

The preceding overview makes it easy to see the structure of the document. It also highlights the fact that the document contains three different namespaces. As you'll see, there's also a fourth namespace, not present in the overview but apparent in Figure 21-1:

• The s: namespace. This namespace represents the XML-Data schema language, and it's referenced as a Globally Unique Identifier (GUID)—see the very first declaration line in Figure 21-1.
• The rs: namespace, which of course represents ADO recordsets, converted to XML. This namespace is declared on the third line in Figure 21-1. This namespace defines all the names you can use for a converted ADO recordset.
• The z: namespace, which represents a rowset in a converted ADO recordset. In this document, there's only one data row; but in our next example, you'll see that each row in a multirow document is represented by a z:row element belonging to this namespace.
• The dt: namespace, which is the namespace not mentioned in the preceding overview. This namespace is the data type namespace, used in the schema. Recall that XML-Data can represent many more data types than Document Type Definition (DTD) can. Looking at the schema's attribute list, you can easily see three examples of data types: int, string, and boolean. You can also see that each dt:type is matched with an rs:dbtype; for example, rs:dbtype='str' matches dt:datatype='string'. And there's more. Every attribute is also specified with a relevant mix of dt:maxLength, rs:maybenull, rs:writeunknown, and rs:fixedlength values.

The last element of the XML document shown in Figure 21-1 is the rs:data element with a number of z:row elements in it. In Figure 21-1, the number of rows is 1 only, as can be seen by the following snippet of code:

 <z:row TrainerId='16'Firstname='Michael' Lastname='Kahn' Amateur='False'/> 

Actions summarized

Let's just review what we've done so far in order to get an XML document from the database and ADO. Here's what we've done:

• Established an ADO recordset in the usual way.
• Declared and created a Stream object.
• Used the recordset's Save method to save the structure and content of the recordset to the Stream object created; used the adPersistXML option when saving.
• Set the Stream object's Position property to 0. This action positions the byte pointer to the beginning of the stream held by the object.
• Used the Stream object's ReadText method to read the byte stream, which in the case of XML is a text stream, into a variable used to return the XML document to the client. This variable can be an ordinary variable such as strXML, used in our example, or an implicit local variable representing the function's return value, such as GetById, also used in our example.

Converting Multirow Recordsets

It almost goes without saying that the only differences between a multirow recordset and the one we've just shown you are the SQL statement and the result. Everything else, including the schema, is the same. Here's an example of a method in the TrainerFetcher class; this method takes a name pattern as its only argument, and it returns a string containing an XML document that includes zero, one, or several trainer records:

 Public Function GetByLastname(strCriteria As String) As String Dim rs As Recordset Dim strSQL As String, st As Stream strSQL = "SELECT TrainerId, Firstname, Lastname, " & _ "Amateur FROM Trainers WHERE Lastname LIKE '" & _ strCriteria & "%' ORDER BY Lastname" Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockReadOnly rs.Source = strSQL rs.ActiveConnection = "PROVIDER=SQLOLEDB.1;SERVER=<Servername>;UID=sa; _ PWD=;DATABASE=MSPress_ADBRacing" rs.Open Set st = CreateObject("ADODB.Stream") rs.Save st, adPersistXML st.Position = 0 GetByLastname = st.ReadText End Function 

The elements of this method's code that are different from our first examples are printed in boldface type. As you can see, only the name of the method, its argument, and the SQL statement differ. Everything else is the same. This similarity, by the way, makes most of the code in this method and the GetById method that we showed you previously candidates for a generalized routine that both methods, and many other methods too, could call to get the result you want. Such a design would simplify such methods as GetByLastname and GetById. Here's an example of a method simplified in the way we just described:

 Public Function GetByLastname(strCriteria As String) As String Dim strSQL As String, xmlFetcher As xmlGeneral.xmlADOServices Set xmlFetcher = CreateObject("xmlGeneral.xmlADOServices") strSQL = "SELECT TrainerId, Firstname, Lastname, " & _ "Amateur FROM Trainers WHERE Lastname LIKE '" & _ strCriteria & "%' ORDER BY Lastname" GetByLastname = xmlFetcher.GetXML(strSQL) End Function 

Figure 21-2 shows an XML document converted from a multirow ADO recordset that was created by the GetByLastName method. To highlight the data, making it easier for you to see the result of the call, we've scrolled the screen to hide the schema. When calling the method, we defined the strCriteria argument so that the method would fetch data about every trainer whose last name begins with "Gusta."

Figure 21-2. The rs:data element of this XML document contains every trainer who has a last name beginning with "Gusta."

As you can see from the declaration of the xmlFetcher variable in the preceding code, we've set up the xmlGeneral project for generalized XML routines. This project contains a class named xmlADOServices that's specifically meant to provide ADO services that return or receive XML documents as arguments. This class has the GetXML method, called by the last version of our GetByLastname method just described. Here's the code for the GetXML method in the xmlADOServices class:

 Public Function GetXML(strSQL As String) As String Dim rs As Recordset, st As Stream Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockReadOnly rs.Source = strSQL rs.ActiveConnection = "Provider=MSDataShape.1; _ Persist Security Info=True; _ Data Source=<Servername>;UID=sa;PWD=; _ Initial Catalog=MSPress_ADBRacing; _ Data Provider=SQLOLEDB.1" rs.Open Set st = CreateObject("ADODB.Stream") rs.Save st, adPersistXML st.Position = 0 GetXML = st.ReadText End Function 

A class such as xmlADOServices with a method such as GetXML makes it easier to create other methods that must return an XML document with its data coming from a database. If you install such a class in a library package (MTS) or in a library application (COM+), its instances of the class will run in-process—the method calls will be fast.

Converting a Hierarchical Recordset to XML

Converting a hierarchical recordset to XML isn't much more difficult. The only difference is that you must create an ADO SHAPE command rather than just an SQL statement to fetch the data. The SHAPE command is well featured in Chapter 19, "Hierarchical Recordsets—Pros and Cons," so let's take a look at an example right away. Here's a SHAPE command that supposedly fetches all the trainers with a specified last name pattern; for each trainer, there'll be a list of all the horses trained by the trainer:

 strShape = "SHAPE "{SELECT TrainerId, Firstname, Lastname " & _ "FROM Trainers WHERE Lastname LIKE '" strCriteria & _ "%' ORDER BY Lastname, Firstname} AS Trainers " & _ "APPEND (" & "{SELECT HorseId, Horsename, BredIn, Sex, " & _ "Birthyear, Trainer FROM Horses WHERE Trainer IN " & _ "(SELECT TrainerId FROM Trainers " & _ WHERE Lastname LIKE '" strCriteria & "%') " & _ "ORDER BY Horsename} AS Horses " & _ "RELATE 'TrainerId' TO 'Trainer') AS Horses" 

You could simplify such a SHAPE command by replacing the string constants that represent the SQL statements with variables, where each variable contains one SQL SELECT statement as follows:

 strSQLParent = " SELECT TrainerId, Firstname, Lastname " & _ "FROM Trainers WHERE Lastname LIKE '" & strCriteria & "%' " & _ "ORDER BY Lastname, Firstname" strSQLChildSub = " (SELECT TrainerId FROM Trainers " & _ "WHERE Lastname LIKE '" & strCriteria & "%')" strSQLChild = " SELECT HorseId, Horsename, BredIn, Sex, " & _ "Birthyear, Trainer FROM Horses WHERE Trainer IN " & _ strSQLChildSub & _ " ORDER BY Horsename " strShape = " SHAPE {" & strSQLParent & "} AS Trainers " & _ "APPEND ({" & strSQLChild & "} As Horses " & _ "RELATE 'TrainerId' TO 'Trainer') AS Horses" 

This code is simpler because it manages each separate SQL statement in its own variable and builds the SHAPE command from these variables rather than from the constants used in the foregoing example. If you put the preceding code in a Visual Basic project and call the method these variables are in, sending the 'Gusta' string as a call argument, and if you run that code step by step, you'll find the following values to be saved in each of the variables used:

 strSQLParent = " SELECT TrainerId, Firstname, Lastname FROM Trainers WHERE Lastname LIKE 'Gusta%' ORDER BY Lastname, Firstname" strSQLChildSub = " (SELECT TrainerId FROM Trainers WHERE Lastname LIKE 'Gusta%')" strSQLChild = " SELECT HorseId, Horsename, BredIn, Sex, Birthyear, Trainer FROM Horses WHERE Trainer IN (SELECT TrainerId FROM Trainers WHERE Lastname LIKE 'Gusta%') ORDER BY Horsename " strShape = " SHAPE {SELECT TrainerId, Firstname, Lastname FROM Trainers WHERE Lastname LIKE 'Gusta%' ORDER BY Lastname, Firstname} AS Trainers APPEND ({SELECT HorseId, Horsename, BredIn, Sex, Birthyear, Trainer FROM Horses WHERE Trainer IN (SELECT TrainerId FROM Trainers WHERE Lastname LIKE 'Gusta%') ORDER BY Horsename } AS Horses RELATE 'TrainerId' TO 'Trainer') AS Horses" 

Figure 21-3 shows the result of sending this SHAPE command to ADO; having ADO send the respective SQL statements to SQL Server, Oracle, or what have you; creating a hierarchical recordset; and converting that hierarchical recordset to XML. Figure 21-3 shows only part of the resultant XML document, but that part should be enough to let you know how it all works.

Figure 21-3. A hierarchical ADO recordset, converted to an XML document by ADO 2.5.