Working with the Cross Apply with XML

Question

Post reply

Working with the Cross Apply with XML

Jacobus-785651

Ten Centuries

Points: 1106
More actions
February 19, 2009 at 1:24 am

#131132

DECLARE @XMLString table
(xmldata xml)
insert into @XMLString(xmldata)(select xmldata from dbo.Stg_XmlFile where id =51)
--insert into stg_RelationalData
SELECT
----level
CAST(x.y.query('//level/title[1]/text()') as varchar(255)) AS title
, cast(x.y.query('//level/description[1]/text()') as varchar(255)) AS [description]
----level_year
, cast(x.y.query('//level_year/year[1]/text()') as varchar(255)) AS [year]
, cast(x.y.query('//level_year/code[1]/text()') as varchar(255)) AS code
, cast(x.y.query('//level_year/default[1]/text()') as varchar(255)) AS [default]
FROM @XMLString
CROSS APPLY xmldata.nodes('//level_year') as x(y)
my result set gives doubles:
title description year code default
havo havo 12 havo1havo2 truetrue
havo havo 12 havo1havo2 truetrue
what i really would like is:
titel description year code default
havo havo 1 havo1 true
havo havo 2 havo2 true
:hehe:

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: 26952 More actions · Answer 1

Can you post your XML (as an attachment if possible)

____________________________________________________

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

Jacobus-785651 Ten Centuries Points: 1106 More actions · Answer 2

Jacobus-785651

Ten Centuries

Points: 1106

February 19, 2009 at 3:21 pm

#947200

:hehe:

GSquared SSC Guru Points: 260824 More actions · Answer 3

Posting XML in the forum directly doesn't work.

Attach it as a text file.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

I think the problem is in your XPath expressions, not with the CROSS APPLY clause:

Does this give you what you are expecting?

SELECT

----level

CAST(x.y.query('../../title[1]/text()') as varchar(255)) AS title

, cast(x.y.query('../../description[1]/text()') as varchar(255)) AS [description]

----level_year

, cast(x.y.query('./year[1]/text()') as varchar(255)) AS [year]

, cast(x.y.query('./code[1]/text()') as varchar(255)) AS code

, cast(x.y.query('./default[1]/text()') as varchar(255)) AS [default]

FROM @XMLString

CROSS APPLY xmldata.nodes('//level_year') as x(y)

Running this against the Finally.xml file gives this result set.

[font="Courier New"]title description year code default

HavoHoger Administratief voortgezet onderwijs1Havo11

HavoHoger Administratief voortgezet onderwijs2Havo21[/font]

Jacobus-785651 Ten Centuries Points: 1106 More actions · Answer 5

hi andrewd.smith well finally it is working yes. I understand that the nodes are not neaded. Only the element names in the nodes and that the path to the element names is like ../../

i am know going to put the results in SSIS and use them in a table difference.

Thanx very much!!

Regards,

Sjaak

:hehe:

bstauffer-926504 SSC Enthusiast Points: 129 More actions · Answer 6

Where is this sytax documented at? Specifically the last bit after the cross apply, i.e. 'as x(y)'. And the references in the query to it, i.e. 'x.y.query('...'

What is that doing/how does it work/where can I find it in the BOL?

Thanks!