XML Output from stored procedure
I am trying to do the difficult task of writing the output from the SP below to an xml file. I can seem to get it to a well formed state(if i manually strip the autogenerated id), but now i need to actually write the output to an xml file. If someone knows how to get rid of the autogenerated id it would help, but just writing the xml file at the moment is urgent.
ALTER PROCEDURE SalaryBetweenDates
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, sh.WeekStart, sh.WeekEnd,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@WeekStart)
AND WeekEnd <=(@WeekEnd)
FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL;