/*<EventDesc>]__calschedid__[
 - CHAPMAN
People:4</EventDesc><MonthDesc>]__arrive_time__[ - ]__depart_time__[: CHAPMAN</MonthDesc><MonthDescFull>]__arrive_time__[ - ]__depart_time__[:  - ]__calschedid__[ - CODY CHAPMAN</MonthDescFull><ReservationEvents><Event_0><callocatid>1</callocatid><calschedid>2000001</calschedid><end_date>03/15/2011 09:35:00</end_date><res_index>0</res_index><reserv_no>5004001</reserv_no><save_info><EventDesc>]__calschedid__[
 - CHAPMAN
People:4</EventDesc><MonthDesc>]__arrive_time__[ - ]__depart_time__[: CHAPMAN</MonthDesc><MonthDescFull>]__arrive_time__[ - ]__depart_time__[:  - ]__calschedid__[ - CODY CHAPMAN</MonthDescFull></save_info><sortid>40617</sortid><start_date>03/15/2011 09:15:00</start_date><updated_save_info><EventDesc>CATERING
 - CHAPMAN
People:4</EventDesc><MonthDesc>09:15 AM - 10:25 AM: CHAPMAN</MonthDesc><MonthDescFull>09:15 AM - 10:25 AM:  - CATERING - CODY CHAPMAN</MonthDescFull></updated_save_info></Event_0><Event_1><callocatid>2</callocatid><calschedid>3000001</calschedid><end_date>03/15/2011 10:25:00</end_date><res_index>0</res_index><reserv_no>5004001</reserv_no><save_info><EventDesc>]__calschedid__[
 - CHAPMAN
People:4</EventDesc><MonthDesc>]__arrive_time__[ - ]__depart_time__[: CHAPMAN</MonthDesc><MonthDescFull>]__arrive_time__[ - ]__depart_time__[:  - ]__calschedid__[ - CODY CHAPMAN</MonthDescFull></save_info><sortid>40617</sortid><start_date>03/15/2011 09:35:00</start_date><updated_save_info><EventDesc>BIRTHDAY PARTY
 - CHAPMAN
People:4</EventDesc><MonthDesc>09:15 AM - 10:25 AM: CHAPMAN</MonthDesc><MonthDescFull>09:15 AM - 10:25 AM:  - BIRTHDAY PARTY - CODY CHAPMAN</MonthDescFull></updated_save_info></Event_1><Event_2><callocatid>2</callocatid><calschedid>3000001</calschedid><end_date>03/16/2011 11:10:00</end_date><res_index>0</res_index><reserv_no>5004001</reserv_no><save_info><EventDesc>]__calschedid__[
 - CHAPMAN
People:4</EventDesc><MonthDesc>]__arrive_time__[ - ]__depart_time__[: CHAPMAN</MonthDesc><MonthDescFull>]__arrive_time__[ - ]__depart_time__[:  - ]__calschedid__[ - CODY CHAPMAN</MonthDescFull></save_info><sortid>40618</sortid><start_date>03/16/2011 10:35:00</start_date><updated_save_info><EventDesc>BIRTHDAY PARTY
 - CHAPMAN
People:4</EventDesc><MonthDesc>10:00 AM - 11:10 AM: CHAPMAN</MonthDesc><MonthDescFull>10:00 AM - 11:10 AM:  - BIRTHDAY PARTY - CODY CHAPMAN</MonthDescFull></updated_save_info></Event_2><Event_3><callocatid>1</callocatid><calschedid>2000001</calschedid><end_date>03/16/2011 10:35:00</end_date><res_index>0</res_index><reserv_no>5004001</reserv_no><save_info><EventDesc>]__calschedid__[
 - CHAPMAN
People:4</EventDesc><MonthDesc>]__arrive_time__[ - ]__depart_time__[: CHAPMAN</MonthDesc><MonthDescFull>]__arrive_time__[ - ]__depart_time__[:  - ]__calschedid__[ - CODY CHAPMAN</MonthDescFull></save_info><sortid>40618</sortid><start_date>03/16/2011 10:00:00</start_date><updated_save_info><EventDesc>CATERING
 - CHAPMAN
People:4</EventDesc><MonthDesc>10:00 AM - 11:10 AM: CHAPMAN</MonthDesc><MonthDescFull>10:00 AM - 11:10 AM:  - CATERING - CODY CHAPMAN</MonthDescFull></updated_save_info></Event_3></ReservationEvents>*/

DECLARE @reservNo   INT
SET     @reservNo = 5004001

IF OBJECT_ID('tempdb..#eventStartTimes') IS NOT NULL
   DROP TABLE #eventStartTimes

CREATE TABLE #eventStartTimes
(
    eventNo     INT,
    location    INT,
    [event]     INT,
    startDate   DATETIME
)

DECLARE @eventIndex INT
SET     @eventIndex = 0

DECLARE @tempString VARCHAR(MAX)
DECLARE @locatID    INT
DECLARE @schedID    INT
DECLARE @startDate  DATETIME

SET     @tempString = (SELECT r.save_info FROM resrvatn r WHERE reserv_no = @reservNo)

WHILE (PATINDEX('%<Event[_]%>%', @tempString) != '')
BEGIN
    SET     @tempString = SUBSTRING(@tempString, PATINDEX('%<Event[_]%>%', @tempString),
            LEN(@tempString)) -- Trim until next event
    SET     @tempString = SUBSTRING(@tempString, PATINDEX('%<callocatid>%', @tempString),
            LEN(@tempString)) -- Trim until location ID

    SET     @locatID = CAST(SUBSTRING(@tempString, LEN('<callocatid>') + 1,
                       PATINDEX('%</callocatid>%', @tempString) - LEN('</callocatid>'))
                       AS INT)

    SET     @tempString = SUBSTRING(@tempString, PATINDEX('%<calschedid>%', @tempString),
                          LEN(@tempString)) -- Trim until event ID

    SET     @schedID = CAST(SUBSTRING(@tempString, LEN('<calschedid>') + 1,
                       PATINDEX('%</calschedid>%', @tempString) - LEN('</calschedid>'))
                       AS INT)

    SET     @tempString = SUBSTRING(@tempString, PATINDEX('%<start[_]date>%', @tempString),
                          LEN(@tempString)) -- Trim until start_date

    SET     @startDate = CAST(SUBSTRING(@tempString, LEN('<start_date>') + 1,
                         PATINDEX('%</start[_]date>%', @tempString) - LEN('</start_date>'))
                         AS DATETIME)

    INSERT INTO #eventStartTimes
        SELECT  @eventIndex,
                @locatID,
                @schedID,
                @startDate

    SET     @eventIndex = @eventIndex + 1

END

SELECT * FROM #eventStartTimes