USP_BBNC_DECRYPTGIFTDATA

            CREATE procedure dbo.USP_BBNC_DECRYPTGIFTDATA
            (
                @GIFTINFORMATION xml = null output,
                @KEYISALREADYOPEN bit = 0
            )
            as
            set nocount on;

            --Don't convert a null @GIFTINFORMATION to an empty string 

            if @GIFTINFORMATION is null 
                return 0;

            begin try
                if @KEYISALREADYOPEN = 0 
                    exec dbo.USP_GET_KEY_ACCESS;

                declare @NEWGIFTINFORMATION xml
                set @NEWGIFTINFORMATION = 
                (    
                    select
                        @GIFTINFORMATION as "*",
                        cast(DecryptByKey(@GIFTINFORMATION.value
                        (
                            '(/GIFTINFORMATION/ITEM[FIELDKEY = "CREDITCARDNUMBER"]/NEWVALUE/text())[1]',
                            'varbinary(8000)'
                        )) as nvarchar(4000)) as [DECRYPTEDCCNUMBER],
                        cast(DecryptByKey(@GIFTINFORMATION.value
                        (
                            '(/GIFTINFORMATION/ITEM[FIELDKEY = "ACCOUNTNUMBER"]/NEWVALUE/text())[1]',
                            'varbinary(8000)'
                        )) as nvarchar(4000)) as [DECRYPTEDACCOUNTNUMBER]
                    for xml path(''), binary base64, type
                )

                begin try
            set @NEWGIFTINFORMATION.modify
                    (
                        'replace value of
                            (/GIFTINFORMATION/ITEM[FIELDKEY = "CREDITCARDNUMBER"]/NEWVALUE/text())[1]
                        with
                            (/DECRYPTEDCCNUMBER/text())[1]'        
                    );

                    set @NEWGIFTINFORMATION.modify
                    (
                        'replace value of
                            (/GIFTINFORMATION/ITEM[FIELDKEY = "ACCOUNTNUMBER"]/NEWVALUE/text())[1]
                        with
                            (/DECRYPTEDACCOUNTNUMBER/text())[1]'        
                    );
        end try
        begin catch
              -- if we have an issue decrypting, just replace with a blank

              set @NEWGIFTINFORMATION.modify
                    (
                        'replace value of
                            (/GIFTINFORMATION/ITEM[FIELDKEY = "CREDITCARDNUMBER"]/NEWVALUE/text())[1]
                        with
                            ("")[1]'        
                    );

                    set @NEWGIFTINFORMATION.modify
                    (
                        'replace value of
                            (/GIFTINFORMATION/ITEM[FIELDKEY = "ACCOUNTNUMBER"]/NEWVALUE/text())[1]
                        with
                            ("")[1]'        
                    );
        end catch


                set @NEWGIFTINFORMATION.modify
                (
                    'delete
                        /DECRYPTEDCCNUMBER[1]'
                );

                set @NEWGIFTINFORMATION.modify
                (
                    'delete
                        /DECRYPTEDACCOUNTNUMBER[1]'
                );

                set @GIFTINFORMATION = @NEWGIFTINFORMATION;

                if @KEYISALREADYOPEN = 0 
                    close symmetric key sym_BBInfinity;
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;

                if @KEYISALREADYOPEN = 0 
                    close symmetric key sym_BBInfinity;

                set @GIFTINFORMATION = null;
                return 0;                    
            end catch

            return 0;