使用SQL Server 2000将现有代码作为Web服务提供

以下示例中，通过 HTTP请求提供表示层次行数据的 XML，然后将其传递到 ISAPI模板中指定的 Transact SQL代码。在存储过程中对 XML进行语法分析，并进行相应的写入操作：

Create Procedure InsertOrder
@Order NVARCHAR(4000) = NULL,
@OrderId int Output
DECLARE @hDoc INT
DECLARE @PKId INT
BEGIN TRANSACTION

EXEC sp_xml_preparedocument @hDoc OUTPUT, @Order

/*插入订单标头 */

INSERT Orders(CustomerId,
OrderDate,
ShipToName,
ShipToAddressId,
OrderStatus)
SELECT *
FROM OPENXML(@hDoc, '/NewDataSet/Orders')
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, '/NewDataSet/Details')
WITH ( ItemId int 'ItemId',
UnitPrice money 'UnitPrice',
Quantity int 'Quantity')

/*指定输出参数的值 */

Select @OrderId = @PKId
COMMIT TRANSACTION

/*清除 XML文档 */

EXEC sp_xml_removedocument @hDoc