CREATE PROCEDURE dbo.OrderSave
@Order as XML
AS
BEGIN
BEGIN TRY
	-- Add the Order object into a temp table.
	SELECT 
		Order.value('OrderId[1]', 'INT') As OrderId,
		Order.value('OrderItemId[1]', 'INT') AS OrderItemId,
		Order.value('SegmentId[1]', 'INT') As SegmentId,
		Order.value('MessageType[1]', 'INT') As MessageType,
		Order.value('JobId[1]', 'INT') AS JobId,
		Order.value('CreatedDateTime[1]', 'DATETIME') AS CreatedDateTime
	INTO #TempOrder
	FROM @Order.nodes('//Order') T1(Order);

	BEGIN TRANSACTION
		MERGE Orders as Target
		USING
		(
		SELECT OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime From Orders
		)
		AS Source (OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime)
		ON Target.OrderId = Source.OrderId
		WHEN MATCHED THEN
			UPDATE SET JobId = source.JobId, CreatedDateTime = source.CreatedDateTime
		WHEN NOT MATCHED THEN
			INSERT (OrderItemId, SegmentId, MessageType)
			VALUES (source.OrderItemId, source.SegmentId, source.MessageType)
	COMMIT

	-- Now Drop the temp table.
	DROP TABLE #TempOrder;
	
	END TRY
	BEGIN CATCH   
	DECLARE @error_message VARCHAR(8000); SET @error_message = CASE WHEN error_number() < 50000 THEN error_message() + ' [in proc ' + error_PROCEDURE() + ' at line ' + CONVERT(VARCHAR, error_line()) + ']' ELSE error_message() END; -- user-generated can be shown to user
	DECLARE @error_state INT; SET @error_state = CASE WHEN error_number() < 50000 THEN 1 ELSE error_state() END; -- system-generated             

	IF @@trancount > 0 ROLLBACK TRAN; -- if there's an open transaction, roll it back
	RAISERROR(@error_message, 16, @error_state); -- error_state: 1 (system-generated), 2 (user-generated errors), 3 (user-generated warnings), 4 (user-generated info), 5 (user-generated question)

	RETURN error_number();
END CATCH
END
GO

www.designpuddle.com

PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE

PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE

SQL