SQL 2000 and Web Services (XML and SQL part 11)

September 26, 2003

In the last article of this series, we will focus on Web Services and their implementation in the SQL Server 2000 environment with the help of the SQLXML component. In particular, we will look into a non-programmatic way of publishing SQL Server 2000 stored procedures, user-defined functions and XML templates as Web Services. Note that this functionality requires the SQLXML component version 3.0 (currently at Service Pack 1 level), downloadable from the Microsoft Web Site.

Web Services constitute one of the most significant benefits of Microsoft .NET technology. The majority of software development these days is geared towards distributed environments. This not only benefits scalability and availability but also speeds up development time, due to increased reusability. A client application can take advantage of pre-fabricated components residing on multiple, redundant servers, eliminating the need for implementing the same functionality locally. The idea is not new, however. In the past, a client-server session was frequently limited by both the operating system and development platform, as well as communication protocols. With .NET Web Services, these limitations have been removed by introducing solutions based on open standards such as XML and HTTP.

HTTP has been selected as the transport protocol for Web Services due to its universal nature. It is undoubtedly the protocol most commonly allowed to pass corporate firewalls, which allows sharing Web Services beyond intranet environments. While HTTP GET and HTTP POST requests are still available, typically, messages between a client and a Web Services server are exchanged via SOAP (an acronym for Simple Object Access Protocol). SOAP messages are XML-formatted documents that specify the schema defining their format and contain reference to a method to be executed as well its parameters or results (depending on whether the message is a SOAP request and SOAP response). XML is also used by Web Services Description Language (WSDL) for publishing Web Services in order to allow prospective clients to determine what methods are available and what parameters are required for their execution.

Starting with SQLXML 3.0, it is possible to create a Web Service by defining a Virtual Directory of SOAP type, which, in turn, results in generating a WSDL file describing functionality of stored procedures, XML templates or user-defined functions. Once the file is available, any SOAP aware client can post a properly formatted SOAP request for services provided by the Web Service created this way. The Web server running the SQLXML component forwards the request to the data source and returns the results in the form of a SOAP response to the client. In the case of an XML Template, the resulting XML document is simply returned by the Web Service, in the case of stored procedures or user-defined functions, the SQLXML component is responsible for converting relational data to XML format and returning both XML data as well as output parameters.

Note, that in order to take advantage of Web Services, you will need to create a Web Services client. This can be accomplished with on .NET development platform (Visual Studio .NET greatly simplifies this task), but it is also possible with COM based applications, by employing SOAP Toolkit available for download from the Microsoft Web site. For details on developing both types of clients, refer to the "Writing Client Applications" topic of the SQLXML 3.0 Service Pack 1 help file (included with the SQLXML component). In this article, we will provide a description of the procedure necessary to set up Web Services:

• Start by creating a new virtual directory representing the target SQL Server 2000 database (we will be using Northwind). The required steps were outlined in one of our earlier articles.
• Ensure that Allow Post is checked on the Settings tab of the Virtual Directory properties dialog box. This is necessary in order for SOAP requests from Web Services clients to be accepted.
• From the Virtual Names tab of the Northwind Properties dialog box, create a virtual name of the soap type. You will need to provide the following values:
  • Name - name assigned to the virtual directory (NorthwindProcedures in our case),
  • Type - must be set to soap,
  • Path - physical path to the folder containing files used by virtual directory (c:\InetPub\wwwroot\Northwind in our case)
  • Web Service Name - name assigned to the Web service used in the WSDL XML document (by default set to the same value as the name assigned to virtual directory). This name is used by clients to contact the Web Service. We will leave it as NorthwindProcedures.
  • Domain Name - namespace used for the Web service (included in the WSDL document). This value is set by default to the hostname of the server hosting the Web server. You might want to replace it with the fully qualified name (including your DNS domain name) to ensure its uniqueness and make it easily identifiable when operating in an Internet environment. In our case, we will set it to WebServer.databasejournal.com
• Once you saved the new virtual name of soap type, you need to configure it. After clicking on the Configure button on the Virtual Name tab, you will be presented with the Virtual Name configuration dialog box.
• In the Edit/New mapping section, you can choose between SP (stored procedures and user-defined functions) and Template (XML templates). The listing you will see after clicking on the (...) button will depend on this initial selection.
• Selecting a stored procedure or a template automatically sets the Method name text box to match the procedure name. This is the name that will be used by the Web Services client to invoke the underlying stored procedure, template or user-defined function.
• If you selected a stored procedure, you will also need to specify how its results will be converted to XML format. This involves specifying output formatting (Raw or Nested) and response type (XML objects, Dataset objects, or Single dataset). Raw and Nested options correspond to the described earlier RAW and NESTED modes of the SELECT queries. XML objects designates the response type in which a separate XML element is created for each XML fragment returned from the stored procedure. Dataset objects response type results in each dataset returned in a separate element. Finally, single dataset option produces a single element for the entire result set (so it is not intended for multiple result sets). Note that methods mapped to XML templates always return XML objects.
• Finally, the "Return error as soap faults" causes errors to be returned in the form of SOAP messages to clients (which can be used for troubleshooting or error detection by SOAP clients).

For each Web Service you configure, the SQLXML 3.0 component creates two files residing in the folder corresponding to the Web Services virtual directory (defined by the Path parameter on the Virtual Name tab of the virtual directory Properties dialog box). Both files are given the same name as the Web Service Name you assigned previously. The first one (with extension .wsdl) contains the Web Services Description Language document, the second one (with extension .ssc) is the Web Services configuration file. You can retrieve the content of the WSDL file by typing the following in the web browser Address box:

http://WebServer.databasejournal.com/Northwind/NorthwindProcedures?wsdl

As you can see, building Web Services with SQLXML 3.0 is straightforward and quick, giving you access to stored procedures, user-defined functions or XML templates. All that is required is to properly configure SQLXML properties and develop a Web Services client.

This article concludes our series, which was intended to give you overview of the most popular XML related features in SQL Server 2000.

» See All Articles by Columnist Marcin Policht