spConvertDonationTransactionsForBBPS

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

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

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

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

                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.donationtransactions dt on t.TransactionID = dt.DonationTransactionsID
                    where t.TransactionType = '{5705B543-4033-4a3a-BBCD-A731403EE1E6}' and dt.processed_date is not null

                update @temp set data.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                replace value of (/ns:ShelbyDonationTran/ns:Gift/ns:CreditCardNumber/text())[1]
                with "****************" ')
                where data.exist('declare namespace ns="urn:blackbaud.RE7.XDATA"; /ns:ShelbyDonationTran/ns:Gift/ns:CreditCardNumber')=1
                and (substring(ltrim(data.value('declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyDonationTran/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