Using SQL Server to provide existing code as a Web service

Server|web Summary: This article describes how to use the XML features of Microsoft SQL Server 2000 to provide existing stored procedure Code as a Web service.

Directory
Brief introduction
Existing code in SQL Server 2000
Summarize

Brief introduction
Microsoft®sql server™2000 XML functionality simplifies the task of providing existing code as a WEB service. This article focuses on the transformation between incoming and outgoing Transact SQL code data and XML messages (used between WEB service clients and servers).

The data transformation issues discussed in this article are not the only issues that need to be considered when evaluating whether existing code is appropriate for WEB service delivery. Other factors to be considered include the state model, the size of the data returned, how to indicate success, how to return an error message, the security model (including access control, authentication, and encryption), the execution model (synchronous or asynchronous), how to distribute the code, and the transaction model (COM + transaction or declaration transaction), and so on. These issues will be discussed in an upcoming architecture topic (English) article.

Existing code in SQL Server 2000
SQL Server 2000 's XML capabilities simplify the process of providing existing Transact SQL code as a Web service. This relies on two XML features in SQL Server 2000:

Extensions to Transact SQL convert relational data to XML and parse the incoming XML.
The ISAPI template feature allows incoming HTTP requests to be applied to Transact SQL code, and you can use XSL style sheets to convert outgoing XML. SQL Server can return XML to an XML template whenever you can use the Forxml clause to "select" Data.
SQL Server XML Template
SQL Server XML Templates perform the following tasks transparently:

Decoding incoming HTTP requests
Apply a parameter to a Transact SQL query
Execute Query
Using XSL to transform outgoing XML
Reading data
The following example executes the Transact SQL specified in the ISAPI template. If necessary, the HTTP request can be passed to the Transact SQL code and parsed by the code. Depending on the. xsl file specified in the template, the returned XML is converted to SOAP and returned to the customer of the Web service:

<root xmlns:sql= "Urn:schemas-microsoft-com:xml-sql" sql:xsl= "bdadotnetwebservice3example1.xsl" >
<Orders>
<sql:query>
Exec Getordersxml
</sql:query>
</Orders>
</ROOT>
The following is the XSL stylesheet referenced in the template that converts XML in a stored procedure to 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, the following stored procedure code uses the FOR XML EXPLICIT clause in the Transact SQL SELECT statement to return the XML. Order and order details are selected from a separate table and then merged into the XML hierarchy:

/* Order is parent XML element * *
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 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
Write Data
The following example provides XML that represents a hierarchical row of data through an HTTP request and then passes it to the Transact SQL code specified in the ISAPI template. Parse the XML in a stored procedure and make the appropriate write operation:

Create Procedure InsertOrder
@Order NVARCHAR (4000) = NULL,
@OrderId int Output
-
DECLARE @hDoc INT
DECLARE @PKId INT
BEGIN TRANSACTION
/* Load XML into the document for analysis * *
EXEC sp_xml_preparedocument @hDoc OUTPUT, @Order
/* Insert order header/*
INSERT Orders (CustomerId,
OrderDate,
Shiptoname,
Shiptoaddressid,
Orderstatus)
SELECT *
From OPENXML (@hDoc, '/newdataset/orders ')
With (CustomerId int ' CustomerId ',
OrderDate Datetime ' OrderDate ',
Shiptoname nvarchar ' shiptoname ',
shiptoaddressid int ' Shiptoaddressid ',
orderstatus int ' orderstatus ')
SELECT @PKId = @ @IDENTITY
/* Insert Order Details/*
INSERT OrderDetails (OrderId,
ItemId,
UnitPrice,
Quantity)
SELECT @PKId as OrderId, ItemId, UnitPrice, Quantity
From OPENXML (@hDoc, '/newdataset/details ')
With (ItemId int ' ItemId ',
UnitPrice money ' UnitPrice ',
Quantity int ' Quantity ')
/* Specify the value of the output parameter * *
Select @OrderId = @PKId
COMMIT TRANSACTION
/* Clear XML Document * *
EXEC sp_xml_removedocument @hDoc
Summarize
This article, along with the accompanying examples, describes information about data transformations. With Data transformation, you can use SQL Server 2000 XML functionality to provide existing Transact SQL code as a WEB service. This article focuses on the conversion between the incoming and outgoing Transact SQL code data and SOAP messages (used between WEB service clients and servers).

The performance of these solutions varies and is affected by the size of the data being passed. In later articles in this series, we will compare these implementations.

When evaluating the suitability of existing code as a WEB service, interfaces are but one of many factors that should be considered. Other factors to consider include security (including authorization, authentication and encryption), transaction model, state model, ways to return errors and results, and whether the code is synchronized or asynchronous, and so on.