Not able to get multiple rows

• harsimranjeetsinghwasson

  Ten Centuries

  Points: 1231

  More actions

  October 24, 2013 at 7:22 am

  #98945

  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

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  October 29, 2013 at 2:39 pm

  #1662539

  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