Browse DevX
Sign up for e-mail newsletters from DevX


Take Advantage of SQLXML with ASP.NET

Use SQLXML's Web services to access XML data directly from your ASP.NET applications.


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:

   <xsd:element name="CustOrdersDetail">
           <xsd:element minOccurs="0" maxOccurs="1" 
           name="OrderID" type="xsd:int" 
   <xsd:element name="CustOrdersDetailResponse">
           <xsd:element minOccurs="1" maxOccurs="1" 
           <xsd:element name="returnValue" 
              type="xsd:int" nillable="true"/>

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.

Thanks for your registration, follow us on our social networks to keep up-to-date