QLXML is a set of supplemental tools that extend SQL Server’s existing support for retrieving and storing XML data. With SQLXML 3.0, you are now able to use SQL Server to expose Web services. SQLXML’s Web services let you execute stored procedures, user-defined functions, and they support templates.
While SQLXML’s Web services capabilities do not offer the flexibility or portability that can be gained by developing your own Web service applications to access data, they are ideal for rapid application development because you can offer SQLXML Web services without writing any code! In addition, the technology is well-suited for exposing reports over the Web. You can develop a client application that consumes SQLXML Web services with very little client-side code. From there, you have a number of options to shape, format, or parse the result set.
In this article you’ll see how to expose a stored procedure as a Web service and build a simple ASP.NET Web Form-based client to access and test the Web service.
You should have a basic of understanding of SQL Server 2000 and IIS, including knowledge of how to setup a virtual directory in IIS and how to grant permissions to a user in SQL Server. You can find basic documentation on these subjects in SQL Server 2000 Books Online and in the SQLXML 3.0 Documentation . To begin, you need an instance of SQL Server 2000, a Windows 2000 server running IIS, Microsoft’s MSXML 4.0 XML parser, the SQLXML 3.0 toolkit, and Visual Studio .NET (VS.NET). You can download the XML parser and SQLXML tools and documentation from the SQLXML resource link in the Resources column of this article.
Configuring a Web Service
The demonstration application for this article uses the Northwind sample database that’s installed by default with SQL Server. To get started, follow the instructions in the SQLXML 3.0 Documentation, available for download with the SQLXML 3.0 installer.
First, perform the steps in the procedure labeled Creating the nwind Virtual Directory under the topic IIS Virtual Directory Management for SQL Server in the SQLXML 3.0 documentation.
Go to the Configure IIS Support MMC snap-in application under the SQLXML 3.0 program group.
Select the default web site for your server, go to the Action menu and select New, then click on Virtual Directory.
Create a virtual directory named nwind under IIS that supports SQLXML applications accessing the Northwind database.
Configure the security settings to support the access to the Northwind database by the virtual directory application.
Under the Settings tab, check the Allow Post option. This allows HTTP POST requests, which are required to support SQLXML Web.
 | |
| Figure 1. Select a Name: Configure the SOAP virtual name type for MyWebService. |
Under this virtual directory, you can configure different types of SQLXML applications, including templates, schemas, and dbobjects, which support template execution, XPath queries against a mapping schema file, and direct access of database objects respectively. These different types of applications under the virtual directory are called virtual name types. There is also a SOAP virtual name type that identifies Web services using SOAP messaging. Create a SOAP virtual name type and name it MyWebService (see Figure 1). Now you need to follow the steps in the section labeled Step 2: Configuring the Virtual Name under the topic Initial Setup for Sending SOAP Requests.
Enter MyWebService as the name for the new virtual directory application and select the virtual name type SOAP (see Figure 1). This creates a reference to the Web Service extensions of the Northwind database. Configuring MyWebService creates a WSDL (Web Services Definition Language) file and a SQL Server Configuration (.ssc) configuration file for your Web service. The .ssc file describes the virtual name type configuration, and SQLXML uses it to generate the WSDL file. The WSDL file describes the Web services and the methods that your client application can call when using the service.
After setting up a SOAP virtual name type, select the Configure option under the Virtual Names tab of the IIS Virtual Directory management tool for SQLXML 3.0. To set up a stored procedure method mapping type for your soap virtual name type, select the CustOrdersDetail stored procedure under SP/Template option and select the Save option. Saving the stored procedure mapping adds a CustOrdersDetail method to MyWebService. After completing this step, browse to your nwind virtual directory and look for a file named soap.wsdl, which is the XML-formatted WSDL file that describes the services you configured. In a text editor the file looks like this:
This fragment of the CustOrdersDetail WSDL file (soap.wsdl) generated by SQLXML shows the configuration of the CustOrdersDetail method, including the definition of parameter inputs and outputs.
 | |
| Figure 2. Add the Controls: ASP.Net Web Form client application for MyWebService. |
Constructing the ASP.NET client
After configuring the SQLXML Web service, you need to develop a client application to access the service and perform any of its operations. First, create a new C# or VB.Net ASP.NET Web application and name it WebServiceClient. You’ll need to add three controls to the default Web Form: a Button with the ID btnRequest, and two TextBox controls, one with the ID txtOrderID and the other with the ID txtResponse. Set the TextMode property of the txtResponse TextBox control to MultiLine to support the XML response data from your Web service. The form should look similar to Figure 2.
 | |
| Figure 3. Add a Reference: Add the MyWebService reference to the ASP.Net application. |
After adding the controls, you’ll need to add a reference to the Web service you’ve just created. To do that, click on the References folder in the Solution Explorer window in VS.NET, right-click on the References folder, and select the Add Web Reference menu option (see Figure 3).
Invoking the Web Service
After creating the ASP.NET project, you need to add the code to invoke the Web Service. You want the application to invoke the Web service, call the CustOrdersDetail method, and process the returned XML result set when users click the button. Add the following code to the btnRequest_Click event.
(C# code)
private void btnRequest_Click(object sender, System.EventArgs e) { int iOrderID; int returnValue = 0; int j = 0; iOrderID = Convert.ToInt32(txtOrderID.Text); localhost.MyWebService proxy = new localhost.MyWebService(); object[] results; results = proxy.CustOrdersDetail(iOrderID, out returnValue); System.Xml.XmlElement result; result = (System.Xml.XmlElement) results[j]; txtResponse.Text = result.OuterXml.ToString(); } Private Sub btnRequest_Click _ (ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnRequest.Click Dim iOrderID As Int32 Dim returnValue As Int16 = 0 Dim j As Int16 Dim proxy As New localhost.MyWebService() Dim results As New Object() Dim result As System.Xml.XmlElement iOrderID = Convert.ToInt32(txtOrderID.Text) results = proxy.CustOrdersDetail _ (iOrderID, returnValue) result = results(j) txtResponse.Text = result.OuterXml.ToString() End Sub(C# code)
result = (System.Xml.XmlElement) results[j]; txtResponse.Text = result.OuterXml.ToString(); result = results(j) txtResponse.Text = result.OuterXml.ToString() |
|
|
SQLXML also enables template execution, which lets you use XPath queries to retrieve data from an underlying XML result set. While template support can assist greatly in developing a useful application, it does not provide all the flexibility needed to effectively leverage XML data.
SQLXML: Simple, but Limited
SQLXML has limitations in comparison with a full VS.Net Web service. For example, SQLXML-generated Web services can’t apply an XSLT stylesheet to the XML data or process the data programmatically to output HTML directly to your client applications. Essentially, the SQLXML approach requires that you consolidate your business and data tiers in order to effectively encapsulate the logic from your client applications. The result can be applications that are difficult to maintain and aren’t easily ported to new tools or platforms.
Nonetheless, as the sample application demonstrates, by following the configuration steps outlined, and writing less than twenty lines of code, you can call Web services that return XML data to your client application. SQLXML is an ideal solution for creating applications to view online catalog information, sales order history, or any other tool that can benefit from remote retrieval of raw XML data. This technique will prove to be effective for applications that focus primarily on data retrieval and manipulation rather then the extensive application of business rules and logic. Because the returned data is already in XML form, you can use XSLT extensively to sort, format, and enhance the output of your data.
