XML Stored Proc Help

  • MY parameters:

    @emp_id

    @work_date

    Table: emp_schedule

    How would I write a Stored Procedure where the output will be in XML format?

    My assignment: take the work_date and for the next 4 weeks release his days in each week and withing each day his/her shift.

    The result has to be in XML format.

    like

    <Week ="1">

    <day = "0">

    <shift = "***">

    </shift>

    </day>

    </week>

  • please provide the table structure and some sample data.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • emp_id = 1

    work_date = 2011-04-01

    shift_start_time = "2011-04-01 07:00:00"

    shift_end_time = "2011-04-01 15:00:00"

    The table includes the dates he is scheduled to work on for the next 4 weeks from 2011-04-01

    So all data between "2011-04-01" and "2011-04-28"

    <Week = "1">

    <day ="0">

    <shift start =>

    <shift finish =>

  • Here's a couple of options/samples for you:

    with emp_schedule as (

    Select shift_start_time = '2011-04-01 07:00:00'

    ,shift_end_time = '2011-04-01 15:00:00'

    ,work_date = '2011-04-01'

    ,emp_id = 1

    )

    Select emp_id as "@Employee",DATEPART(wk,work_date) as "@WorkWeek"

    ,(Select DATEPART(dd,work_date) as "@Day"

    ,(Select shift_end_time as "@EndShift"

    ,shift_start_time as "@StartShift"

    From emp_schedule

    Group by emp_id,shift_start_time,shift_end_time

    FOR XML PATH ('Shift'), type)

    From emp_schedule

    Group by emp_id,work_date

    FOR XML PATH ('WorkDay'), type)

    From emp_schedule

    Group By emp_id,DATEPART(wk,work_date)

    FOR XML PATH ('WorkWeek'), type

    ;

    with emp_schedule as (

    Select shift_start_time = '2011-04-01 07:00:00'

    ,shift_end_time = '2011-04-01 15:00:00'

    ,work_date = '2011-04-01'

    ,emp_id = 1

    )

    Select emp_id as "Employee",DATEPART(wk,work_date) as "WorkWeek"

    ,(Select DATEPART(dd,work_date) as "Day"

    ,(Select shift_end_time as "EndShift"

    ,shift_start_time as "StartShift"

    From emp_schedule

    Group by emp_id,shift_start_time,shift_end_time

    FOR XML PATH ('Shift'), type)

    From emp_schedule

    Group by emp_id,work_date

    FOR XML PATH ('WorkDay'), type)

    From emp_schedule

    Group By emp_id,DATEPART(wk,work_date)

    FOR XML PATH ('WorkWeek'), type

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This is how the result should be

    <e emp_id="518" emp_name="Avila, Bobbie" start_sched="2011-04-17T00:00:00" end_sched="2011-05-14T00:00:00">

    <w wk="1">

    <s work_date="04/17" date="2011-04-17T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/18" date="2011-04-18T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/19" date="2011-04-19T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/20" date="2011-04-20T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/21" date="2011-04-21T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/22" date="2011-04-22T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/23" date="2011-04-23T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    </w>

    <w wk="2">

    <s work_date="04/24" date="2011-04-24T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/25" date="2011-04-25T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/26" date="2011-04-26T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/27" date="2011-04-27T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/28" date="2011-04-28T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/29" date="2011-04-29T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/30" date="2011-04-30T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    </w>

    <w wk="3">

    <s work_date="05/01" date="2011-05-01T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/02" date="2011-05-02T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/03" date="2011-05-03T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/04" date="2011-05-04T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/05" date="2011-05-05T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/06" date="2011-05-06T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/07" date="2011-05-07T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    </w>

    <w wk="4">

    <s work_date="05/08" date="2011-05-08T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/09" date="2011-05-09T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/10" date="2011-05-10T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/11" date="2011-05-11T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/12" date="2011-05-12T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/13" date="2011-05-13T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/14" date="2011-05-14T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    </w>

    </e>

  • You didn't have all of that info included in your first posts.

    I provided two sample queries to help you figure out what needs to be done. Add in the additional fields and query from the table as needed to get the data you need.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • One of the guys told me I need to create a table with

    emp_id,

    emp_name,

    job_description,

    shift_id,

    shift_start_time,

    shift_end_time

    but I have to parse the data into this format with w wk and day, can you please point me in the right direction so I can write the code, because it is somewhat confusing to me.

    <e emp_id="518" emp_name="Avila, Bobbie" start_sched="2011-04-17T00:00:00" end_sched="2011-05-14T00:00:00">

    <w wk="1">

    <s work_date="04/17" date="2011-04-17T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/18" date="2011-04-18T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/19" date="2011-04-19T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/20" date="2011-04-20T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/21" date="2011-04-21T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/22" date="2011-04-22T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    <s work_date="04/23" date="2011-04-23T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="1" />

    </s>

    </w>

    <w wk="2">

    <s work_date="04/24" date="2011-04-24T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/25" date="2011-04-25T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/26" date="2011-04-26T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/27" date="2011-04-27T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/28" date="2011-04-28T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/29" date="2011-04-29T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="2" />

    </s>

    <s work_date="04/30" date="2011-04-30T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />

    </s>

    </w>

    <w wk="3">

    <s work_date="05/01" date="2011-05-01T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/02" date="2011-05-02T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/03" date="2011-05-03T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/04" date="2011-05-04T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/05" date="2011-05-05T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/06" date="2011-05-06T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    <s work_date="05/07" date="2011-05-07T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />

    </s>

    </w>

    <w wk="4">

    <s work_date="05/08" date="2011-05-08T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/09" date="2011-05-09T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/10" date="2011-05-10T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/11" date="2011-05-11T00:00:00">

    <shift job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/12" date="2011-05-12T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/13" date="2011-05-13T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    <s work_date="05/14" date="2011-05-14T00:00:00">

    <shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />

    </s>

    </w>

    </e>

    Previous ItemNext Item

  • you should be able to follow this and add in the pieces that you need.

    with emp_schedule as (

    Select shift_start_time = '2011-04-01 07:00:00'

    ,shift_end_time = '2011-04-01 15:00:00'

    ,work_date = '2011-04-01'

    ,emp_id = 1

    )

    Select emp_id as "@emp_id",DATEPART(wk,work_date) as "@WorkWeek"

    ,(Select DATEPART(dd,work_date) as "@Day"

    ,(Select work_date as "@work_date"

    ,(Select shift_end_time as "@shift_end_time"

    ,shift_start_time as "@shift_start_time"

    From emp_schedule

    Group by emp_id,shift_start_time,shift_end_time

    FOR XML PATH ('shift'), type)

    From emp_schedule

    Group by emp_id,work_date

    FOR XML PATH ('s'), type)

    From emp_schedule

    Group by emp_id,work_date

    FOR XML PATH ('w'), type)

    From emp_schedule

    Group By emp_id,DATEPART(wk,work_date)

    FOR XML PATH ('e'), type

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • In the first select statement, I need to split calender into 28 days. with wk 1,2,3,4 and days from 1-7 or 0-6.

    How do I make sure that the work_date is between April 1 and 28. And how do I split it into weeks and days.

  • with emp_schedule as (

    Select shift_start_time = '2011-04-01 07:00:00'

    ,shift_end_time = '2011-04-01 15:00:00'

    ,work_date = '2011-04-01'

    ,emp_id = 1

    )

    -How would I make the start time and end time be on work date between the 1st and the 28th

    -I also want week 1 to be the 1st through the 7th, week 2 8-14, 3 --> 15-21, 4 --> 22-28

  • Also How would I create a time table with all the values you have given me in your query.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply