Exposing Existing Code as a Web Service Using SQL Server 2000

Steve Kirk and Priya Dhawan
Microsoft Developer Network

Revised August 2001

Summary: This article covers exposing existing stored procedure code as a Web Service using the XML features of Microsoft SQL Server 2000. (6 printed pages)

Download bdadotnet_beta2.msi.

Contents

Introduction
Existing Code in SQL Server 2000
   SQL Server 2000 XML Templates
Conclusion

Introduction

The XML features of Microsoft® SQL Server™ 2000 can simplify the task of exposing existing code as a Web Service. This article focuses on transformations made between data as passed to and from Transact SQL code and the XML messages used between the Web Service client and server.

The data transformation issues covered in this article are not the only issues to consider when evaluating the suitability of existing code for exposure as a Web Service. Other considerations include state models, return data sizes, how success is indicated, how error information is returned, the security model (including access control, authentication, and encryption), the execution model (synchronous or asynchronous), how the code is to be distributed, and the transaction model (COM+ transactions or declarative transactions). These issues will be covered in upcoming Architectural Topics articles.

Existing Code in SQL Server 2000

The XML features of SQL Server 2000 simplify exposing existing Transact SQL code as a Web Service. Two XML features of SQL Server 2000 contribute to this capability:

• Extensions to Transact SQL transform relational data to XML and enable parsing of incoming XML.
• The ISAPI Template feature allows an incoming HTTP request to be applied to Transact SQL code and outgoing XML to be transformed using an XSL style sheet. SQL Server can return XML to the XML template as long as data can be SELECTED with the FORXML clause.

SQL Server 2000 XML Templates

SQL Server 2000 XML templates transparently perform the following tasks:

• Decode the incoming HTTP request
• Apply parameters to Transact SQL query
• Execute query
• Transform outgoing XML with XSL

Read data

In the following example, Transact SQL specified in the ISAPI Template is executed. If necessary the HTTP Request can be passed to and parsed by Transact SQL Code. The returned XML is transformed into SOAP according to the .xsl file specified in the template and returned to the Web Service consumer:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="BDAdotNetWebService3Example1.xsl">
  <Orders>
    <sql:query>
      Exec GetOrdersXML
    </sql:query>
  </Orders>
</ROOT>

Here is the XSL style sheet referenced in the template that transforms XML from the stored procedure into SOAP:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:m="Some-URI">
<xsl:template match="/">
<SOAP-ENV:Envelope>
   <SOAP-ENV:Body>
       <m:BDAdotNetWebService3Example1Response >
         <xsl:copy-of select="//Orders"/>
       </m:BDAdotNetWebService3Example1Response>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
</xsl:template>
</xsl:stylesheet>

Finally, here is the stored procedure code that returns the XML using the FOR XML EXPLICIT clause on a Transact SQL SELECT. Orders and Order Details are SELECTED from separate tables and combined into an XML hierarchy:

/* Orders are the parent XML elements */
Select 1 as Tag, NULL as Parent,
   Orders.OrderId AS [Order!1!OrderId],
   Orders.OrderStatus AS [Order!1!OrderStatus],
   Orders.OrderDate AS [Order!1!OrderDate],
   Orders.SubTotal AS [Order!1!SubTotal],
   Orders.Tax AS [Order!1!Tax],
   Orders.ShippingHandling AS [Order!1!ShippingHandling],
   Orders.ShipToName AS [Order!1!ShipToName],
   Orders.ShipToAddressId AS [Order!1!ShipToAddressId],
   NULL AS [OrderDetail!2!OrderDetailId],
   NULL AS [OrderDetail!2!OrderId],
   NULL AS [OrderDetail!2!ItemId],
   NULL AS [OrderDetail!2!UnitPrice],
   NULL AS [OrderDetail!2!Quantity]
from Orders 
UNION ALL
/* Order details are the child XML elements */
select 2 as tag, 1 as parent,
   Orders.OrderId AS [Order!1!OrderId],
   NULL AS [Order!1!OrderStatus],
   NULL AS [Order!1!OrderDate],
   NULL AS [Order!1!SubTotal],
   NULL AS [Order!1!Tax],
   NULL AS [Order!1!ShippingHandling],
   NULL AS [Order!1!ShipToName],
   NULL AS [Order!1!ShipToAddressId],
   OrderDetails.OrderDetailId AS [OrderDetail!2!OrderDetailId],
   OrderDetails.OrderId AS [OrderDetail!2!OrderId],
   OrderDetails.ItemId AS [OrderDetail!2!ItemId],
   OrderDetails.UnitPrice AS [OrderDetail!2!UnitPrice],
   OrderDetails.Quantity AS [OrderDetail!2!Quantity]
from Orders, OrderDetails
where Orders.OrderId = OrderDetails.OrderId
ORDER BY [Order!1!OrderId],[OrderDetail!2!OrderDetailId]
For XML EXPLICIT

Note   See Example 1 in the BDAdotNetWebServices3.vb sample code (see top of article for download).

Write data

In the following example, XML representing hierarchical row data is supplied in the HTTP request and passed to Transact SQL code as specified in the ISAPI Template. The XML is parsed in a stored procedure and the appropriate writes are made:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="BDAdotNetWebService3Example2.xsl">
  <sql:header>
    <sql:param name='SOAPENV' />  
  </sql:header>                                          
  <Orders>
    <sql:query>
      Exec InsertOrderXML @SOAPENV
    </sql:query>
  </Orders>
</ROOT>

Here is the stored procedure code that parses the XML data using sp_preparedocument system stored procedure and inserts data into Orders and OrderDetails tables using the OPENXML method:

Create  Procedure InsertOrderXML 
@SOAPENV   NVARCHAR(4000) = NULL
AS
   SET NOCOUNT ON
   DECLARE @hDoc INT
   DECLARE @PKId  INT
   SET XACT_ABORT ON
   BEGIN TRANSACTION
   EXEC sp_xml_preparedocument @hDoc OUTPUT, @SOAPENV
   INSERT Orders(CustomerId,
         OrderDate,
         ShipToName,
         ShipToAddressId,
         OrderStatus)
   SELECT * 
   FROM OPENXML(@hDoc, '//Order') 
   WITH ( CustomerId int 'CustomerId',
          OrderDate Datetime 'OrderDate',
          ShipToName nvarchar(40) 'ShipToName',
          ShipToAddressId int 'ShipToAddressId',
          OrderStatus  int 'OrderStatus')
   SELECT @PKId = @@IDENTITY
   INSERT OrderDetails (OrderId,
                       ItemId,
                       UnitPrice,
                       Quantity) 
   SELECT @PKId as OrderId, ItemId, UnitPrice, Quantity
   FROM OPENXML(@hDoc, '//OrderDetail') 
   WITH ( ItemId int 'ItemId',
          UnitPrice money 'UnitPrice',
          Quantity int 'Quantity')
   COMMIT TRANSACTION
   EXEC sp_xml_removedocument @hDoc 
 

Note   See Example 2 in the BDAdotNetWebServices3.vb sample code (see top of article for download).

Conclusion

This article and accompanying examples cover data transformations that enable existing Transact SQL code to be exposed as a Web Service with the XML features of SQL Server 2000. This article focuses on transformations made between data as passed to and from Transact SQL code and the SOAP messages used between the Web Service client and server.

The performance of these solutions varies and is also affected by the size of the data being passed. Look for comparisons of these implementations in an upcoming article in this series.

Interface is only one of many things to consider when evaluating the suitability of existing code as a Web Service. Other considerations are security (including authorization, authentication, and encryption), transaction model, state model, the way errors and results are returned, and whether the code executes synchronously or asynchronously.