Creating a Web Service

Use SQLXML 3.0 to expose your stored procedures as Web services

Editor's Note: Beginning this month, Exploring XML is transitioning from a question-and-answer format to a subject-based column, complemented by occasional questions and answers. You'll still find the same in-depth XML information, including plenty of code samples and practical information about how to use SQL Server XML technologies to build applications. Please submit your suggestions for future column topics and your questions to [email protected].

The latest technology buzz is Web services. Terms such as Simple Object Access Protocol (SOAP), Web Services Description Language (WSDL), and Universal Description, Discovery, and Integration (UDDI) now appear in almost all marketing materials about XML and the Internet. But how are Web services relevant to you as a SQL Server professional?

Web services are about building loosely coupled applications and achieving interoperability. A loosely coupled application lets you reconfigure, redeploy, or relocate the implementation without affecting dependent applications. Interoperability refers to building applications that you can use from any Web services—enabled platform. Exposing SQL Server through Web services gives applications a loosely coupled interface that lets you modify the structure and location of your database without affecting the application. Additionally, Web services let SQL Server interoperate with more programming environments and platforms.

To achieve interoperability, developers established Web services on a set of open industry-standard protocols and formats. XML is the basis for many of these standards, providing a platform-independent description and representation of data. SOAP builds on XML to provide a standards-based way to send data to and receive data from an application, typically by using HTTP. WSDL describes the location, methods, parameters, and data types that a Web service uses. UDDI provides an interface to a directory in which you can register your Web service or find other Web services you might require. By combining these standard technologies and T-SQL programming, you can implement SQL Server stored procedures as Web services.

The focus of SQL Server 2000 Web Release 3 (SQLXML 3.0) is Web services. (You can download SQLXML 3.0 at http://microsoft.com/sql/default.asp.) SQLXML 3.0 lets you select stored procedures from your database and XML templates from your virtual directory to expose as a Web service's methods. Because you can now invoke stored procedures through Web services as well as through traditional methods— such as T-SQL EXEC statements, OLE DB, ADO, and ODBC—you get maximum reuse of these components. In short, SQLXML 3.0 lets you extend your expertise in database programming to the new world of Web services without learning a new language or new tools. Let's see how to turn stored procedures into Web services.

Configuring your virtual directory. The first step in exposing a stored procedure as a Web service is to start the Microsoft IIS Virtual Directory Manager from the Configure IIS Support menu item that installs with SQLXML 3.0. To create a new virtual directory, select your Web server from the left tree view, then expand the view and click Default Web Site. Right-click in any open area on the right pane. Select Context, New, Virtual Directory to display the New Virtual Directory Properties dialog box. On the tabs of this dialog box, you configure the virtual directory to access your database. On the General tab, name your virtual directory June 2002. Then, select a local directory on your system. On the Security and Data Source tabs, enter a SQL Server login with permissions to access the Northwind sample database, which you select on the same tab. Note that permissions on the directory you selected on the General tab must grant permissions to the default user you configured for IIS on the Security and Data Source tabs. (See IIS documentation for configuration details.) To avoid permission problems, choose a subdirectory of the Inetpubwwwroot directory for your virtual directory; the default user for IIS automatically has permissions to access data below Inetpubwwwroot. Next, on the Settings tab, select the Allow POST option so that the virtual directory will accept HTTP POST requests. Click Apply.

Configuring a virtual name. After you've configured the virtual directory, click the Virtual Names tab in the New Virtual Directory Properties dialog box. Select New virtual name from the list of virtual names, choose a name for this virtual name, then set its type to soap. Listing 1 shows the VBScript code that uses the soap string for the virtual name. Type a path for the virtual name. The path will contain the WSDL file output from the IIS Virtual Directory Manager when you save changes to the virtual root. I usually choose ('.'), which selects the same directory as the virtual directory. You can accept the default values for the remaining fields in the dialog box. Click Save. When you save the virtual name, SQLXML 3.0 writes two files to the directory you specified for the virtual name. One file—the WSDL description of your Web service—has a .wsdl suffix. The other file, which has a .ssc suffix, contains an XML description of the stored procedures that the IIS Virtual Directory Manager uses. Now you select the stored procedures you want to expose as your Web service's methods. For this example, I used the OrdersForCustomer stored procedure that Listing 2 shows. This stored procedure returns the set of orders for the specified customer ID, passed as a parameter. You need to use Query Analyzer to install this stored procedure in the Northwind sample database before proceeding with the steps that follow.

Creating a Web service. On the Virtual Names tab in the New Virtual Directory Properties dialog box, select the virtual name you just created, then click Configure. This action displays the Soap virtual name configuration dialog box, which Figure 1 shows. In this dialog box, you select the stored procedures you want to expose as your Web service's methods. To select a stored procedure, click ("..."), which brings up a list of the stored procedures available in the database you configured the virtual directory to access. Resist the impulse to select a stored procedure that contains a query with a FOR XML clause. Instead, you need to select a stored procedure that returns a standard result set. SQLXML 3.0 expects the stored procedure to return output parameters or a standard result set. The results are then built into XML on the middle tier. Choosing stored procedures that return standard result sets gives you maximum flexibility for making all your stored procedures available for Web services or for traditional programming methods such as T-SQL. When you build XML query results on the middle tier, be careful to avoid SQLXML 3.0's limitations. (For details about these limitations, see SQLXML 3.0 Books Online—BOL.) After you choose the OrdersForCustomer stored procedure, click OK, then click Save on the Soap virtual name configuration dialog box. Congratulations, you've created your first Web service. You can now use any text editor to examine the WSDL in the directory you chose for the virtual name. The WSDL file contains the method definitions corresponding to the Web services you selected.

To test your newly created Web service, use the VBScript code that Listing 1 shows. The code creates a SOAP message to invoke the Web service, then uses an XMLHTTP object to post the SOAP request to your Web service. The SOAP result appears in a message box. This client simply illustrates the SOAP messages exchanged between the VBScript code and SQLXML 3.0. However, it also shows that stored-procedure invocations can now interoperate across platforms by using SOAP over HTTP, and they can provide loose coupling to the stored procedures through the specification in the WSDL and SCC files. In my next column, I'll explore an example of how to use Web services and SQL Server within an application.