T-SQL/XQuery to Sort Nodes by Name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL/XQuery to Sort Nodes by Name

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-10-09 : 11:47:02

I have a table with an XML typed column and I need a script to go through and touch each XML and re-write it ordering some nodes by some attribute. Any ideas?


declare @xml xml
select @xml = '
<SomeList>
    <SomeNode name="Page47"/>
    <SomeNode name="AAA"/>
    <SomeNode name="ZZZ"/>
</SomeList>'

Expected Result:


<SomeList>
    <SomeNode name="AAA"/>
    <SomeNode name="Page47"/>
    <SomeNode name="ZZZ"/>
</SomeList>

Jay
to here knows when

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 11:51:56

Why are you bothered for which ORDER the tags are?
What you are trying to is much like using a TEXT-file.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-09 : 12:20:00

agree with Peter on this one.
in xml the order on the same hierarchy level is irrelevant.
much like in a DB

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-10-09 : 12:35:52

This XML sits in a database that is delivered by Microsoft as the back end for ProClarity and the specific block I am worried about contains the Background Dimensions used by the report. In ProClarity Web Professional, the list is ordered alpha, but in Web Standard, the stupid thing isn't smart enough to do that. It is alphabetical when the Cube is first created, but after you go through and rename some Dimensions and other stuff ... and then in Stupid ProClarity go through the Stupid "Missing Data Wizard" ... the Stupid Web Standard version displays the Dims in their original order, rather than ordered by their new names. Of course there is no way in Stupid Web Standard to set the order through the Stupid GUI, so I'm trying to figure out, as best I can, how to delight my client without manually recreating hundreds of Stupid Reports. I've looked at the Stupid XMLData column in the Stupid BookElements table (which of course is varchar, rather than XML ... even though it contains Stupid XML) and found that the Stupid Web Standard is displaying the Dims in the same order as they are in the XML.

I hope M$ makes ProClarity better in Performance Point, rather than worse ... I can't take wasting my valuable time on this type of stupid crap.

Yeah, you guys are correct ... tag ORDER is irrelevant ... unless the consumers of your XML data are STUPID!!

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-09 : 16:26:12

well... this is one way:


declare @xml xml
select @xml = '
<SomeList>
    <SomeNode name="Page47"/>
    <SomeNode name="AAA"/>
    <SomeNode name="ZZZ"/>
</SomeList>'


SELECT	'<SomeList>' + 
			'<SomeNode name="' + T.c.value('@name', 'nvarchar(max)') + '"/>' +
		'</SomeList>' AS res
FROM	@xml.nodes('/SomeList/SomeNode') T(c)
ORDER BY res

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources