使用SQL2000将现有代码作为Web服务提供

/* 订单是父 XML 元素 */
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

/* 订单详细信息是子 XML 元素 */
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

Create Procedure InsertOrder 
@Order NVARCHAR(4000) = NULL,
@OrderId int Output
-
DECLARE @hDoc INT
DECLARE @PKId INT 
BEGIN TRANSACTION
/* 将 XML 载入文档以进行分析 */ 
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