Home Forums Programming XML Not able to get multiple rows RE: Not able to get multiple rows

  • XML questions don't always get much love around here. Here's what you are looking for:

    EXEC SP_xml_preparedocument @Payrollhandle OUTPUT,@Payrolldoc;

    SELECT A.*, B.AsstDaysOfficeID

    FROM

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

    WITH

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

    WorkDaysOfficeID VARCHAR(10) './@ID') AS A

    OUTER APPLY

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

    WITH

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

    AsstDaysOfficeID VARCHAR(10) '@ID') AS B

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

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001