Using the solution I posted here: Not Able to Get Multiple Rows

you could do this:

DECLARE @Payrollhandle int

DECLARE @Payrolldoc VARCHAR(MAX)=

('<Payroll StartDate="2013-10-30" EndDate="2013-10-31">

<Doctor ID="74962">

<WorkDays>

<Office ID="60101" Days="23"/>

<Office ID="60102" Days="23"/>

</WorkDays>

<AsstDays>

<Office ID="60101" Days="23"/>

<Office ID="60102" Days="23"/>

</AsstDays>

</Doctor>

</Payroll>')

EXEC SP_xml_preparedocument @Payrollhandle OUTPUT,@Payrolldoc;

SELECTA.DoctorID,

A.WorkDaysOfficeID,

B.AsstDaysOfficeID,

A.OfficeDays,

B.AsstDays,

A.StartDate,

A.EndDate

FROM

OPENXML(@Payrollhandle,'Payroll/Doctor/WorkDays/Office',8)

WITH

(StartDate date '../../../@StartDate',

EndDate date '../../../@EndDate',

DoctorID int '../../@ID',

WorkDaysOfficeID VARCHAR(10) '@ID',

OfficeDays int '@Days'

) AS A

OUTER APPLY

OPENXML(@Payrollhandle,'Payroll/Doctor/AsstDays/Office',8)

WITH

(DoctorID INT '../../@ID',

AsstDaysOfficeID VARCHAR(10) '@ID',

AsstDays int '@Days') AS B

WHERE A.DoctorID=B.DoctorID AND A.WorkDaysOfficeID=B.AsstDaysOfficeID

The above query gives you everything you need to get any attribute in your XML file.

The most important thing to note here is that I am breaking this up into two tables and joining them on //Workdays/Office/@ID=//AsstDatys/Office/@ID.