xquery - order by problem

SQL Server XML

Bjorn D. Jensen


Hi!

In MSS Management Studio it is very irritating that queries not can be sorted.

So I looked at the ssmssqlproj-file.

I want to order by filename, but:

declare @x xml;

select @x = '<LogicalFolder Name="Queries" Type="0">

<Items>

<FileNode Name="updateTbl.sql">

<AssociatedConnectionMoniker />

<AssociatedConnSrvName />

<AssociatedConnUserName />

<FullPath>updateTbl.sql</FullPath>

</FileNode>

<FileNode Name="insertTbl.sql">

<AssociatedConnUserName />

<FullPath>insertTbl.sql</FullPath>

</FileNode>

<FileNode Name="selectTbl.sql">

<AssociatedConnUserName />

<FullPath>selectTbl.sql</FullPath>

</FileNode>

<FileNode Name="deleteTbl.sql">

<AssociatedConnUserName />

<FullPath>deleteTbl.sql</FullPath>

</FileNode>

</Items>

</LogicalFolder>';

--select @x.query('for $item in /LogicalFolder/Items

-- return count($item/FileNode) ')

select @x.query('for $x in /LogicalFolder/Items/FileNode

order by $x/FullPath

return $x ')

--for xml path(''), root('LogicalFolder');

--Msg 2389, Level 16, State 1, Line 29

--XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic

Can anyone help

Thanks

Bjorn




Top

Re: xquery - order by problem

Martin Honnen


Try

Code Snippet

select @x.query('for $x in /LogicalFolder/Items/FileNode

order by $x/FullPath[1]

return $x ')






Top

Re: xquery - order by problem

Bjorn D. Jensen

Hi!

Great, it does what I look for!

Now I hope it solves my problem with sorting in Management Studio.

If you now an good link about xml/xquery/xpath in Sql Server, pleas let me now (-:

I read other examples on internet, and don't understand why my 1. try didn't work;

Is it wrong, or has it something to do with MS implementation

/Bjorn






Top

Re: xquery - order by problem

rajesh_dadi_d847c5

tried a lot unable to sort but interesting




Top

Re: xquery - order by problem

Martin Honnen

Books online in http://msdn2.microsoft.com/en-us/library/ms190945.aspx says "Each sorting expression must result in a singleton a sequence with one item" and the example there also has the [1] to ensure that.



Top