Initial Setup for the SOAP Sample Applications

The initial setup process that is described in this section is common to all SOAP sample applications in the Microsoft® SQLXML 3.0 documentation. In this initial setup, you create a virtual directory and a virtual name of soap type. Then you configure the virtual name and add the sample stored procedures and template that are provided in the following discussion.

The configuration process creates the Web Services Description Language (WSDL) file. The clients in the sample applications that are provided in Sample Applications for Sending SOAP Requests use this WSDL file to send SOAP requests.

As part of the initial set up, you create the following:

• GetCustomerInfo and GetCustAndOrderInfo stored procedures
  
  
• UDFReturningATable user-defined function (UDF)
  
  
• SampleTemplate.xml template file

About the GetCustomerInfo Stored Procedure

The GetCustomerInfo stored procedure executes three statements. The second statement (an INSERT statement) will fail in order to illustrate how errors are returned as SqlMessage type objects and how you process error messages on the client. During configuration of the virtual name of soap type, two WSDL operations (methods) are added that map to this stored procedure. The only difference between these WSDL operations is in how they are configured to return the output:

• The WSDL operation GetCustInfoAsXMLElementObjects returns output as XML objects in the object array.
  
  
• The WSDL operation GetCustInfoAsDataSetObjects returns the output as DataSet objects in the object array.

This Microsoft Visual Studio® .NET client application illustrates how to determine the object types that are returned in the array and process them accordingly. To simplify the application, the client application displays the object content.

About the GetCustAndOrderInfo Stored Procedure

The GetCustAndOrderInfo stored procedure executes only one statement, and it maps to a WSDL operation that returns a single DataSet object. This Visual Studio .NET client application illustrates how to process the DataSet object that is returned. Again, to simplify the application, the client application displays the object content.

About the UDFReturningATable User-defined Function

The UDFReturningATable user-defined function (UDF) returns a table. The WSDL operation that corresponds to this function is configured to return a single DataSet object, which the Visual Studio .NET client application processes accordingly.

About the SampleTemplate.xml Template

The Template.xml sample template executes two statements. The first statement will fail in order to illustrate how the errors are returned and processed on the client. The WSDL operation that corresponds this template is configured to return output as XML objects, which the Visual Studio .NET client application then processes accordingly.

Step1: Creating a sample stored procedure and an XML template

1. In the Northwind database, create the following stored procedures (GetCustomerInfo and GetCustAndOrderInfo) and user-defined function (UDFReturningATable):

   CREATE PROCEDURE GetCustomerInfo 
                       @CustomerID nchar(5), 
                       @OutputParam nchar(5) OUTPUT  
   AS  
   SELECT @OutputParam = '99999'  
   SELECT CustomerID, ContactName 
   FROM Customers 
   WHERE CustomerID = @CustomerID
   
   INSERT Customers (CustomerID) VALUES ('zzzzz')
   
   SELECT OrderID, OrderDate FROM Orders
   WHERE CustomerID = @CustomerID
   
   CREATE PROCEDURE GetCustAndOrderInfo 
                       @CustomerID nchar(5), 
                       @OutputParam nchar(5) OUTPUT  
   AS  
   SELECT @OutputParam = '99999'  
   SELECT C.CustomerID, ContactName, OrderID, OrderDate 
   FROM   Customers C, Orders O
   WHERE  C.CustomerID = O.CustomerID
   AND    C.CustomerID = @CustomerID
   
   CREATE FUNCTION UDFReturningATable ( @CustomerID nchar(5) )
   RETURNS @OrderTable TABLE
      (
       CustomerID    nchar(5),
       OrderID       integer
      )
   AS
   BEGIN
      INSERT @OrderTable
           SELECT CustomerID, OrderID
           FROM   Orders
           WHERE  CustomerID = @CustomerID
      RETURN
   END
   

2. Create the nwind2 subfolder (for example, C:\Inetpub\wwwroot\nwind2). This is the subfolder to which the nwind2 virtual directory (which you will create later) points.
   
   
3. Open Notepad and add the following XML template to it. Note that the first SELECT statement fails. This is intentional to illustrate how errors are returned to the client.

   <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
     <sql:header>
          <sql:param name='CustomerID'>ALFKI</sql:param>
     </sql:header>
   <sql:query>
        SELECT  CustomerID,CompanyName 
        FROM    Customer
        WHERE   CustomerID=@CustomerID 
        FOR XML AUTO
     </sql:query>
     <sql:query>
        SELECT  top 5 [Order Details].OrderID, ProductID, UnitPrice, Quantity
        FROM    [Order Details], Orders
        WHERE   Orders.OrderID = [Order Details].OrderID
        AND     Orders.CustomerID = @CustomerID
        FOR XML AUTO
     </sql:query>
     
   </ROOT>
   

4. Save the file as SampleTemplate.xml in the nwind2 folder.

Step2: Creating the nwind2 virtual directory and soap virtual name

1. In the SQLXML 3.0 program group, click Configure IIS Support.
   
   
2. Expand a server, and then click the Web site you want (for example, Default Web Site).
   
   
3. On the Action menu, point to New; and then click Virtual Directory. The property page for the new virtual directory is displayed on the screen.
   
   
4. In the New Virtual Directory Properties dialog box, on the General tab, enter the name of the virtual directory (nwind2) and the physical directory path (C:\Inetpub\Wwwroot\nwind2). You also have the option of using the browse (...) button to select the directory.
   
   
5. On the Security tab, select SQL Server and enter the valid Microsoft® SQL Server™ login.
   
   
6. On the Data Source tab, in the SQL Server box, enter the name of a server computer (for example, (local)). And if more than one instance of SQL Server 2000 is installed on the specified computer, also enter the name of an instance of SQL Server 2000. In the Database box, enter Northwind as the name of the default database.
   
   
7. On the Settings tab, select the Allow POST option.
   
   
8. On the Virtual Names tab, click <New virtual name> to create the virtual name of type soap.
   
   
9. Enter soap as the virtual name in the Name text box. (Notice that this same name also appears in the Webservice text box.)
   
   
10. Select soap as the Type (virtual name type) from the drop-down list.
    
    
11. Specify the physical directory path that is associated with the virtual name (for example, C:\Inetpub\Wwwroot\nwind2). (This can be any folder on your computer.)
    
    
12. Enter soap as the Web Service Name.
    
    
13. Enter the domain name. If you do not specify the domain name, the name defaults to the name of the Microsoft Internet Information Services (IIS) server computer.
    
    
14. Click Save to save the virtual name.
    
    
15. Now you can configure the virtual name. In this process, you add five WSDL operations that map to the two stored procedures, to a user-defined function, and to the template that you previously created. Select the soap virtual name, and then click Configure.
    
    
16. In the Virtual Name Configuration dialog box:
    1. Select SP to add the first stored procedure to the configuration, and then click the browse (...) button.
       
       
    2. Select the GetCustomerInfo stored procedure from the list, and then click OK.
       
       
    3. Specify GetCustInfoAsXMLElementObjects in the method name text box. This will be the method name that appears in the WSDL file for the GetCustomerInfo stored procedure.
       
       
    4. Under Row Formatting, select Raw; and then under Output, select XML objects.
       
       
    5. Clear the Return errors as soap faults check box, and then click Save. This saves the change to the configuration, adding the first stored procedure to the configuration file.
       
       
    6. Select SP again to add the second stored procedure to the configuration, and then click the browse (...) button.
       
       
    7. Select the GetCustomerInfo stored procedure from the list, and then click OK.
       
       
    8. Specify GetCustInfoAsDataSetObjects in the method name text box. This will be the method name that appears in the WSDL file.
       
       
    9. Under Row Formatting, select Raw; and then under Output, select Dataset objects.
       
       
    10. Clear the Return errors as soap faults check box, and then click Save. This saves the change to the configuration, adding the second stored procedure to the configuration file.
        
        
    11. Select SP again to add the third stored procedure to the configuration, and then click the browse (...) button.
        
        
    12. Select the GetCustAndOrderInfo stored procedure from the list, and then click OK.
        
        
    13. Specify GetCustAndOrderInfoAsADataSet in the method name text box. This will be the method name that appears in the WSDL file.
        
        
    14. Under Row Formatting, select Nested; and then under Output, select Single Dataset.
        
        
    15. Click Save. This saves the change to the configuration, adding the third stored procedure to the configuration file.
        
        
    16. Select SP again to add the user-defined function (UDF) to the configuration, and then click the browse (...) button.
        
        
    17. Select the UDFReturningTable stored procedure from the list, and then click OK.
        
        
    18. Note that the name in the method name box is the same as the UDF name. Keep that name. This will be the method name that appears in the WSDL file.
        
        
    19. Under Row Formatting, select Raw; and then under Output, select Single Dataset.
        
        
    20. Clear the Return errors as soap faults check box, and then click Save. This saves the change to the configuration, adding the UDF to the configuration file.
        
        
    21. Select Template to add a template to the configuration, and then click the browse (...) button.
        
        
    22. Select SampleTemplate, and click OK. Note that the name in the method name text box is the same as the template name. Keep that name. This will be the method name that appears in the WSDL file for the SampleTemplate template.
        
        
    23. Clear the Return errors as soap faults check box, and then click Save. This saves the change to the configuration, adding the template to the configuration file.
        
        
    24. Click OK. This updates the configuration file with the stored procedures and a template. Corresponding Web Services Description (WSDL) files are created automatically.
17. During the development of the application, you might want to disable caching of mapping schemas, templates, and XSL style sheets. On the Advanced tab, select all of the check boxes under Caching options.
    
    
18. Click OK to save the virtual name (nwind2).

This creates a virtual directory nwind2 with a virtual name soap of soap type.