alter procedure [mig_translation].[TranslationValueset_Update] @valuesetXml xml -- The xml is constructed by stored procedure [mig_translation].[ListValuesets] in the migration db -- -- Xml sample (displayTextID and descriptionTextId are optional) -- -- -- 7eef8e2d-0429-4786-9949-4f207d372ceb -- TranslateBankUnits -- 7eef8e2d-0429-4786-9949-4f207d372ceb -- a5411722-071c-43ac-9963-2d4a6d6055c3 -- -- -- 12a1f01e-38b2-438c-882c-1f2f6b133bf8 -- TranslateSegments -- -- -- BankUnits display name -- -- -- BankUnits decription -- -- as -- Update Translation Valuesets merge [mig_translation].[TranslationValueset] as tgt using ( select [VsElement].value('(*/id)[1]', 'uniqueidentifier') as [ValuesetID] , [VsElement].value('(*/name)[1]', 'nvarchar(max)') as [Name] , [VsElement].value('(*/displayTextID)[1]', 'uniqueidentifier') as [DisplayTextID] , [VsElement].value('(*/descriptionTextID)[1]', 'uniqueidentifier') as [DescriptionTextID] from ( select T.c.query('.') as [VsElement] from @valuesetXml.nodes('/*/valueset') T(c) ) as X ) as src on tgt.[ValuesetID] = src.[ValuesetID] when matched then update set tgt.[Present] = 1 , tgt.[DisplayTextID] = src.[DisplayTextID] , tgt.[DescriptionTextID] = src.[DescriptionTextID] when not matched then insert ( [ValuesetID] , [Name] , [Present] , [DisplayTextID] , [DescriptionTextID] ) values ( src.[ValuesetID] , src.[Name] , 1 , src.[DisplayTextID] , src.[DescriptionTextID] ) when not matched by source then update set tgt.[Present] = 0; -- Update LanguageTexts used by Translation Valuesets declare @textID uniqueidentifier declare @xml xml declare @runDate datetime2 = sysdatetime() declare csr cursor for select [VsElement].value('(*/@id)[1]', 'uniqueidentifier') as [ItemID] , [VsElement] as [Xml] from ( select T.c.query('.') as [VsElement] from @valuesetXml.nodes('/*/smartText') T(c) ) as X open csr fetch next from csr into @textID, @xml while @@fetch_status = 0 begin exec [mig_director].[LanguageText_Insert] @textID, @xml, @runDate fetch next from csr into @textID, @xml end close csr deallocate csr go