ALTER   PROCEDURE [dbo].[SaveSalesOrderV]
(
	/*
		In this version we are using XML data type, instead of TEXT.
	*/
	@OrderInfo XML
)
AS

SET NOCOUNT ON

/*
	The next point that needs attention is that we are using Structured Exception Handling
	capability of SQL Server 2005. The code below is within a  TRY..CATCH block. In the previous
	version we used @@ERROR system global variable to detect errors. 
*/
BEGIN TRY
	BEGIN TRAN

	/*
		We dont need the XML document handle any more. 
		
		DECLARE @hdoc INT    
		EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo
	*/

	-- check if ORDER already exists
	DECLARE @OrderID INT
	DECLARE @OrderNumber VARCHAR(20)

	/*
		SELECT @orderNumber = x.OrderNumber 
			FROM OPENXML( @hdoc, '/salesOrder', 1 ) WITH (orderNumber VARCHAR(20) ) AS x
			
		We will no more use OPENXML to access the information stored inside the XML variable.
		The code below uses XQuery retrieve the information from the order information.
	*/
	
	SELECT @orderNumber = x.header.value('@orderNumber[1]', 'varchar(20)')
		FROM @OrderInfo.nodes('//salesOrder') as x(header)

	SELECT @OrderID = OrderID 
		FROM orderHeader WHERE OrderNumber = @OrderNumber

	/*
		If the order does not exist, insert it.
		Once again, we are using XQuery to retrieve information from the XML variable. 
	*/
	
	IF @OrderID IS NULL BEGIN
		INSERT INTO OrderHeader (OrderNumber, CustomerNumber, OrderDate)	
		SELECT 
			x.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
			x.header.value('@customerNumber[1]', 'varchar(20)') as customerNumber,
			x.header.value('@orderDate[1]', 'datetime') as OrderDate
			FROM @OrderInfo.nodes('//salesOrder') as x(header)

		SET @OrderID = SCOPE_IDENTITY()
	END ELSE BEGIN
		/*
			Here, we are using yet another new feature of SQL Server 2005, CTE. In the code below
			it is not necessary to use a Common Table Expression (CTE). However, I used it for
			making the code simpler to read and understand.
		*/
		;WITH orderInfo AS 
		(
			SELECT 
				x.header.value('@orderNumber[1]', 'varchar(20)') AS OrderNumber,
				x.header.value('@customerNumber[1]', 'varchar(20)') AS CustomerNumber,
				x.header.value('@orderDate[1]', 'datetime') AS OrderDate
			FROM @OrderInfo.nodes('//salesOrder') AS x(header)
		)

		UPDATE orderHeader SET
			customerNumber = x.CustomerNumber,
			orderDate = x.OrderDate
		FROM orderHeader h
		INNER JOIN orderInfo x ON (x.OrderNumber = h.OrderNumber)
	END

	/*
		delete any items which does not exist in the XML but exists in the table.
		Again, uses XQuery to access the information inside the XML variable.
	*/
	DELETE FROM OrderDetailsIV 
		WHERE OrderHeaderID = @OrderID
		AND ItemNumber NOT IN 
			( 
				SELECT 
					x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber
					FROM @OrderInfo.nodes('//item') AS x(item)
			)

	/*
		Update any existing records.
		Again, we are using another new KEYWORD introduced with SQL Server 2005. Note the usage of
		CROSS APPLY, which returns a resultset comprising of the "Order Number" from the order header
		element and other item information from the item details part of the XML structure.
	*/
	;WITH itemInfo AS(
		SELECT 
			y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
			x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
			x.item.value('@qty[1]','int') AS Qty,
			x.item.value('@rate[1]','float') AS Rate
		FROM @OrderInfo.nodes('//item') AS x(item)
		CROSS APPLY @OrderInfo.nodes('//salesOrder') AS y(header)
	)
	UPDATE OrderDetailsIV SET
		Qty = x.Qty,
		Rate = x.Rate
	FROM OrderDetailsIV o
	INNER JOIN itemInfo x ON (x.OrderNumber = o.OrderNumber AND o.ItemNumber = x.ItemNumber)

	/*
		Insert new records.
		Note usage of CTE, CROSS APPLY and XQuery.
	*/
	;WITH itemInfo AS(
		SELECT 
			y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
			x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
			x.item.value('@qty[1]','int') AS Qty,
			x.item.value('@rate[1]','float') AS Rate
		FROM @OrderInfo.nodes('//item') AS x(item)
		CROSS APPLY @OrderInfo.nodes('//salesOrder') as y(header)
	)
	INSERT INTO OrderDetailsIV( OrderHeaderID, OrderNumber, ItemNumber, Qty, Rate)
	SELECT @OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
		FROM itemInfo AS x
		WHERE x.itemNumber NOT IN ( 
			SELECT itemNumber FROM OrderDetailsIV WHERE orderHeaderID = @orderID )

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	/*
		Rollback the transaction in case of an error.
	*/
	IF XACT_STATE() <> 0
	BEGIN
		ROLLBACK TRANSACTION
	END
END CATCH