yes, but can you please tell if i mention rowpattern in OPEN XML like '/Payroll/Doctor' Only then will be the corresponding path for below i used "//" but getting only one row.

I am saying to fix rowpattern to that only because i don't know what nodes can be multiple

AS ASSDAYS can have multiple days with respect to office.

FROM

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

WITH (DoctorID INT '.@ID',

StartDate DATE '../@StartDate',

EndDate DATE '../@EndDate',

WorkDaysOfficeID VARCHAR(10) 'WorkDays//Office//@ID',

WorkDays INT 'WorkDays//Office',

AsstDaysOfficeID VARCHAR(10) 'AsstDays/Office/@ID',

AsstDays INT 'AsstDays/Office' ,

PTODaysOfficeID VARCHAR(10) 'PTODays/Office/@ID',

PTODays INT 'PTODays/Office'

)

AS A