Advanced Search

PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE

/ Published in: SQL
Save to your folder(s)
PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE PROCEDURE dbo.OrderSave
  2. @ORDER AS XML
  3. AS
  4. BEGIN
  5. BEGIN TRY
  6. 	-- Add the Order object into a temp table.
  7. 	SELECT 
  8. 		ORDER.value('OrderId[1]', 'INT') AS OrderId,
  9. 		ORDER.value('OrderItemId[1]', 'INT') AS OrderItemId,
  10. 		ORDER.value('SegmentId[1]', 'INT') AS SegmentId,
  11. 		ORDER.value('MessageType[1]', 'INT') AS MessageType,
  12. 		ORDER.value('JobId[1]', 'INT') AS JobId,
  13. 		ORDER.value('CreatedDateTime[1]', 'DATETIME') AS CreatedDateTime
  14. 	INTO #TempOrder
  15. 	FROM @ORDER.nodes('//Order') T1(ORDER);
  16.  
  17. 	BEGIN TRANSACTION
  18. 		MERGE Orders AS Target
  19. 		USING
  20. 		(
  21. 		SELECT OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime FROM Orders
  22. 		)
  23. 		AS SOURCE (OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime)
  24. 		ON Target.OrderId = SOURCE.OrderId
  25. 		WHEN MATCHED THEN
  26. 			UPDATE SET JobId = SOURCE.JobId, CreatedDateTime = SOURCE.CreatedDateTime
  27. 		WHEN NOT MATCHED THEN
  28. 			INSERT (OrderItemId, SegmentId, MessageType)
  29. 			VALUES (SOURCE.OrderItemId, SOURCE.SegmentId, SOURCE.MessageType)
  30. 	COMMIT
  31.  
  32. 	-- Now Drop the temp table.
  33. 	DROP TABLE #TempOrder;
  34.  
  35. 	END TRY
  36. 	BEGIN CATCH   
  37. 	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
  38. 	DECLARE @error_state INT; SET @error_state = CASE WHEN error_number() < 50000 THEN 1 ELSE error_state() END; -- system-generated             
  39.  
  40. 	IF @@trancount > 0 ROLLBACK TRAN; -- if there's an open transaction, roll it back
  41. 	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)
  42.  
  43. 	RETURN error_number();
  44. END CATCH
  45. END
  46. GO

URL: www.designpuddle.com

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.