Data Services Components and XML

In the last several tutorials, we have discussed user and business services components. In this tutorial, we will look at data services components. Recall that data services components are used to exchange business information with business partners, integrate data from other systems, and store and retrieve business data. We'll examine two Microsoft technologies that can be used to build data services components that can perform these tasks. We will start by examining ActiveX Data Objects (ADO) 2.5 and its ability to work with XML data. ADO 2.5 simplifies programmatic access to data. In the second half of this tutorial, we'll discuss two Internet Server Application Programming Interface (ISAPI) extensions for Microsoft Internet Information Server (IIS). The first ISAPI extension allows you to retrieve data directly from a SQL Server 6.5 or 7.0 database in XML format using a Web browser. The second extension enables IIS to pass the XML directly to the client computer. It can also automatically transform XML documents to other formats using an XSL document on the server if the client does not have Internet Explorer 5 installed. Although the second ISAPI extension won't be used to build data services components that can access data, it's included in this tutorial so that it can be discussed along with the first ISAPI extension. Let's begin by looking at the XML features found in ADO 2.5.

Using ADO 2.5, you can read data from nearly any data source, place the data into an ADO recordset (which is similar to a virtual table containing the data in memory), and transform the data to XML. Once the data has been transformed to XML, it can be placed into a data stream and used as output to various sources, such as a file, the ADO ASP Response object (which is used to return information to the Web client), and the XML DOM. Outputting the data as XML can be accomplished by using the COM IStream interface. The IStream interface is an interface designed to support reading and writing data to ADO Stream objects. ADO 2.5 requires support for the IStream interface.

On the other hand, XML data can be used as a data source of a read-only or read/write recordset in ADO 2.5. For example, you can use ADO 2.5 in an ASP page on a Web server to retrieve data from a database by placing the data into an ADO recordset. The data will then be transferred as XML output to the ASP Response object using ADO 2.5. Once the XML data arrives on the client, it can be read into an ADO recordset on the client using DHTML. Using the client-side ADO recordset and DHTML, the user can read and update the data. Let's look at several examples of inputting and outputting data as XML in ADO 2.5 to see how this works.

In this example, we will retrieve data from the SQL Server 7.0 Northwind Traders database and save the data as XML in a text file. We'll use ADO 2.5 in a Visual Basic application to perform this task. To create the example application, follow these steps:

1. Open Visual Basic, create a standard EXE application, and change the name of the default form to frmADOXML.
2. Choose Reference from the Project menu, and add a reference to Microsoft ActiveX Data Objects 2.5 Library.
3. Add a command button called cmdSave to the form with a caption Save.
4. Add the following code to the click event handler of the command button cmdSave:

   Private Sub cmdSave_Click()     Dim objNWRecordset As ADODB.Recordset     Dim objNWConnection As ADODB.Connection     Set objNWRecordset = New ADODB.Recordset     Set objNWConnection = New ADODB.Connection     objNWConnection.CursorLocation = adUseClient     'You will need to replace IES-FUJI with the appropriate data     'source in the following statement.     objNWConnection.Open "Provider=SQLOLEDB.1; " & _   "Integrated Security=SSPI;Persist Security Info=False;" & _   "User ID=sa;Initial Catalog=Northwind;" "Data Source=IES-FUJI"     objNWRecordset.CursorLocation = adUseClient     objNWRecordset.CursorType = adOpenStatic     Set objNWRecordset.ActiveConnection = objNWConnection     objNWRecordset.Open "Products"     'Save the recordset to a file as XML.     objNWRecordset.Save "C:\Products.xml", adPersistXML End Sub

This code initially creates an ADO Connection object called objNWConnection and a Recordset object called objNWRecordset, and then sets the properties for these objects and opens them. A Connection object provides a connection to any data source. A Recordset object is a virtual table in memory that contains the data that is retrieved from a data source. The CursorLocation property of the Recordset object determines whether the data will be located on the client or on the server. The CursorLocation property also determines whether the connection must be maintained with the database (server cursor) or the connection can be broken (client cursor) while creating a disconnected recordset. The Open method of the ADO Connection object contains the connection string as a parameter. The connection string includes the catalog, which is the database that is going to be used, the data source, which is the name of the SQL Server, and the user ID, which is a valid user name to use when opening the connection. This connection string is connecting to a SQL Server database. You will have to change the name of the data source to the name of your SQL Server database that contains the Northwind Traders database.

The ADO Connection object connects to the Northwind Traders database, and the Recordset object connects to the Products table of the Northwind Traders database. Once this is done, the Save method of the Recordset object is called to save the data as XML.

As you can see, the Save method uses the adPersistXML parameter to save the data as XML. The XML file that is created will have two main sections. The first section contains a BizTalk schema for the data, and the second section contains the actual data. There are four namespace prefixes that are used in the file. The first namespace prefix is s, which is used to prefix the schema definition for the data. The second namespace prefix is dt, which is used for the datatype definitions in the schema. The third namespace prefix is rs, which references the properties and methods of the ADO recordset. The fourth namespace prefix is z, which references the actual data. The Products.xml XML file that was generated from the above code looks as follows:

  <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
     xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
     xmlns:rs='urn:schemas-microsoft-com:rowset'
     xmlns:z='#RowsetSchema'>
  <s:Schema id='RowsetSchema'>
     <s:ElementType name='row' content='eltOnly'>
        <s:AttributeType name='ProductID' rs:number='1'>
           <s:datatype dt:type='int' dt:maxLength='4'
              rs:precision='10' rs:fixedlength='true' 
              rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='ProductName' rs:number='2' 
           s:writeunknown='true'>
           <s:datatype dt:type='string' dt:maxLength='40' 
              rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='SupplierID' rs:number='3' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' 
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='CategoryID' rs:number='4' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' 
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='QuantityPerUnit' rs:number='5' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='string' dt:maxLength='20'/>
        </s:AttributeType>
        <s:AttributeType name='UnitPrice' rs:number='6' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i8' rs:dbtype='currency' 
              dt:maxLength='8' rs:precision='19' 
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='UnitsInStock' rs:number='7' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' 
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='UnitsOnOrder' rs:number='8' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' 
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='ReorderLevel' rs:number='9' 
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2' 
              rs:precision='5' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='Discontinued' rs:number='10' 
           rs:writeunknown='true'>
           <s:datatype dt:type='boolean' dt:maxLength='2' 
              rs:fixedlength='true' rs:maybenull='false'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
     </s:ElementType>
  </s:Schema>
  <rs:data>
  <z:row ProductID='1' ProductName='Chai' SupplierID='1' 
     CategoryID='1' QuantityPerUnit='10 boxes x 20 bags' 
     UnitPrice='18' UnitsInStock='39' UnitsOnOrder='0' 
     ReorderLevel='10' Discontinued='False'/>
  <z:row ProductID='2' ProductName='Chang' SupplierID='1' 
     CategoryID='1' QuantityPerUnit='24 - 12 oz bottles'
     UnitPrice='19' UnitsInStock='17' UnitsOnOrder='40' 
     ReorderLevel='25' Discontinued='False'/>
  
  </rs:data>
  </xml>
  

Using ADO 2.5, we have created an XML document that contains the schema and the data for the Products table of the Northwind Traders database.

In some cases, you will have to make changes to the generated data before it can be used as XML. For example, if there is an invalid character in the column name, such as a space, you will have to change the name to a valid XML name. You would do this by changing the name attribute and adding an rs:name attribute that contains the original name of the field. Thus, if you had a column in the database called Shipper Name you could end up with the following AttributeType:

  <s:AttributeType name='Shipper Name' rs:number='9' 
     rs:nullable='true' rs:writeunknown='true'>
     <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50' 
        rs:fixedlength='true'/>
  </s:AttributeType>
  

You would have to change the AttributeType as follows:

  <s:AttributeType name='ShipperName' rs:name='Shipper Name'
     rs:number='9' rs:nullable='true' rs:writeunknown='true'>
     <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50' 
        rs:fixedlength='true'/>
  </s:AttributeType>
  

This document can then be presented in Internet Explorer 5 as XML or transformed to XHTML or other formats using an XSL document.

The original file that is generated can be used only to create a read-only ADO recordset. If you want to create an updatable client-side disconnected recordset, you must add an rs:updatable attribute to the ElementType definition. A client-side disconnected recordset has no connection to the original data source. A user can review, edit, delete, update, and add records to the recordset, but a connection to the database must be reestablished in order for the changes to be saved to the database. To save the changes, the UpdateBatch method of an ADO recordset must be called after the disconnected recordset is reconnected to the database. The UpdateBatch method is used to send multiple recordset updates to the server in one call.

To make the data updatable, you would change the ElementType definition to the following:

  <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
  

Reading the XML data generated by ADO 2.5 is just as easy as outputting the data. To examine how to input data as XML using ADO 2.5 in the example application, add another command button to the frmADOXML form and call it cmdRetrieve with a caption Retrieve&Add. Add the following code to the click event handler of the command button cmdRetrieve:

  Private Sub cmdRetrieve_Click()
      Dim objNWRecordset As ADODB.Recordset
      Set objNWRecordset = New ADODB.Recordset
      
      'Open the recordset to a file as XML.
      objNWRecordset.Open "C:\Products.XML", Options:=adCmdFile
      'Add a new record.
      objNWRecordset.AddNew
      objNWRecordset.Fields("ProductName") = "Test"
      
  End Sub
  

Setting Options to adCmdFile tells ADO that this data will be coming from a regular file and not from a database.

Once you add a new record, edit a record, or delete a record, you must call the Update method of the ADO recordset. Each time you call the Update method, the updated record is marked within the recordset. If you make changes to the recordset and save the changes to a file, you can see the actual changes. These changes are being made only to the recordset, not to the actual data in the database, as there is no connection to the database.

Now that we have seen how to save data as XML and how to open XML data using ADO, we will look at how to make changes to the XML data. We will start by writing code to open the Products.xml file we created, and then we'll make changes to the data. Once the changes are made, we will call the Update method of the recordset. When the changes are complete, we will save the new data to a file called ProductsUpdate.xml. First add another command button called cmdMakeChanges to the form with a caption Make Changes. Add the following code to the click event handler of the command button cmdMakeChanges:

  Private Sub cmdMakeChanges_Click()
      Dim objNWRecordset As ADODB.Recordset
      Set objNWRecordset = New ADODB.Recordset
      'Open the recordset to a file as XML
      objNWRecordset.Open "C:\Products.XML", Options:=adCmdFile
      objNWRecordset.Fields("ProductName") = "Test"
      objNWRecordset.Update
      objNWRecordset.MoveLast
      objNWRecordset.Delete
      objNWRecordset.Update
      objNWRecordset.Filter = adFilterPendingRecords
      objNWRecordset.Save "c:\ProductsUpdate.xml", adPersistXML
  End Sub
  

In this case, we have changed the product name of the first record to Test and deleted the last record. The changed recordset is then saved to the ProductUpdate.xml file. You can see the following new additions in the ProductUpdate.xml file after clicking the Make Changes command button:

  
  <rs:update>
     <rs:original>
        <z:row ProductID='1' ProductName='Chai' SupplierID='1' 
           CategoryID='1' QuantityPerUnit='10 boxes x 20 bags' 
           UnitPrice='18' UnitsInStock='39' UnitsOnOrder='0' 
           ReorderLevel='10' Discontinued='False'/>
     </rs:original>
        <z:row ProductName='Test'/>
  </rs:update>
  
  <rs:delete>
     <z:row ProductID='77' ProductName='Original Frankfurter gr¨¹ne Soße' 
        SupplierID='12' CategoryID='2' QuantityPerUnit='12 boxes'
        UnitPrice='13' UnitsInStock='32' UnitsOnOrder='0' 
        ReorderLevel='15' Discontinued='False'/>
  </rs:delete>
  

With the updated XML file, ADO can reconstruct a recordset that has the original and new values for fields that are edited and can determine which rows have been deleted. Now let's look at how we would actually update the data source.

Currently, the data we have been working with has been saved only to a local file. This data will still have to be saved to the original data source (the Northwind Traders database, in this case). To save the updated data, add another command button to the form called cmdSaveUpdate with a caption Save Update and add the following code to the click event handler of the command button cmdSaveUpdate:

  Private Sub cmdSaveUpdate_Click()
  Dim objNWRecordset As ADODB.Recordset
  Dim objNWConnection As ADODB.Connection
  Dim objXMLRecordset As ADODB.Recordset
  Dim lngFieldCounter As Long

  Set objNWRecordset = New ADODB.Recordset
  Set objXMLRecordset = New ADODB.Recordset
  Set objNWConnection = New ADODB.Connection


  objNWConnection.CursorLocation = adUseServer
  'You will need to replace IES-FUJI with the appropriate data
  'source in the following statement.
  objNWConnection.Open _
     "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
     "Persist Security Info=False;" & _
     "User ID=sa;Initial Catalog=Northwind;Data Source=IES-FUJI"
  objNWRecordset.CursorLocation = adUseServer
  objNWRecordset.CursorType = adOpenDynamic
  objNWRecordset.LockType = adLockPessimistic
  Set objNWRecordset.ActiveConnection = objNWConnection
  objXMLRecordset.Open "C:\ProductsUpdate.XML", Options:=adCmdFile
  objXMLRecordset.Filter = adFilterPendingRecords
  Do Until objXMLRecordset.EOF
     If objXMLRecordset.EditMode <> adEditAdd Then
        objNWRecordset.Open _
           "Select * From Products Where ProductID=" _
           & objXMLRecordset.Fields.Item("ProductID").OriginalValue
        If objXMLRecordset.EditMode = adEditDelete Then
           'Delete
           objNWRecordset.Delete
        Else
           'Edit
        For lngFieldCounter = 0 To objXMLRecordset.Fields.Count-1
              'Can Not Change Primary Key
           If UCase(objXMLRecordset.Fields.Item( _
              lngFieldCounter).Name) _
              <> "PRODUCTID" Then
              objNWRecordset.Fields.Item(lngFieldCounter).Value = _
                 objXMLRecordset.Fields.Item(lngFieldCounter).Value
           End If
        Next
        End If
     Else
        objNWRecordset.Open _
           "Select * From Products Where ProductID=" & 0
        objNWRecordset.AddNew
          'Add New
        For lngFieldCounter = 0 To objXMLRecordset.Fields.Count - 1
           'Auto Increment field for productID
           If UCase(objXMLRecordset.Fields.Item( _
              lngFieldCounter).Name) _
              <> "PRODUCTID" Then
              objNWRecordset.Fields.Item(lngFieldCounter).Value = _
                 objXMLRecordset.Fields.Item(lngFieldCounter).Value
           End If
        Next

     End If
     objNWRecordset.Update
     objNWRecordset.Close
     objXMLRecordset.MoveNext
     Loop
  End Sub
  

Once again, we create a Connection object called objNWConnection to connect to the Northwind Traders database, and a Recordset object called objNWRecordset to hold the data from the ProductsUpdate.xml file. You will need to configure the data source and change the connection string again, just like in the previous example. ObjNWRecordset is used to get a reference to the record that is being updated using a SELECT statement with a WHERE clause.

The second Recordset object called objXMLRecordset is used to retrieve the XML data, which contains the data that has been added, edited, or deleted. You can also get the XML data from an ADO data stream, which we'll cover in the section "Working With Streams" later in this chapter.

Once we have obtained the XML data stored in the objXMLRecordset recordset, we apply a filter so that the only visible records are the ones that have had changes done to them or are new records. We then move through each record in objXMLRecordset that is new or has been changed and retrieve that record from the database using objNWRecordset.

Once we have only the records that are about to be changed, we can perform the correct operations: AddNew, Delete, and Edit. We begin by checking the EditMode property of objXMLRecordset to find out which operation was being done on this record, and then perform the operation accordingly.

If you have worked with disconnected ADO recordsets before, you might have expected that we would use the UpdateBatch method of the ADO recordset. Unfortunately, the disconnected ADO recordset created using XML has no reference to the original table that was used to get the data. Thus, even though you can create an ADO connection to the correct database and set the recordset's ActiveConnection property to this connection, there is simply no way of connecting the recordset to the right table. Because the recordset cannot be connected to the correct table, the UpdateBatch method cannot work. As you can see from the above example, we have created two Recordset objects: ObjXMLRecordset and objNWRecordset.

In the above examples, we have been saving information to data files. It's likely that you will not want to do this for most of your applications. Instead, you can work directly with data streams that can pass the data into a DOM object or pass it back to the client in an ASP Response object. Now we'll look at an example that shows how we can use a stream to place the data directly into the DOM objects.

This example will load data from a text file and place it into an ADO Stream object. The data will then be loaded into a DOM object. Once you have the data in the DOM object, you can do almost anything you want to it, including transforming it with XSL. To place the data into an ADO Stream object, add another command button to the form. Name it cmdStream with a caption Stream and add the following code to the click event handler of the command button cmdStream:

  Private Sub cmdStream _Click()
      Dim objNWRecordset As ADODB.Recordset
      Dim objADOStream As ADODB.Stream
      Dim objDOM As DOMDocument
      Dim strXML As String
      Dim objNWConnection As ADODB.Connection
      Set objADOStream = New ADODB.Stream
      Set objDOM = New DOMDocument
      Set objNWRecordset = New ADODB.Recordset
      Set objNWConnection = New ADODB.Connection
      objNWConnection.CursorLocation = adUseClient
      'You will need to replace IES-FUJI with the appropriate data
      'source in the following statement.
      objNWConnection.Open _
      "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
      "Persist Security Info=False;" & _
      "User ID=sa;Initial Catalog=Northwind;Data Source=IES-FUJI"
      objNWRecordset.CursorLocation = adUseClient
      objNWRecordset.CursorType = adOpenStatic
      Set objNWRecordset.ActiveConnection = objNWConnection
      objNWRecordset.Open "Products"
      objADOStream.Open
      objNWRecordset.Save objADOStream, adPersistXML
      strXML = objADOStream.ReadText
      objDOM.loadXML strXML
  End Sub
  

Just as in our other examples, this code creates an ADO Connection object and a Recordset object. Once the connection to the Northwind Traders database has been made and the recordset contains the data from the Products table, the XML data is saved to an ADO Stream object called objADOStream. The information is passed from the ADO Stream object to a string variable called strXML, and then the XML data is placed into a DOM document object called objDOM using the loadXML method of the document object. In this way, we have opened data from a regular database and passed the data as XML into a DOM document object.

As you can see, the ADO recordset is versatile and can be used on both the client and the server to build powerful data access components in distributed applications.

The SQL Server extension allows you to send a SQL query to a SQL Server 6.5 or 7.0 database through IIS in the HTTP query string of the request and get the data back as XML. The extension will give you a preview of the functionality that will be in SQL Server 2000. You can download this tool from the Microsoft Web site at http://msdn.microsoft.com/xml/articles/xmlsql/sqlxml_prev.asp, where you can find the link Download Microsoft SQL Server XML Technology Preview. Right-click this link and choose Save Target As from the context menu to open the Save As dialog box. You will see the Sqlxmlsetup.exe file in the File Name box. Save this file to the local drive of a server running IIS. To install this extension, follow these steps:

1. Run Sqlxmlsetup.exe. The ISAPI DLL will be copied to your server and a menu entry named XML Technology Preview for SQL Server will be created.
2. Choose Programs from the Start menu, and then choose XML Technology Preview for SQL Server, and then Registration Tool. This will open a vrootmgt MMC snap-in that can be used to set up a SQL database so that the database can be accessed directly through the Web.
3. You will need to create a virtual root on the IIS Web server using the vrootmgt MMC snap-in. As an example of how this works, right click on the Default Web Site, choose New, and then choose Virtual Directory to open the New Virtual Directory Properties window, as shown in Figure 15-1.

   

   Figure 15-1. The New Virtual Directory Properties window.

4. In the General tab, change the default name of the virtual directory to a name such as Northwind and specify a local path to the actual directory that contains the files you want to make accessible through this virtual directory.
5. Click the Security tab. You can choose any of the security mechanisms that are appropriate for your server, including Windows Integrated security and SQL Server account. Select a security mechanism that will give you access to the Northwind Traders database on a SQL Server database that you have access to. If you select the Always Log In As option, you must supply a user ID that has access to the Northwind Traders database.
6. Next select the Datasource tab, choose the correct data source, and pick the Northwind Traders database.
7. Select the Setting tab, and then select Allow URL Queries.
8. Click OK to close the New Virtual Directory Properties window.

You should now be able to access SQL Server in a URL that specifies HTTP as the protocol. Place the following query into the navigation bar in Internet Explorer 5 and get back the appropriate results:

  http://localhost/northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO
  

Figure 15-2 shows what this query returns.

Figure 15-2. The XML data returned from the query.

As you can see, this ISAPI extension allows you to rapidly and easily retrieve data from a SQL Server database. The format of the XML data can be specified in several ways by using the FOR XML clause in the SELECT statement. The FOR XML clause can specify one of the three modes: AUTO, RAW, and EXPLICIT. In Figure 152, we used the AUTO mode to allow the ISAPI extension to format the data as a nested tree. The RAW mode takes the query result and transforms each row in the result set into an XML element with a generic row identifier as the element tag. Figure 15-3 shows what the results would look like using the RAW mode.

Figure 15-3. Using the RAW mode in the query.

The EXPLICIT mode allows you to define the shape of the XML tree that is returned.

You can use a URL and SQL statements to access SQL Server and execute stored procedures. Besides SQL statements, you can also specify templates using a URL. A template is a SQL query string formatted as an XML document. It contains one or more SQL statements. The general format for the query strings are as follows:

  http://NameOfIISServer/
   NameOfVirtualRoot?SQL=SQLQueryString| template=XMLTemplate] 
   [&param=value[&param=value]¡­]
  

or

  http://NameOfIISServer/NameOfVirtualRoot [/filepath]/
   filename.xml [?param=value[&param=value]¡­]
  

If a SQL statement is used, you can use the FOR XML clause and specify one of the three modes mentioned above.

The param value in the above query string is a parameter or a keyword. Keywords can be of three types: contenttype, outputencoding, and _charset_. The contenttype keyword describes the content type of the document that will be returned. The content type can be images such as JPEG and text. The contenttype value will become part of the HTTP header that is returned. The default is text/XML. If you are returning XHTML, you should set conenttype to text/html. If you do not want the browser to perform any formatting, you can use text/plain. For images and other SQL Server binary large object (BLOB) fields, you can use one of the registered MIME types. The registered MIME types can be found at ftp://ftp.isi.edu/in-notes/iana/assignments/media-types/media-types.

The outputencoding keyword is the character set that will be used for the returned document. The default is UTF-8. The _charset_ keyword is the character set for decoding the parameters that are passed in. The default is also UTF-8.

When writing out the queries you must use the defaults for HTML query strings. For example, we used the plus sign (+) for spaces. You will need to use a percentage sign (%) followed by the hex value for the following characters: /, ?, %, #, and &.

You can also specify an XSL file to transform the XML data, as shown in the following URL:

  http://localhost/northwind?sql=SELECT+CompanyName,+ContactName
  +FROM+ Customers+FOR+XML+AUTO&xsl=customer.xsl&contenttype=text/html
  

The file Customer.xsl can be placed in any subdirectory that is part of the virtual root subdirectory tree. The code for the XSL file would look as follows:

  <?xml version="1.0"  encoding="ISO-8859-1" ?>
  <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
     <xsl:template match = "*">
        <xsl:apply-templates />
     </xsl:template>

     <xsl:template match = "Customers">
        <TR>
        <TD><xsl:value-of select = "@CompanyName" /></TD>
        <TD><B><xsl:value-of select = "@ContactName" /></B></TD>
        </TR>
     </xsl:template>

     <xsl:template match = "/">
        <HTML>
        <HEAD>
           <STYLE>th { background-color: #CCCCCC }</STYLE>
        </HEAD>
        <BODY>
           <TABLE border="1" style="width:300;">
              <TR><TH colspan="2">Customers</TH></TR>
              <TR><TH >CompanyName</TH><TH>Contact Name</TH></TR>
              <xsl:apply-templates select = "root" />
           </TABLE>
        </BODY>
        </HTML>
     </xsl:template>
  </xsl:stylesheet>
  

This document will look as shown in figure 15-4.

Figure 15-4. The transformed XML data.

You can execute a stored procedure using the EXECUTE command. For example, the Northwind Traders SQL Server database comes with a stored procedure called CustOrderHist. The stored procedure looks as follows:

  CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
  AS
  SELECT ProductName, Total = SUM(Quantity)
  FROM Products P, [Order Details] OD, Orders O, Customers C
  WHERE C.CustomerID = @CustomerID
  AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID 
  AND OD.ProductID = P.ProductID
  GROUP BY ProductName
  

This stored procedure takes one parameter, the ID of the customer. To execute this query we can use the following HTTP query string:

  http://localhost/northwind?sql=EXECUTE+CustOrderHist+
  "ALFKI"+FOR+XML+AUTO
  

This query gets the order history for the customer with an ID of ALFKI. The results of this query are shown in Figure 15-5.

Figure 15-5. Sales for customer with ID ALFKI.

You can also update data to a database using the SQL ISAPI extension. To perform an update you must create an update gram. An update gram is a template that is sent in an HTML query string. The general format of an update gram is shown below:

  <sql:sync xmlns:sql="urn:schemas-microsoft-com:xml-sql">
     <sql:before>
        <TABLENAME [sql:id="value"] col="value" col="value".../>
     </sql:before>
     <sql:after>
        <TABLENAME [sql:id="value"] [sql:at-identity="value"] 
           col="value" col="value".../>
     </sql:after>
  </sql:sync>
  

Using this format, you can perform inserts, updates, and deletes. When performing an insert you would leave out the before element, when performing a delete you would leave out the after element, and when performing an update you would include both the before and after elements and list the columns that have changed.

For example, to add a new product to the Northwind Traders' Products table we could create the following HTML file called InsertProd.htm:

  <HTML>
  <SCRIPT>
     function InsertXML(ProdName, UInStock)
     {
     myTemplate = "http://localhost/northwind?template=" +
        "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" +
           "<sql:sync>" +
           "<sql:after>" +
           "<Products ProductName=\"" + ProdName + 
           " UnitsInStock=\"" + UInStock + " \"/>" +
           "</sql:after>" +
           "</sql:sync>" +
           "<sql:query>select * from Products FOR XML AUTO " +
           "</sql:query>" +
        "</ROOT>";
     alert(myTemplate);
     document.location.href = myTemplate;
     }
  </SCRIPT>
  <BODY>
     Product Name:<INPUT type="text" id="ProductName" value="">
        <br></br>
     Units In Stock:<INPUT type="text" id="UnitInStock" value="">
        <br></br>
     <INPUT type="button" value="insert" 
        OnClick="InsertXML(ProductName.value, UnitInStock.value);"/>
  </BODY>
  </HTML>
  

This HTML page uses a Java script function that builds the template. The backslash (/) is required for including a quote within a quote. You must place quotes around the new values or you will get an error. Unfortunately, there are some fields, such as money, which will not be accepted by SQL Server this way. These fields would need to be updated using a stored procedure as described below. Notice that we also included a sql:query element that is used to determine what is returned to the client. This will allow us to see whether the data was actually added to the database. Figure 15-6 shows what the HTML page and the query string look like.

Figure 15-6. The HTML page for updating with query string.

There are other more advanced features of the SQL ISAPI extension that extend beyond the level of this book. If you are interested in these features, you can look at the documentation that comes with the SQL ISAPI extension.

In Chapter 12, we created an ASP page that used the DOM to transform an XML page to XHTML on the server using an XSL document. Instead of writing your own code to perform XSL transformations on the server, you can use the XSL ISAPI extension to automatically transform an XML page that includes a reference to an XSL page if the browser is not Internet Explorer 5. If the browser being used is Internet Explorer 5, the XML page is sent to the client as is, and the transformation is done on the client. For the most part, the XSL ISAPI will be working with data and transforming it according to a set of rules defined in an XSL document.

The Xslisapi.exe file is a zipped file that contains the files for the ISAPI extension. This file can be found at http://msdn.microsoft.com/downloads/webtechnology/xml/xslisapi.asp. To install this extension on a server running IIS, follow these steps:

1. Expand the files into a folder in your local drive. You will see numerous headers for C++, but we are interested in only Xslisapi.dll.
2. Copy the Xslisapi.dll file into the %SystemRoot%\SYSTEM32\INETSRV directory.
3. Open the Internet Services Manager console, right click on the default Web site, and select Properties as shown in Figure 15-7.

   

   Figure 15-7. Opening Properties Window in the default Web site.

4. In the Properties window, select the Home Directory tab and then click on the Configuration button.
5. Select the App Mappings tab and click Add.
6. Type in Xslisapi.dll as the executable and .xml as the extension. If you are using IIS 5 (Microsoft Windows 2000), click Limit To and add HEAD, GET. If you are using IIS 4, enter PUT, POST>, DELETE in the method exclusions box.
7. Finally, clear the Script engine check box and select Check That File Exists check box. If you are using Internet Explorer 5 in Windows 2000, the configuration would appear as shown in Figure 15-8. Click OK twice.

   

   Figure 15-8. Application Mappings for Internet Explorer 5.

8. In the Home Directory tab's Execute Permissions box, select Scripts And Executables so that the xslisapi DLL is allowed to execute over any XML document on your Web site.
9. Click OK and close the Properties window.

   At this point, each request for an XML file that contains the processing instruction <?xml-stylesheet type='text/xsl' href='yourstyle.xsl'?> will go through the xslisapi DLL.

   If the requesting browser is Internet Explorer 5, the xslisapi DLL will pass the XML document directly to the client. Otherwise, the xslisapi DLL will perform the XSL transformation on the server.

You can use a different style sheet for server-side processing and client-side processing by using a server-href attribute within the same processing instruction. If the xslisapi DLL needs to do server-side processing, it will first look for the server-href attribute. If the attribute is there, it will use the XSL page referenced by server-href. If the client doesn't have Internet Explorer 5 installed, the style sheet referenced by the server-href attribute will be used to transform the document. Thus, we would rewrite the processing instruction as follows:

  <?xml-stylesheet type="text/xsl" 
     server-href="serversideStyle.xsl" href="yourstyle.xsl"?>
  

You can also add a special Config.xml file into the same directory as the xslisapi DLL. This configuration file is optional and gives additional instructions to the xslisapi DLL.

In this chapter, we discussed two Microsoft technologies that we can use to create data services components that can access data: ADO 2.5 and the SQL ISAPI extension. ADO will allow you to access data in any data store, whereas the SQL ISAPI extension allows you to access data in a SQL 6.5 or 7.0 database. Using ADO and the SQL ISAPI extension, you can create XML data that can be presented to the user and used for updating data.

The XSL ISAPI extension allows you to automatically transform XML documents using XSL on the server when the client does not have Internet Explorer 5 installed. This can be used if you do not want to write ASP pages that use the DOM to perform these translations. In the next chapter, we will look at Microsoft BizTalk Server 2000, a new technology that enables business partners to exchange information.

discuss this topic to forum