Issue ,Getting Only One Child Node by sp_xml_preparedocument.

Question

Post reply

Issue ,Getting Only One Child Node by sp_xml_preparedocument.

harsimranjeetsinghwasson

Ten Centuries

Points: 1231
More actions
October 24, 2013 at 2:07 am

#100164

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 2

harsimranjeetsinghwasson

Ten Centuries

Points: 1231

October 24, 2013 at 2:57 am

#1661173

Can this be acheived through sp_xml_preparedocument

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

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

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 4

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

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 5

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">

</WorkDays>

</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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

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;

SELECTA.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.

"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