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

Issue ,Getting Only One Child Node by sp_xml_preparedocument. Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 2:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
Hi,

I am using Below logic , but getting only one Office ID for Doctor 74962 and its corresponding workdays.


USE DoctorPayroll3
GO



DECLARE @Payrollhandle int
DECLARE @Payrolldoc VARCHAR(MAX)=
('<Payroll StartDate="2013-10-30" EndDate="2013-10-31">
<Doctor ID="74962">
<WorkDays>
<Office ID="60101">23</Office>
<Office ID="60102">23</Office>
</WorkDays>
<AsstDays>
<Office ID="60102">23</Office>
</AsstDays>
<PTODays>
<Office ID="60101">23</Office>
</PTODays>
</Doctor>
<Doctor ID="74961">
<WorkDays>
<Office ID="60101">23</Office>
<Office ID="60102">23</Office>
</WorkDays>
<AsstDays>
<Office ID="60102">23</Office>
</AsstDays>
<PTODays>
<Office ID="60101">23</Office>
</PTODays>
</Doctor>
</Payroll>')

EXEC SP_xml_preparedocument @Payrollhandle OUTPUT,@Payrolldoc
SELECT

A.*

FROM

OPENXML(@Payrollhandle,'/Payroll/Doctor',8)
WITH (DoctorID INT '@ID',
StartDate DATE '../@StartDate',
EndDate DATE '../@EndDate',
WorkDaysOfficeID VARCHAR(10) 'WorkDays/Office/@ID',
WorkDays INT 'WorkDays//Office'--,
--AsstDaysOfficeID VARCHAR(10) 'AsstDays/Office/@ID',
--AsstDays INT 'AsstDays/Office' ,
--PTODaysOfficeID VARCHAR(10) 'PTODays/Office/@ID' ,
--PTODays INT 'PTODays/Office'
)
AS A


EXEC sp_xml_removedocument @Payrollhandle
Post #1507912
Posted Thursday, October 24, 2013 2:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555


DECLARE @PayrollXML XML = CAST(@Payrolldoc AS XML)

SELECT doctor.r1.value('@ID','INT') AS DoctorID,
doctor.r1.value('../@StartDate','DATETIME') AS StartDate,
doctor.r1.value('../@EndDate','DATETIME') AS EndDate,
workdays.r2.value('@ID','INT') AS WorkDaysOfficeID,
workdays.r2.value('.','INT') AS WorkDays
FROM @PayrollXML.nodes('/Payroll/Doctor') AS doctor(r1)
CROSS APPLY doctor.r1.nodes('WorkDays/Office') AS workdays(r2)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1507922
Posted Thursday, October 24, 2013 2:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
Can this be acheived through sp_xml_preparedocument
Post #1507924
Posted Thursday, October 24, 2013 3:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
Like this


SELECT 

A.*

FROM

OPENXML(@Payrollhandle,'/Payroll/Doctor/WorkDays/Office',8)
WITH (DoctorID INT '../../@ID',
StartDate DATE '../../../@StartDate',
EndDate DATE '../../../@EndDate',
WorkDaysOfficeID VARCHAR(10) '@ID',
WorkDays INT '.'--,
--AsstDaysOfficeID VARCHAR(10) 'AsstDays/Office/@ID',
--AsstDays INT 'AsstDays/Office' ,
--PTODaysOfficeID VARCHAR(10) 'PTODays/Office/@ID' ,
--PTODays INT 'PTODays/Office'
)
AS A



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1507930
Posted Thursday, October 24, 2013 3:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
yes, but can you please tell if i mention rowpattern in OPEN XML like '/Payroll/Doctor' Only then will be the corresponding path for below i used "//" but getting only one row.


I am saying to fix rowpattern to that only because i don't know what nodes can be multiple

AS ASSDAYS can have multiple days with respect to office.

FROM

OPENXML(@Payrollhandle,'/Payroll/Doctor/',8)
WITH (DoctorID INT '.@ID',
StartDate DATE '../@StartDate',
EndDate DATE '../@EndDate',
WorkDaysOfficeID VARCHAR(10) 'WorkDays//Office//@ID',
WorkDays INT 'WorkDays//Office',
AsstDaysOfficeID VARCHAR(10) 'AsstDays/Office/@ID',
AsstDays INT 'AsstDays/Office' ,
PTODaysOfficeID VARCHAR(10) 'PTODays/Office/@ID' ,
PTODays INT 'PTODays/Office'
)
AS A
Post #1507943
Posted Thursday, October 24, 2013 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
Not Able to get Assistant days now by open xml



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>
<AsstDays>
<Office ID="60101" Days="23"/>
<Office ID="60102" Days="23"/>
</AsstDays>
</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) '../../AsstDays/Office/@ID'

)
AS A
Post #1508038
Posted Tuesday, October 29, 2013 3:08 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 632, Visits: 2,950
harsimranjeetsinghwasson (10/24/2013)
Not Able to get Assistant days now by open xml



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>
<AsstDays>
<Office ID="60101" Days="23"/>
<Office ID="60102" Days="23"/>
</AsstDays>
</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) '../../AsstDays/Office/@ID'

)
AS A


Using the solution I posted here: Not Able to Get Multiple Rows
you could do this:

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>
<AsstDays>
<Office ID="60101" Days="23"/>
<Office ID="60102" Days="23"/>
</AsstDays>
</Doctor>
</Payroll>')

EXEC SP_xml_preparedocument @Payrollhandle OUTPUT,@Payrolldoc;

SELECT A.DoctorID,
A.WorkDaysOfficeID,
B.AsstDaysOfficeID,
A.OfficeDays,
B.AsstDays,
A.StartDate,
A.EndDate
FROM
OPENXML(@Payrollhandle,'Payroll/Doctor/WorkDays/Office',8)
WITH
( StartDate date '../../../@StartDate',
EndDate date '../../../@EndDate',
DoctorID int '../../@ID',
WorkDaysOfficeID VARCHAR(10) '@ID',
OfficeDays int '@Days'
) AS A
OUTER APPLY
OPENXML(@Payrollhandle,'Payroll/Doctor/AsstDays/Office',8)
WITH
( DoctorID INT '../../@ID',
AsstDaysOfficeID VARCHAR(10) '@ID',
AsstDays int '@Days') AS B
WHERE A.DoctorID=B.DoctorID AND A.WorkDaysOfficeID=B.AsstDaysOfficeID

The above query gives you everything you need to get any attribute in your XML file.

The most important thing to note here is that I am breaking this up into two tables and joining them on //Workdays/Office/@ID=//AsstDatys/Office/@ID.


-- 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 #1509578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse