Web Services in Action

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.