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:
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:
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.