Sorting XML nodes by attribute

Question

Sorting XML nodes by attribute

KubaB

SSC-Addicted

Points: 443
More actions
November 9, 2010 at 10:16 am

#72948

Hello! I hope somone can help... In brief, we have a number of xml files that are used by an application. The xml file is split into pages, and the application loads the pages in the order they are in in the xml file. Within the application you put rules and all sorts of stuff to control the page flow. So when I load up my application it shows:
Page 1 - (start) goes to page 2
Page 2 - goes to page 3
Page 3 - finish
Unfortunately, when you add pages in the application, you can't reorder them so you end up with stuff like
Page 2 - go somewhere else
Page 1 - (start) go to page 3
Page 8 - go somewhere else
Page 3 - go to page 2
etc.
Which becomes a total nightmare when you are trying to track your way through 30 pages and each page has a number of rules attached to it..
So what I want to do is pull out the xml, sort it by name, then stick it back in, because then at least it would be easier to look at! In the simplist way I want to just stuff in the xml string into sql and have it spit back out sorted xml the same format as the input string.
MY xml is not really up to this, so hopefully someone can give a few pointers? I wan to sort by the <PageTitle>
Here is a starting point..
declare @xml xml
set @xml='<Pages>
<Page>
<PageProperties>
<PageIdentifier>D_Page</PageIdentifier>
<PageTitle>D_Page</PageTitle>
<PageType>End</PageType>
<OpenArticle>False</OpenArticle>
<RuleDefinitions>
<a3:RulesCollection Type="Ascent.Client.Controls.RulesCollection" xmlns:a1="http://www.ascenttechnology.co.uk/schemas/services" xmlns:a2="http://www.ascenttechnology.co.uk/schemas/entities" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" AscentCollection="true" xmlns:a3="http://www.ascenttechnology.co.uk/schemas/AscentSerialzable/Ascent.Client.Controls.SurveyControls">
<__vers>1</__vers>
<IgnoreMissingItem>0</IgnoreMissingItem>
<ReadOnly>0</ReadOnly>
<AllowNulls>0</AllowNulls>
<a3:PageRule Type="Ascent.Client.Controls.PageRule">
<PageIdentifier />
<PageCaption />
<RuleXPath />
<RuleDescription>Default</RuleDescription>
<RuleExpression xsi:nil="true" />
<RuleId>d45fb76c-ae52-4f51-b777-20bafc2c5932</RuleId>
<RuleType xsi:nil="true" />
</a3:PageRule>
</a3:RulesCollection>
</RuleDefinitions>
</PageProperties>
<Questions />
</Page>
<Page>
<PageProperties>
<PageIdentifier>B_Page</PageIdentifier>
<PageTitle>B_Page</PageTitle>
<PageType>Default</PageType>
<SaveOnNext>False</SaveOnNext>
<RuleDefinitions>
<a3:RulesCollection Type="Ascent.Client.Controls.RulesCollection" xmlns:a1="http://www.ascenttechnology.co.uk/schemas/services" xmlns:a2="http://www.ascenttechnology.co.uk/schemas/entities" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" AscentCollection="true" xmlns:a3="http://www.ascenttechnology.co.uk/schemas/AscentSerialzable/Ascent.Client.Controls.SurveyControls">
<__vers>1</__vers>
<IgnoreMissingItem>0</IgnoreMissingItem>
<ReadOnly>0</ReadOnly>
<AllowNulls>0</AllowNulls>
<a3:PageRule Type="Ascent.Client.Controls.PageRule">
<PageIdentifier>C_Page</PageIdentifier>
<PageCaption>C_Page</PageCaption>
<RuleXPath>Survey/Pages/Page[PageProperties/PageIdentifier=''C_Page'']</RuleXPath>
<RuleDescription>Default</RuleDescription>
<RuleExpression xsi:nil="true" />
<RuleId>9c3a470c-e4ab-4cfe-b75b-a14f3891828a</RuleId>
<RuleType xsi:nil="true" />
</a3:PageRule>
</a3:RulesCollection>
</RuleDefinitions>
</PageProperties>
<Questions />
</Page>
<Page>
<PageProperties>
<PageIdentifier>A_Page</PageIdentifier>
<PageTitle>A_Page</PageTitle>
<PageType>Default</PageType>
<SaveOnNext>False</SaveOnNext>
<RuleDefinitions>
<a3:RulesCollection Type="Ascent.Client.Controls.RulesCollection" xmlns:a1="http://www.ascenttechnology.co.uk/schemas/services" xmlns:a2="http://www.ascenttechnology.co.uk/schemas/entities" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" AscentCollection="true" xmlns:a3="http://www.ascenttechnology.co.uk/schemas/AscentSerialzable/Ascent.Client.Controls.SurveyControls">
<__vers>1</__vers>
<IgnoreMissingItem>0</IgnoreMissingItem>
<ReadOnly>0</ReadOnly>
<AllowNulls>0</AllowNulls>
<a3:PageRule Type="Ascent.Client.Controls.PageRule">
<PageIdentifier>B_Page</PageIdentifier>
<PageCaption>B_Page</PageCaption>
<RuleXPath>Survey/Pages/Page[PageProperties/PageIdentifier=''B_Page'']</RuleXPath>
<RuleDescription>Default</RuleDescription>
<RuleExpression xsi:nil="true" />
<RuleId>5f43c6d4-b9df-49b5-87dd-e66703f6e24c</RuleId>
<RuleType xsi:nil="true" />
</a3:PageRule>
</a3:RulesCollection>
</RuleDefinitions>
</PageProperties>
<Questions />
</Page>
<Page>
<PageProperties>
<PageIdentifier>C_Page</PageIdentifier>
<PageTitle>C_Page</PageTitle>
<PageType>Default</PageType>
<SaveOnNext>False</SaveOnNext>
<RuleDefinitions>
<a3:RulesCollection Type="Ascent.Client.Controls.RulesCollection" xmlns:a1="http://www.ascenttechnology.co.uk/schemas/services" xmlns:a2="http://www.ascenttechnology.co.uk/schemas/entities" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" AscentCollection="true" xmlns:a3="http://www.ascenttechnology.co.uk/schemas/AscentSerialzable/Ascent.Client.Controls.SurveyControls">
<__vers>1</__vers>
<IgnoreMissingItem>0</IgnoreMissingItem>
<ReadOnly>0</ReadOnly>
<AllowNulls>0</AllowNulls>
<a3:PageRule Type="Ascent.Client.Controls.PageRule">
<PageIdentifier>D_Page</PageIdentifier>
<PageCaption>D_Page</PageCaption>
<RuleXPath>Survey/Pages/Page[PageProperties/PageIdentifier=''D_Page'']</RuleXPath>
<RuleDescription>Default</RuleDescription>
<RuleExpression xsi:nil="true" />
<RuleId>ef234637-c13d-4c0a-91d0-4b980f14e290</RuleId>
<RuleType xsi:nil="true" />
</a3:PageRule>
</a3:RulesCollection>
</RuleDefinitions>
</PageProperties>
<Questions />
</Page>
<StartPage>A_Page</StartPage>
</Pages>'
select @xml
So this lot are ordered D_Page, B_Page, A_Page, C_Page and obviously I want them A, B, C, D..
Really appreciate any help! 🙂

Viewing 5 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: 26983 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!