Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not able to get multiple rows Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 7:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:07 AM
Points: 49, Visits: 196
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
Post #1508039
Posted Tuesday, October 29, 2013 2:39 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1509568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse