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)
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.
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 transparently perform the following tasks:
- Decode the incoming HTTP request
- Apply parameters to Transact SQL query
- Execute query
- Transform outgoing XML with XSL
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).
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).
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.