spConvertEventTransactionsForBBPS

            create procedure [dbo].[spConvertEventTransactionsForBBPS]
            as
            begin
                declare @temp table (ID int, data xml) 

                insert into @temp (ID, data) 
                    select eventtransactionsID, convert(xml, REPLACE(convert(nvarchar(max),XMLObjectData), 'encoding="utf-8"',    '')) 
                    from eventtransactions 
                    where processed_date is not null

                update @temp set data.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                replace value of (/ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber/text())[1]
                with "****************" ')
                where data.exist('declare namespace ns="urn:blackbaud.RE7.XDATA"; /ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber')=1
                and (substring(ltrim(data.value('declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber)[1]','nvarchar(20)')), 1, 4)<>'****')

                update eventtransactions set XMLObjectData = '<?xml version="1.0" encoding="utf-8"?>' + convert(nvarchar(max),t.data) 
                from @temp t inner join eventtransactions on t.id=eventtransactions.eventtransactionsID

                delete from @temp

                --Transactions Table
                insert into @temp (ID, data) 
                    select t.ID, convert(xml, REPLACE(convert(nvarchar(max),t.Data), 'encoding="utf-8"',    '')) 
                    from dbo.Transactions t inner join dbo.EventTransactions et on t.TransactionID = et.EventTransactionsID
                    where t.TransactionType = '{172A5D9A-5241-493c-A2C2-EAC164C095B0}' and et.processed_date is not null

                update @temp set data.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                replace value of (/ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber/text())[1]
                with "****************" ')
                where data.exist('declare namespace ns="urn:blackbaud.RE7.XDATA"; /ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber')=1
                and (substring(ltrim(data.value('declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyEventRegTran/ns:Gift/ns:CreditCardNumber)[1]','nvarchar(20)')), 1, 4)<>'****')

                update dbo.Transactions set Data = '<?xml version="1.0" encoding="utf-8"?>' + convert(nvarchar(max),t.data) 
                from @temp t inner join dbo.Transactions on t.id=Transactions.ID
            end