Posted By

cjb_bjc on 12/09/15


Tagged

test


Versions (?)

PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE


 / Published in: SQL
 

URL: www.designpuddle.com

PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE

  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

Report this snippet  

You need to login to post a comment.