Build Data-Driven Web Services with Updated XML Support for SQL Server 2000

Using SQLXML 3.0 for Data Access

Querying SQL Server with XML

• To create the template virtual directory in the MMC, select Default Web Site, then New Virtual Directory.
  
• On the General tab, name the virtual directory to match the database you will be accessing. I simply use Northwind (see Figure 1). This becomes the virtual directory upon which you will access any XML feature. Set this root directory to contain all templates.
  
  
  Figure 1 Good Ol'Northwind
  
  
• On the Security tab, select the authentication scheme you will use to access the database.
  
• On the Data Source tab, select your data source.
  
• On the Settings tab, select "Allow sql= ..." and select "Allow template queries." These two will be enough to get you going. Later you will select "Allow Post" to enable calls to SQL Server as a Web Service.
  
• On the Virtual Names tab (see Figure 2), select <New virtual name>, call it "template," specify the template type, and point it to the template subdirectory that should now reside under your main directory.
  
  
  Figure 2 Defining a New Virtual Name
  
  
• Name the template "Customers.xml" and save it under your template subdirectory. Any SQL command can be added to this file. Both updategrams and bulk loading can be used for updates or inserts from the template directory as well. Here you can see a sample XML query template for retrieving all customers from the Northwind database:

  <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:query
                                                                          client-side-xml="0"> SELECT * FROM Customers FOR XML AUTO </sql:query>
                                                                          </ROOT> 

• Now execute the following in your browser: http://localhost/northwind/template/customers.xml. You should see the XML query results shown in Figure 3 (not fancy but functional). If so, your queries are working and now you can proceed to the more advanced features of SQLXML 3.0.
  

Getting Started with SQLXML Web Services

Setting Up a SQLXML Web Service

• Select the Northwind virtual directory that you created in the previous section and display its properties.
  
• Select the Settings tab and make sure Allow Post is checked so that SOAP requests can be posted from the client.
  
• Select the Virtual Names tab and select <New virtual name> as you did to create the template type.
  
• Select the SOAP type and give it a name. I called mine "soapprocedures." You can name it anything you want and you can have as many defined SOAP types as you like. For each defined SOAP type, SQLXML creates a corresponding configuration file (.ssc) and a Web Services Description Language (WSDL) file that are used to access the Web Service. It is important to note that these files are named after the Web Service you provided, not the SOAP type. The SOAP type's name is used to retrieve the generated WSDL file, which describes the service and the operations (stored procedures, UDFs, and templates) that a client can then request.
  
• Select a directory to map this SOAP type. Use the SOAP directory created earlier. This is where the .ssc and WSDL files will be created. Select Save.
  
• Finally, give your Web Service a name. I called mine "procedures." This is the name you will use from your client code to instantiate the Web Service using the proxy in .NET. Figure 4 shows the WSDL output for this Web Service. You'll notice that under IIS, your virtual directory (Northwind) will have a SOAP directory and two files: procedures.ssc and procedures.wsdl. Note that by default you can't select the WSDL file directly from the browser. You need this URI: http://localhost/northwind/SOAPprocedures?wsdl.
  
• When the new SOAP type is selected, select Configure.
  
• Under the Soap Virtual Name Configuration dialog, select <New method mapping>.
  
• Select a mapping type (SP for stored procedures and user-defined functions).
  
• Select the stored procedure using the browse button, and give it a method name. The name will be the invokable Web method you will use from the client. Keep the remaining defaults. For my example I selected two stored procedures from the Northwind database, SalesByCategory and CustOrderHist, and kept the default, which simply uses the stored procedure name.
  
• Test the WSDL file that contains the Web methods created from your browser as you did in the section "Setting Up a SQLXML Virtual Directory." Now let's build the clients.
  

A SQLXML Web Services Client Using C#

localhost.procedures oWSProcs = new localhost.procedures(); int nReturnValue;
                                        ••• = oWSProcs.CustOrderHist("ALFKI", out nReturnValue); 

public static DataSet GetDataSetFromXmlFragment(XmlElement oXml) { DataSet
                                        ds = new DataSet(); XmlTextReader oReader = new XmlTextReader(oXml.OuterXml, XmlNodeType.Element,
                                        new XmlParserContext(null, null, null, XmlSpace.None)); // now lets create a schema
                                        off of the instance data ds.ReadXml(oReader, XmlReadMode.InferSchema); return ds;
                                        } 

Calling Templates and UDFs as Web Services

• Go to the properties dialog of the Northwind virtual directory.
  
• On the Virtual Names tab, select the soapprocedures SOAP type created earlier and select Configure.
  
• Select template as the Edit/New mapping type.
  
• Select the browse button. From there you can find any previously built XML template.
  
• Select the customers.xml template that you have used already to test the installation of SQLXML 3.0 and call it GetAllCustomers.
  

Using the SOAP Toolkit 2.0

Conclusion