Back to Articles Page      

SQL XML and MSXML - an alternative approach to traditional data access

• Abstract
  SQL Server 2000 introduced several features to enable native XML support. This includes the FOR XML clause with SELECT statement, the OPENXML function, HTTP data access, etc. In order to enhance and upgrade the XML support in SQL Server 2000, Microsoft is making periodic XML for SQL Server Web releases on its Web site.
  
  MSXML, Microsoft's flagship XML component, has evolved remarkably. The MSXML 3.0 release enhanced XMLHTTP class and renamed it as ServerXMLHTTP. This class helps in building server-to-server applications, wherein you need to establish an HTTP connection between files and objects on different Web servers. For instance, using ServerXMLHTTP, from one server (or from any application) you can call an ASP page or an XML file residing on another server and then receive the response as a stream or XML document object.
  
  In this article, we'll learn how to build data driven Web applications using SQL Server's XML support in conjunction with MSXML. We will read and write SQL Server data without using ADO, ODBC, or any other data access mechanism.



• Prerequisites
  You'll need SQL Server 2000, IIS or PWS Web server, MSXML 3.0, and XML for SQL Server 2000 Web Release 1.0 installed in order to run sample code provided with this article. This article assumes you are familiar with ServerXMLHTTP, and have some knowledge of accessing SQL Server data over HTTP and using IIS Virtual Directory Management for SQL Server tool.



• In this article
  We'll build a classic three-tier Web database application using IIS, SQL Server 2000, Visual Basic COM DLL and XML for SQL Server Web Release 1.0. We'll work with the Northwind sample database that ships with SQL Server 2000. If you have changed this database or wish to restore this database after running the data modification code from this article, you can use instnwnd.sql script file available under MSSQL\Install subdirectory to reinstall Northwind database.



• Architecture
  The browser client requests an ASP page residing on the IIS Web server, the ASP page creates an instance of VB COM component and calls a particular method/property on that COM object. The method/property implementation in the VB COM object uses MSXML ServerXMLHTTP class to request SQL XML template file residing on the same (or may be different) Web server under a special virtual directory configured using IIS Virtual Directory Management tool. SQL XML ISAPI Extension DLL processes any request on this special virtual directory, which in turn use SQL Server OLE DB Provider to talk to SQL Server and do database stuff.
  
  



• Getting Ready
  Let's now make sure we have the components and environment ready to build the sample Web application from this article.
  • Make sure you have SQL Server 2000 installed and you can access Northwind sample database.
  • We'll need IIS or PWS Web server, either on the same machine as SQL Server or on a different machine. We'll configure one virtual directory using standard IIS Manager and another virtual directory using IIS Virtual Directory Management tool.
  • As mentioned earlier, we'll build a COM component using Visual Basic 6.0. The COM component will use MSXML ServerXMLHTTP class to call SQL XML template files and post data on the special virtual directory. We could have skipped this step and directory used MSXML ServerXMLHTTP from ASP pages, but it is a good idea to do this in COM component since we can add business logic also there, instead of putting the business logic in the ASP pages.
  • Finally, this article also illustrates use of Updategrams, which are not natively available with SQL Server 2000. You'll have to download the XML for SQL Server 2000 Web Release 1.0 from MSDN download area and install it on the middle tier machine.
  
  I'll be running all these on my laptop running Microsoft Windows 2000 Advanced Server, IIS 5.0, SQL Server 2000, XML for SQL Server 2000 Web Release 1.0, Visual Basic 6.0, all installed on this single machine.



• Let's Begin
  The steps that I'll follow to illustrate building middle tier using MSXML and SQL XML include:
  1. Writing the COM component using Visual Basic 6.0. This COM component will have just one class having 4 exposed methods. This COM component will be called from ASP pages.
  2. Next, we'll create the special SQL XML Virtual Directory and write XML template files and updategrams.
  3. Finally, we'll write the ASP pages for the sample Web site.



• The Visual Basic COM Component
  
  
  As mentioned earlier, our COM component will have just one class, Supplier, and four methods:
  
  
  1. GetSupplierDetails
     Returns the details from the Supplier table. If the SupplierID parameter is passed, just a single matching record is returned, otherwise all records are returned in XML format.
  2. AddSupplier
     Accepts XML encoded Supplier details and uses Updategrams to insert a new record into the Supplier table.
  3. UpdateSupplier
     This method also accepts XML encoded supplier details and updates the Supplier record, once again using Updategrams.
  4. GetOrderDetails
     Returns the Order details in XML format for the supplier. The SupplierID is passed as input parameter to this method.
  
  These methods will make use of special SQL XML virtual directory, XML template files, and updategrams, which will be discussed in the following section.



• Steps to build COM component
  
  1. Start Visual Basic 6.0 and create a new ActiveX DLL Project.
  2. Rename the class to Supplier.
  3. Add the reference to MSXML 3.0 library by clicking on Project | References and selecting Microsoft XML 3.0.
  4. Click on Tools | Add Procedure… and add a function named GetSupplierDetails
  5. This function looks like:
     

     
     Public Function GetSupplierDetails(Optional SupplierID As Integer) As String
       Dim objSvr As New MSXML2.ServerXMLHTTP30
       szURL = "http://localhost/perfectxml/"
       If SupplierID > 0 Then
         szQuery = "?sql=SELECT%20*%20FROM%20Suppliers%20where%20SupplierID=" & _
            SupplierID & "%20for%20xml%20auto&root=root"
       Else
         szQuery = "?sql=SELECT%20*%20FROM%20Suppliers%20for%20xml%20auto&root=root"
       End If
       objSvr.open "GET", szURL + szQuery, False
       objSvr.send
       iStatus = objSvr.Status
       If iStatus = 200 Then
         GetSupplierDetails = objSvr.responseXML.xml
       Else
         'Do error handling here
       End If
     
     End Function
     

  6. The above function does a direct URL query over HTTP using MSXML.ServerXMLHTTP. If the HTTP call succeeds (status=200), we use ServerXMLHTTP's responseXML property, which is a DOM XML document object loaded with the returned Supplier details in XML format.
  7. Let's now build the other 3 methods.
  8. Once again click on Tools | Add Procedure… and this time select Sub, type the procedure name as AddSupplier and then click OK. This function looks like:
     

     
     Public Sub AddSupplier(XMLEncodedData)
       Dim objSvr As New MSXML2.ServerXMLHTTP30
       Dim objDoc As New MSXML2.DOMDocument30
       Dim objNode As MSXML2.IXMLDOMNode
       
       szURL = "http://localhost/perfectxml/template/addsupplier.xml"
       
       objDoc.loadXML XMLEncodedData
       Set objNode = objDoc.documentElement
       strCompName = objNode.Attributes.getNamedItem("CN").Text
       strContName = objNode.Attributes.getNamedItem("CoN").Text
       strParams = "CN=" & strCompName & "&CoN=" & strContName
       objSvr.open "POST", szURL + szQuery, False
       objSvr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
       
       objSvr.send strParams
       iStatus = objSvr.Status
       If iStatus = 200 Then
         'Page call succeeded, however
         'parse the returned XML and do error checking
         'We'll also receive the value of identity column
       Else
         'Do error handling here
       End If
     End Sub
     

  9. The above function receives supplier data as XML string, parses it and passes the values as parameters to the updategram (addsupplier.xml). We'll have a closer look at the updategram code and it's functioning in the next section. Note how we pass the parameters to the updategram by setting the content-type request header and passing parameter to the ServerXMLHTTP's send method.
  10. Before proceeding, let's save the class file as Supplier.cls. Also let's update the project properties and save the project. Click on Project | Project1 Properties… and change the project name to Suppliers.
  11. UpdateSupplier and GetOrderDetails functions are very similar to above two functions and they look like:
      

      
      
      Public Sub UpdateSupplier(XMLEncodedData)
        Dim objSvr As New MSXML2.ServerXMLHTTP30
        Dim objDoc As New MSXML2.DOMDocument30
        Dim objNode As MSXML2.IXMLDOMNode
        
        szURL = "http://localhost/perfectxml/template/updatesupplier.xml"
        
        objDoc.loadXML XMLEncodedData
        Set objNode = objDoc.documentElement
        strID = objNode.Attributes.getNamedItem("ID").Text
        strCompName = objNode.Attributes.getNamedItem("CN").Text
        strContName = objNode.Attributes.getNamedItem("CoN").Text
        strParams = "ID=" & strID & "&CN=" & strCompName & "&CoN=" & strContName
        objSvr.open "POST", szURL + szQuery, False
        objSvr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        
        objSvr.send strParams
        iStatus = objSvr.Status
        If iStatus = 200 Then
          'Page call succeeded, however
          'parse the returned XML and do error checking
        Else
          'Do error handling here
        End If
      End Sub
      
      Public Function GetOrderDetails(SupplierID As Integer) As String
        Dim objSvr As New MSXML2.ServerXMLHTTP30
        szURL = "http://localhost/perfectxml/template/OrderDetails.xml?ID=" & SupplierID
        objSvr.open "GET", szURL, False
        objSvr.send
        iStatus = objSvr.Status
        If iStatus = 200 Then
          GetOrderDetails = objSvr.responseXML.xml
        Else
          'Do error handling here
        End If
      
      End Function
      

  12. The final step is to build the project DLL. Click File | Make Supplier.dll… to build the COM DLL.
  
  Note that this code is not necessarily well-organized, for instance there is lot of ServerXMLHTTP code that is getting repeated, you might want to modularize it little bit and separate out ServerXMLHTTP and XML parsing into separate class(es).



• Configuring SQL XML Virtual Directory
  In this section we'll quickly look at how to configure the SQL XML Virtual directory using IIS Virtual Directory Management for SQL Server Utility.
  
  Click on Start | Programs | Microsoft SQL Server | Configure SQL XML Support in IIS and then perform the following steps.
  1. Right click on Default Web Site and select New | Virtual Directory.
     
     
     
  2. Name the virtual directory as perfectxml and specify the physical p ath for this virtual directory (on my machine it is c:\perfectxml):
     
     
     
  3. I'll specify the SQL Server login name and password for this virtual directory, however I recommend you study the other options from SQL Server Books Online. I found the last option (Use Basic Authentication to SQL Server) interesting, with this option when the virtual directory is accessed it is required to pass the SQL Server login information (a login window will pop up when the page is accessed from the browser; and if the page is accessed from a program using ServerXMLHTTP, the login name and password needs to be passed to the open method). In our case, we are directly specifying the login information and this account will be used for all data access.
     
     
     
  4. Since I have SQL Server installed on the same machine, the server name is (local) and make sure to select the database as Northwind.
     
     
     
  5. Once again, I'll not go into details regarding options on the next tab, SQL Server Books Online and help file shipped with Web Release 1.0 has enough information on all these options.
     
     
     
  6. Finally, let's create a template virtual name. First create a sub-directory under the physical directory created for perfectxml virtual directory and specify this sub-directory as the path for template virtual name. This is the directory where we'll create and save the template and updategram files.
     
     
     



• XML Template Files and Updategrams
  The Visual Basic code calls two updategrams and one SQL XML template file (addsupplier.xml, updatesupplier.xml, and OrderDetails.xml).
  
  A template is a well-formed XML document containing one or more tagged Transact-SQL statements or XPath queries. This XML document is persisted as a physical file on the Web server in a special subdirectory under the virtual root.
  
  Updategrams refers to the ability to modify (insert, update, or delete) database directly through XML. Updategrams consists of blocks of special XML tags that describe what the data looks like now and what you want it to look like when the Updategram is executed.
  
  Refer to SQL Server Books Online and Web Release 1.0 help file documentation for more details on this. Here is how these files look like:
  
  • addsupplier.xml
    

    
    <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
       <updg:header>
        <updg:param name="CN"/>
        <updg:param name="CoN"/>
       </updg:header>
    
      <updg:sync >
    
      <updg:after updg:returnid="x" >
         <Suppliers updg:at-identity="x" CompanyName="$CN" ContactName="$CoN" />
      </updg:after>
    
      </updg:sync>
    </ROOT>
    

  • updatesupplier.xml
    

    
    <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
       <updg:header>
        <updg:param name="ID"/>
        <updg:param name="CN"/>
        <updg:param name="CoN"/>
       </updg:header>
    
      <updg:sync >
    
      <updg:before >
         <Suppliers SupplierID="$ID" />
      </updg:before >
      <updg:after >
         <Suppliers CompanyName="$CN" ContactName="$CoN" />
      </updg:after>
    
      </updg:sync>
    </ROOT>
    

  • OrderDetails.xml
    

    
    <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
      <sql:header>
       <sql:param name='ID'></sql:param>  
      </sql:header>
      <sql:query >
        exec sproc_OrderDetails @ID, 2
      </sql:query>
    </ROOT>
    

  
  I've tried to illustrate various ways in which SQL XML functionality can be utilized. For instance, the GetSupplierDetails function in Visual Basic COM class makes a direct HTTP query while GetOrderDetails function calls a template file, which in turn calls a stored procedure named sproc_OrderDetails, which looks like:

  
  CREATE PROCEDURE sproc_OrderDetails
          @SuppId integer,
          @Format Integer
  AS
          IF @Format = 1
          BEGIN
                  SELECT od.orderid, od.productid, od.unitprice, od.quantity, p.productname
                  FROM 
                  suppliers s INNER JOIN products p on s.supplierid = p.supplierid AND s.supplierid = @SuppId 
                  INNER JOIN [order details] od on od.productid = p.productid
          END
          ELSE
          BEGIN
                  SELECT od.orderid, od.productid, od.unitprice, od.quantity, p.productname
                  FROM 
                  suppliers s INNER JOIN products p on s.supplierid = p.supplierid AND s.supplierid = @SuppId 
                  INNER JOIN [order details] od on od.productid = p.productid FOR XML RAW
          END
  Go 
  

  
  The important point to note in above stored procedure is @Format input parameter; it decides whether the returned data should be in regular rowset format or in XML format. This allows stored procedure to be called from regular ADO application and also from XML template files.
  
  The final step in building our three-tier Northwind Web application is building the front end HTML and ASP pages. This involves write HTML pages, forms, calling COM component in ASP pages, data formatting, etc. I'll not discuss the HTML and ASP pages in this article, however you can download the source code files and study them. Here is how the front end looks like:
  
  
  1. Initial Screen
     
     
     
  2. Supplier Details
     
     
     The COM method call returns data in XML format and I've used Data Islands to present the information in tabular format. However you can use stylesheet, etc. to convert the XML to HTML format.
     
     
  3. Inserting new record
     
     
     When the above form is posted, the action ASP page simply calls AddSupplier method on the VB COM object, which in turn uses updategram to insert a new record. Update Supplier functionality is also similar to this one.
     
     
  4. Order details
     
     
     The ASP page calls GetOrderDetails method on the COM object, which returns the order data in XML format, I've used Data Island and stylesheets to convert the XML to HTML format.
     
     I would recommend download the source code, build the COM object, configure the SQL XML virtual directory and copy XML template files, copy the HTML and ASP pages in some other regular IIS virtual directory, run and study the above sample application.
     
     


• Summary
  Microsoft SQL Server 2000 simplifies building Web based database applications. You no longer have to worry about connection pooling, ADO code maintenance, etc. Just use of SQL Server 2000 XML features facilitates complete data access.
  
  In this article, we saw how to build a Web database application using MSXML ServerXMLHTTP and SQL XML. All the data access was done using XML template files and updategrams.

Download this article in Microsoft Word Format (Zip file [249 KB])
Download Source code for the sample Web application (Zip file [8 KB])

If you have any questions or comments, feel free to contact author of this article, Darshan Singh at .

Back to Articles Page      

All information on this site is for training only. We do not warrant its correctness or its fitness to be used. The risk of using it remains entirely with the user.