USP_RESERVATION_GETATTENDEES_UPDATEFROMXML

        CREATE PROCEDURE [dbo].[USP_RESERVATION_GETATTENDEES_UPDATEFROMXML] 
        (
            @RESERVATIONID uniqueidentifier,
            @XML xml,
            @CHANGEAGENTID uniqueidentifier = null,
            @CHANGEDATE datetime = null 
        )
        as
            set nocount on;

            if @CHANGEAGENTID is null
                exec dbo.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 (
               [ITINERARYID] uniqueidentifier,
               [ID] uniqueidentifier,
               [QUANTITY] integer,
               [PRICETYPECODEID] uniqueidentifier)

            insert into @TempTbl select 
                [ITINERARYID],
                [ID],
                [QUANTITY],
                [PRICETYPECODEID]
            from dbo.UFN_RESERVATION_GETATTENDEES_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 from dbo.[ITINERARYATTENDEE] 
            where ITINERARYATTENDEE.ID in
                (select T.ID 
                from @TempTbl T
                where T.QUANTITY = 0)

            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 dbo.[ITINERARYATTENDEE]
                    set [ITINERARYATTENDEE].[ID]=temp.[ID],
                    [ITINERARYATTENDEE].[ITINERARYID]=temp.[ITINERARYID],
                    [ITINERARYATTENDEE].[PRICETYPECODEID]=temp.[PRICETYPECODEID],
                    [ITINERARYATTENDEE].[QUANTITY]=temp.[QUANTITY],
                    [ITINERARYATTENDEE].CHANGEDBYID = @CHANGEAGENTID,
                    [ITINERARYATTENDEE].DATECHANGED = @CHANGEDATE
                from dbo.[ITINERARYATTENDEE] inner join @TempTbl as [temp] on [ITINERARYATTENDEE].ID = [temp].ID
                inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.RESERVATION on RESERVATION.ID = ITINERARY.RESERVATIONID
                where ([ITINERARYATTENDEE].[QUANTITY]<>temp.[QUANTITY]) and
                    temp.[QUANTITY] <> 0 and
                    RESERVATION.ID = @RESERVATIONID

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into dbo.[ITINERARYATTENDEE] 
                ([ITINERARYID], 
                [PRICETYPECODEID],
                [ID],
                [QUANTITY],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select [ITINERARYID], 
                [PRICETYPECODEID],
                [ID],
                [QUANTITY], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[ITINERARYATTENDEE] as data where data.ID = [temp].ID)
            and [temp].QUANTITY <> 0

            if @@Error <> 0
                return 4;

            return 0;