USP_PROGRAMEVENTLOCATION_INSERTLOCATIONS

            CREATE procedure dbo.USP_PROGRAMEVENTLOCATION_INSERTLOCATIONS
            (
                @EVENTS xml,
                @LOCATIONS xml,
                @PROGRAMID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @CONFLICTSEXIST bit = null
            )
            as 
                set nocount on;

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                declare @idoc int;
                exec sp_xml_preparedocument @idoc OUTPUT, @EVENTS;

                -- PUT LOCATIONS HERE

                with LOCATIONS_CTE as 
                (
                        select 
                            T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') as EVENTLOCATIONID,
                            T.locations.value('(CAPACITY)[1]', 'integer') as CAPACITY,
                            T.locations.value('(SEQUENCE)[1]', 'integer') as SEQUENCE,
                            @PROGRAMID as PROGRAMID
                        from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(locations)
                )


                insert into dbo.PROGRAMEVENTLOCATION
                (
                    ID,
                    EVENTID,
                    EVENTCONFLICTID,
                    EVENTLOCATIONID,
                    SEQUENCE,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    newid(),
                    eventid,
                    null,
                    LCTE.EVENTLOCATIONID,
                    LCTE.SEQUENCE,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from LOCATIONS_CTE LCTE
                INNER JOIN openxml (@idoc,'/EVENTS/ITEM', 2)
                with
                    (EVENTID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    ISCONFLICT bit) P
                 on LCTE.PROGRAMID = P.PROGRAMID
                where ISCONFLICT = 0

                if @CONFLICTSEXIST = 1 or @CONFLICTSEXIST is null
                begin
                    declare @CON uniqueidentifier;
                    with LOCATIONS2_CTE as 
                    (
                        select 
                            T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') as EVENTLOCATIONID,
                            T.locations.value('(CAPACITY)[1]', 'integer') as CAPACITY,
                            T.locations.value('(SEQUENCE)[1]', 'integer') as SEQUENCE,
                            @PROGRAMID as PROGRAMID
                        from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(locations)
                    )
                    insert into dbo.PROGRAMEVENTLOCATION
                    (
                        ID,
                        EVENTID,
                        EVENTCONFLICTID,
                        EVENTLOCATIONID,
                        SEQUENCE,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )                    
                    select
                        newid(),
                        null,
                        CONFLICTID,
                        LCTE.EVENTLOCATIONID,
                        LCTE.SEQUENCE,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from LOCATIONS2_CTE LCTE
                    INNER JOIN openxml (@idoc,'/EVENTS/ITEM', 2)
                    with
                        (CONFLICTID uniqueidentifier,
                        PROGRAMID uniqueidentifier,
                        ISCONFLICT bit) P
                    on LCTE.PROGRAMID = P.PROGRAMID
                    where ISCONFLICT = 1

                end

                exec sp_xml_removedocument @idoc