Web services provide a standard way to build loosely coupled applications that are resilient to change, can be easily described using Web Services Description Language (WSDL), and will make you rich overnight. OK, I exaggerated on that last point. But Web services do provide a new way to expose the valuable data stored in your SQL Server database to applications inside and outside your company. In "Creating a Web Service," June 2002, InstantDoc ID 24909, I introduced basic Web-services concepts and their relevance to SQL Server professionals. I also provided a simple example of how to invoke Web services support in XML for SQL Server 2000 Web Release 3 (SQLXML 3.0), available for download at http://microsoft.com/sql/default.asp. Building on this information, let's review the Web services support in SQLXML 3.0 and construct a simple Web service by using T-SQL stored procedures. Then, let's walk through an example of how you can use the Web service within an application that uses Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0.
Let's say that your company uses a supply-chain application that stores your customers' orders in a SQL Server database and keeps track of each order's status. Currently, when customers want to know which of their orders are pending, they contact your customer-service representative, who queries the database for that information. Customers then update their ordering systems. But suppose a customer wants to streamline the process by using an application to request order status directly from your system. To enable this type of access to your system, you and the customer need to agree on the interface the customer will use to make the request and the format in which you will return the requested data.
This scenario is an ideal application for Web services because you can use SOAP to build a single standards-based interface that works for many different customers with varying needs, regardless of the software applications and computing platform their enterprises use. Additionally, SOAP lets you build a loosely coupled interface that incorporates XML as the data format. (A loosely coupled application lets you reconfigure, redeploy, or relocate the implementation without affecting dependent applications.) By using XML, you gain extensibility that lets you expand the scope of the data you can provide to your customers in the future. Simply put, supplying a Web service lets you leverage the full value of XML's standard format, extensibility, and platform independence.
The first step in building the Web service is to define the stored procedure that returns the customer's data. You can create the stored procedure on your SQL Server by using Query Analyzer to execute the script in Listing 1. The stored procedure queries the Northwind sample database for a list of orders placed on or after a given order date, then returns a standard result set. The list of orders shows the order ID and the order date.
The next step is to configure a SQLXML 3.0 virtual directory that provides the Web services implementation for the stored procedure. In "Creating a Web Service," you can find detailed instructions for configuring a virtual directory so that it can use SQL Server's Northwind database. For this column's example, create a virtual directory named July2002. In SQLXML 3.0, you can configure a Web service by creating a virtual name, then selecting the stored procedures you want to include as the Web service's methods. (You can also select XML templates to include as methods, but that discussion is beyond the scope of this column.) To create the virtual name, click the Virtual Names tab of the Microsoft IIS Virtual Directory Manager. Place your cursor in the Name text field and type OrderStatus. Select soap in the Type field and type OrderStatus in the Path field. You need to create the OrderStatus directory below the path you specified for the virtual name. (I usually use something like C:\Inetpub\wwwroot\SQLMag\July2002.) Leave the default values in the Web Service Name and Domain Name fields. Press Save to store the virtual name.
Selecting stored procedures is the last step in creating your Web service. First, click the OrderStatus virtual name in the list at the upper-left corner of the Virtual Names tab, then press the Configuration button at the bottom. In the Soap virtual name configuration dialog box that appears, press ("..."), then click the getOrderList stored procedure you created in the Northwind database in the previous step. Press OK to select the stored procedure as a method of your Web service. Next, change the Row formatting option from Raw to Nested. Raw and Nested refer to modes for formatting XML results. Both modes correspond to FOR XML modes that SQLXML uses. When you select Nested mode, data is formatted using table and column names for the XML elements returned in the SOAP message. This formatting is similar to the way FOR XML AUTO queries format XML. (For an overview of each FOR XML mode, see "Selecting XML Technologies for Queries and Updates," April 2002, InstantDoc 24342; for a detailed description, see SQLXML 3.0 Books Online—BOL). To add the stored procedure as a method to your Web service, press Save. The method appears in the list box at the top of the dialog box that Figure 1 shows. Finally, press OK to save the Soap virtual name configuration, then press OK to save the virtual directory.
While you were creating the virtual name for your Web service, the IIS Virtual Directory Manager created a WSDL file in the directory you specified for the virtual name. The WSDL file describes how to invoke your newly created Web service from another application and is a crucial piece of information for users of your Web service.
WSDL describes each method as a set of messages that the client and the server exchange. The WSDL file is an XML document that describes the methods of your Web service (in this example, the getOrderList stored procedure), including the names and types of parameters, the names and types of return values, and the URL you use to invoke the method. Web Listing 1 shows the WSDL that SQLXML 3.0 generated for the Web service you constructed. (To access the Web listing, enter InstantDoc ID 24910 at http://www.sqlmag.com and click Download the code.) Let's briefly look at the WSDL file so that you can get a basic understanding of how Web services work with SQLXML 3.0. (For detailed information about WSDL files, see the "Contents of the WSDL File" section in SQLXML 3.0 BOL.)
The WSDL file defines messages by using wsdl:message elements. Web Listing 1's example contains two messages: getOrderListIn and getOrderListOut. Each message has one message part corresponding to a complex XML that is specified using the wsdl:part subelement. Wsdl:part's element attribute refers to a complex type defined or referred to within the wsdl:types elements. SQLXML 3.0 defines the complex types by using standard XML Schema Definition (XSD) schemas. Notice in Web Listing 1 that the wsdl:types element contains several schema definitions. Only the last schema is specific to your Web service. All the other schemas defined in the wsdl:types element are part of every WSDL file that SQLXML 3.0 generates. These schemas contain common type definitions that SQLXML 3.0 uses to implement the Web service. Your Web service-specific schema includes these schemas by using xsd:import statements. For the getOrderList messages, the Web service—specific schema defines two complex types: getOrderList and getOrderListResponse. These type definitions specify the XML content of the messages that pass between the client and the server within the SOAP protocol. The wsdl:operation subelement of the wsdl:portType element associates these messages with one of the Web service's methods. The wsdl:binding element associates each method specified by a wsdl:operation in the wsdl:portType element with an encoding of the I/O messages within the protocol. In this case, the getOrderList operation uses SOAP document encoding over HTTP. (For descriptions of the different binding types, see the WSDL specification at http://www.w3.org/TR/wsdl.) Finally, the wsdl:service element associates the methods defined within wsdl:portType and the bindings in the wsdl:binding element with the URL that accepts messages and executes the Web service's methods.
By interpreting the WSDL, a client can send the appropriately formatted SOAP messages to SQLXML 3.0, which in turn interprets the SOAP message and invokes the stored procedure configured for the virtual name. You might notice the OrderStatus.ssc SOAP service configuration (SSC) file in the same directory as the WSDL file. OrderStatus.ssc, an XML file containing configuration data that SQLXML 3.0 generated, holds data about the stored procedures that make up your Web service's methods. SQLXML 3.0 uses the data from this file to correctly call a stored procedure when it receives a SOAP request. OrderStatus.ssc also provides configuration data that specifies how SQLXML 3.0 formats the stored procedure's output parameters into a SOAP response message that is returned to the client.
Using a Web service could be tedious if you needed to write code directly in your program to interpret the WSDL file. Thankfully, Microsoft provides the SOAP Toolkit 2.0, which reads a WSDL file and provides an automation object that supports the methods defined within the WSDL file. Web Listing 2 shows a VBScript example that uses the SOAP Toolkit to invoke the getOrderList method on the Web service you just created. First, the code uses the mssoapinit method to create the SoapClient object and read the WSDL file from SQLXML 3.0. The method takes three parameters: a URL for the WSDL, the service name to use within the WSDL file, and a port to connect to the Web service (also specified in the WSDL file). Notice that the URL specifies the virtual directory and virtual name that you configured in the previous steps as well as a wsdl parameter. The wsdl parameter requests the WSDL for the Web service from SQLXML 3.0. The SoapClient object parses the WSDL (remember, the WSDL is in XML format), interprets the content, then dynamically provides getOrderList as a method of the SoapClient object.
Exposing methods dynamically on the SoapClient object lets programmers invoke Web services in the same way you call a method on a local object. The method returns an object that provides IXMLDOMNodeList—an interface from the XML Document Object Model (DOM)—to access a list of XML elements. The code assigns the interface to the xmlnodelist variable and uses the interface to access the returned data. If the stored procedure returns only one value with a simple type (e.g., the order count) instead of a result set, the SoapClient object returns the value directly to a variable within your script.
Web services provide a powerful new paradigm for implementing client/server processing both within and between enterprises. Admittedly, this column's example would be more complete if it showed how the customer's order system requested the data automatically. Nevertheless, the example shows how you can easily build a Web service by using SQLXML 3.0. You can apply this technology in many other scenarios. For example, you can use Web services to replace direct execution of stored procedures from your Web servers—or from any HTML, Active Server Pages (ASP), or JavaServer Pages (JSP) application. By using SQLXML 3.0's Web services, you can gain the advantages of a loosely coupled interface and extensibility of both the interface and the data you return.