Sorting XML nodes by attribute

Question

Sorting XML nodes by attribute

Viewing 4 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is the XML split across files, or just pages within the files?

If it's just pages within the files, take a look at OpenRowset Bulk Single_CLob in Books Online or MSDN. You can pull an XML file into an XML field in a table (so long as it's not more than 2 GB of XML per file). Then you can use T-SQL XQuery to shred the XML into a relational format, and use the usual Order By clauses and all that.

But the first thing is, can you use OpenRowset to pull it into a staging table from the files?

- 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

KubaB SSC-Addicted Points: 443 More actions · Answer 2

Hi, the pages are all in one file for each set so I don't need to worry about multiple files. It's the shredding where I'm falling over slightly..

I can pull out the page titles like so:

SELECT x.page.value('PageTitle[1]', 'varchar(100)') AS PageTitle

from @xml.nodes('//Pages/Page/PageProperties') AS x (page)

But I can't work out how to get the block of xml for each page alongside it so I can sort and redump it all

If I do this:

SELECT x.pages.value('Page[1]', 'varchar(max)') AS Page

from @xml.nodes('//Pages') AS x (pages)

I'd expect it to give me a row for each page, but it just gives me one?

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

SELECT x.page.value('(PageProperties/PageTitle)[1]', 'varchar(100)') AS PageTitle,

x.page.query('.') AS Page

from @xml.nodes('//Pages/Page') AS x (page)

____________________________________________________

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

KubaB SSC-Addicted Points: 443 More actions · Answer 4

Ah! Excellent! Thanks! That should get me on the right tracks!