Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steven.liberman1
Starting Member
15 Posts |
 Posted - 2011-04-20 : 12:26:03
|
| [code]<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 Item Next Item [/code]I need a stored proc which would order the data above into this XML format.It needsw wkdayemp_idemp_nameshift_start_timeshift_end_time |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-20 : 12:52:27
|
| I assume you are trying to reshape the XML. From your description, not quite clear what the new shape should be. Is it one element for each employee? Also, in the XML you provided, I don't see any node that shows shift_start_time and shift_end_time.For the sample input you posted, can you also post what the XML output you expect to get is (with XML element tags, attributes etc.)? |
 |
|
|
steven.liberman1
Starting Member
15 Posts |
Posted - 2011-04-20 : 13:01:06
|
| Instead of shift_description I need to show the shift_start_time and shift_end_time which are columns in the (emp-schedule) table. |
|
|
|
|
|
|
XML Stored Proc Help