DECLARE @PayrollXML XML = CAST(@Payrolldoc AS XML)

SELECT doctor.r1.value('@ID','INT') AS DoctorID,

doctor.r1.value('../@StartDate','DATETIME') AS StartDate,

doctor.r1.value('../@EndDate','DATETIME') AS EndDate,

workdays.r2.value('@ID','INT') AS WorkDaysOfficeID,

workdays.r2.value('.','INT') AS WorkDays

FROM @PayrollXML.nodes('/Payroll/Doctor') AS doctor(r1)

CROSS APPLY doctor.r1.nodes('WorkDays/Office') AS workdays(r2)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537