Using SQL Server 2000 to provide existing code as a Web service

First, Introduction

Microsoft SQL Server 2000 's 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).

Ii. 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:

1. Extensions to Transact SQL can transform relational data into 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 as long as you can use the FOR XML clause to "select" Data.

2.SQL Server XML Template

SQL Server XML templates perform the following tasks transparently:

Decoding incoming HTTP requests apply parameters to Transact SQL query execution query use XSL Transform outgoing XML read 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:

Exec GetOrdersXML
以下是模板中引用的 XSL样式表，它将存储过程中的 XML转换为 SOAP：
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">

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