CREATE  PROCEDURE [dbo].[SaveSalesOrderII]
(
	@OrderInfo text
)
AS

SET NOCOUNT ON

BEGIN TRAN

-- Initialize XML handle
DECLARE @hdoc INT    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo

-- Insert data to order header
INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)	
SELECT x.orderNumber, x.orderDate, x.customerNumber
	FROM OPENXML ( @hdoc, '/salesOrder', 1 ) WITH (
		orderNumber VARCHAR(20) '@orderNumber',
		customerNumber VARCHAR(20) '@customerNumber',
		orderDate DATETIME '@orderDate'
	) AS x

-- Take the IDENTITY of the new record
DECLARE @OrderID INT
SET @OrderID = SCOPE_IDENTITY()

-- Insert data to Order Details
INSERT INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)
SELECT @OrderID, x.ItemNumber, x.Qty, x.Rate
	FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item', 1 ) WITH (
		ItemNumber VARCHAR(20) '@itemNumber',
		Qty INT '@qty',
		Rate FLOAT '@rate'
	) AS x

-- Release XML handle
EXEC sp_xml_removedocument @hdoc

IF @@ERROR <> 0
	ROLLBACK TRAN
ELSE
	COMMIT TRAN