USP_PLANNEDGIFT_ASSETS_UPDATEFROMXML

            CREATE procedure dbo.USP_PLANNEDGIFT_ASSETS_UPDATEFROMXML
            (
                @PLANNEDGIFTID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
               [ADDRESSID] uniqueidentifier,
               [ASSETTYPECODEID] uniqueidentifier,
               [ASSETVALUATIONMETHODCODEID] uniqueidentifier,
               [COSTBASIS] money,
               [DESCRIPTION] nvarchar(1000),
               [ID] uniqueidentifier,
               [SEQUENCE] int,
               [VALUATIONSOURCE] nvarchar(200),
               [VALUE] money,
               [ASSETHOLDERS] xml)

            insert into @TempTbl select 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE],
                [ASSETHOLDERS]
            from dbo.UFN_PLANNEDGIFT_ASSETS_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            declare @contextCache varbinary(128);
            declare @e int;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete any items that no longer exist in the XML table

            delete from dbo.[PLANNEDGIFTASSET] where [PLANNEDGIFTASSET].ID in 
                (select ID from dbo.UFN_PLANNEDGIFT_ASSETS
                (
                    @PLANNEDGIFTID
                )
                EXCEPT select ID from @TempTbl)    

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            update [PLANNEDGIFTASSET]
                    set [PLANNEDGIFTASSET].[ADDRESSID]=temp.[ADDRESSID],
                    [PLANNEDGIFTASSET].[ASSETTYPECODEID]=temp.[ASSETTYPECODEID],
                    [PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID]=temp.[ASSETVALUATIONMETHODCODEID],
                    [PLANNEDGIFTASSET].[COSTBASIS]=temp.[COSTBASIS],
                    [PLANNEDGIFTASSET].[DESCRIPTION]=temp.[DESCRIPTION],
                    [PLANNEDGIFTASSET].[ID]=temp.[ID],
                    [PLANNEDGIFTASSET].[SEQUENCE]=temp.[SEQUENCE],
                    [PLANNEDGIFTASSET].[VALUATIONSOURCE]=temp.[VALUATIONSOURCE],
                    [PLANNEDGIFTASSET].[VALUE]=temp.[VALUE],
                    [PLANNEDGIFTASSET].CHANGEDBYID = @CHANGEAGENTID,
                    [PLANNEDGIFTASSET].DATECHANGED = @CHANGEDATE

                from dbo.[PLANNEDGIFTASSET] inner join @TempTbl as [temp] on [PLANNEDGIFTASSET].ID = [temp].ID
                where ([PLANNEDGIFTASSET].[ADDRESSID]<>temp.[ADDRESSID]) or 
                    ([PLANNEDGIFTASSET].[ADDRESSID] is null and temp.[ADDRESSID] is not null) or 
                    ([PLANNEDGIFTASSET].[ADDRESSID] is not null and temp.[ADDRESSID] is null) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID]<>temp.[ASSETTYPECODEID]) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID] is null and temp.[ASSETTYPECODEID] is not null) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID] is not null and temp.[ASSETTYPECODEID] is null) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID]<>temp.[ASSETVALUATIONMETHODCODEID]) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID] is null and temp.[ASSETVALUATIONMETHODCODEID] is not null) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID] is not null and temp.[ASSETVALUATIONMETHODCODEID] is null) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS]<>temp.[COSTBASIS]) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS] is null and temp.[COSTBASIS] is not null) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS] is not null and temp.[COSTBASIS] is null) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION]<>temp.[DESCRIPTION]) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION] is null and temp.[DESCRIPTION] is not null) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION] is not null and temp.[DESCRIPTION] is null) or 
                    ([PLANNEDGIFTASSET].[ID]<>temp.[ID]) or 
                    ([PLANNEDGIFTASSET].[ID] is null and temp.[ID] is not null) or 
                    ([PLANNEDGIFTASSET].[ID] is not null and temp.[ID] is null) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE]<>temp.[SEQUENCE]) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE]<>temp.[VALUATIONSOURCE]) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE] is null and temp.[VALUATIONSOURCE] is not null) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE] is not null and temp.[VALUATIONSOURCE] is null) or 
                    ([PLANNEDGIFTASSET].[VALUE]<>temp.[VALUE]) or 
                    ([PLANNEDGIFTASSET].[VALUE] is null and temp.[VALUE] is not null) or 
                    ([PLANNEDGIFTASSET].[VALUE] is not null and temp.[VALUE] is null)

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [PLANNEDGIFTASSET] 
                ([PLANNEDGIFTID], 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select @PLANNEDGIFTID, 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[PLANNEDGIFTASSET] as data where data.ID = [temp].ID)

            declare @PLANNEDGIFTASSETID uniqueidentifier;
            declare @ASSETHOLDERS xml;
            declare ASSETCURSOR cursor local fast_forward for
                select
                    [temp].ID,
                    ASSETHOLDERS
                from
                    @TempTbl as [temp]
            open ASSETCURSOR
            fetch next from ASSETCURSOR into @PLANNEDGIFTASSETID, @ASSETHOLDERS;
            while @@FETCH_STATUS = 0
            begin
                exec dbo.USP_PLANNEDGIFT_ASSETHOLDERS_UPDATEFROMXML @PLANNEDGIFTASSETID, @ASSETHOLDERS, @CHANGEAGENTID, @CHANGEDATE; 
                fetch next from ASSETCURSOR into @PLANNEDGIFTASSETID, @ASSETHOLDERS;
            end
            close ASSETCURSOR
            deallocate ASSETCURSOR

            if @@Error <> 0
                return 4;

            return 0;