USP_MEMBERSHIP_GETMEMBER_UPDATEFROMXML

        CREATE PROCEDURE [dbo].[USP_MEMBERSHIP_GETMEMBER_UPDATEFROMXML] 
        (
            @MEMBERSHIPID 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 (
               [CONSTITUENTID] uniqueidentifier,
               [ID] uniqueidentifier,
               [ISPRIMARY] bit)

            insert into @TempTbl select 
                [CONSTITUENTID],
                [ID],
                [ISPRIMARY] 
            from dbo.UFN_MEMBERSHIP_GETMEMBERS_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

            update dbo.[MEMBER] set ISDROPPED = 1 where [MEMBER].ID in 
                (select ID from dbo.UFN_MEMBERSHIP_GETMEMBERS
                (
                    @MEMBERSHIPID
                )
                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 [MEMBER]
                    set [MEMBER].[CONSTITUENTID]=temp.[CONSTITUENTID],
                    [MEMBER].[ID]=temp.[ID],
                    [MEMBER].[ISPRIMARY]=temp.[ISPRIMARY],
                    [MEMBER].CHANGEDBYID = @CHANGEAGENTID,
                    [MEMBER].DATECHANGED = @CHANGEDATE

                from dbo.[MEMBER] inner join @TempTbl as [temp] on [MEMBER].ID = [temp].ID
                where ([MEMBER].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or 
                    ([MEMBER].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or 
                    ([MEMBER].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or 
                    ([MEMBER].[ID]<>temp.[ID]) or 
                    ([MEMBER].[ID] is null and temp.[ID] is not null) or 
                    ([MEMBER].[ID] is not null and temp.[ID] is null) or 
                    ([MEMBER].[ISPRIMARY]<>temp.[ISPRIMARY]) or 
                    ([MEMBER].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or 
                    ([MEMBER].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null)

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [MEMBER] 
                ([MEMBERSHIPID], 
                [CONSTITUENTID],
                [ID],
                [ISPRIMARY],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select @MEMBERSHIPID, 
                [CONSTITUENTID],
                [ID],
                [ISPRIMARY], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[MEMBER] as data where data.ID = [temp].ID)

            if @@Error <> 0
                return 4;

            return 0;