/ Published in: SQL
PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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
URL: www.designpuddle.com