Not able to get multiple rows

  • Hi,

    I am using below code to Get WorkDaysOfficeID AND AsstDaysOfficeID but Getting only one data for

    AsstDaysOfficeID.

    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>

    <AssDays>

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

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

    </AssDays>

    </Doctor>

    </Payroll>')

    EXEC SP_xml_preparedocument @Payrollhandle OUTPUT,@Payrolldoc

    SELECT

    * FROM

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

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

    WorkDaysOfficeID VARCHAR(10) './@ID',

    AsstDaysOfficeID VARCHAR(10) '../../AssDays/Office/@ID'

    )

    AS A

    Please let me know how we can achieve this by sp_xml_preparedocument

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply