Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 15, 2015 3:22 AM
Points: 51, Visits: 233
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,153, Visits: 7,254
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
Post #1509568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse