SQL Server: to control XML sorting by attribute

Question

I am generating an XML file from a SQL Server query.

I have a problem with sorting element issues.

For example, there is simple code as shown below.

<test> <tree abc="123"/> <tree abc="789"/> <tree-order abc="456"/> </test> 

As you can see the tree and the tree order are different elements, and I want to sort by attributes as

 <test> <tree abc="123"/> <tree-order abc="456"/> <tree abc="789"/> </test> 

like this.

Anyone have a good idea to solve this problem?

Thanks.

Sorry, I have a question about the details below.

AS is

  <Main> <test> <tree abc="123"/> <tree abc="789"/> <tree-order abc="456"/> </test> <test> <tree abc="123"/> <tree abc="789"/> <tree-order abc="456"/> </test> </Main> 

Be:

 <Main> <test> <tree abc="123"/> <tree abc="456"/> <tree-order abc="789/> </test> <test> <tree abc="123"/> <tree abc="456"/> <tree-order abc="789/> </test> </Main> 

Thanks.

Answer 1

If you want to reorder existing xml, you can use XQuery :

 declare @data xml = ' <test> <tree abc="123"/> <tree abc="789"/> <tree-order abc="456"/> </test> ' select @data.query('<test>{for $i in test/* order by $i/@abc return $i}</test>') 

Result:

 <test> <tree abc="123" /> <tree-order abc="456" /> <tree abc="789" /> </test> 

demo sql

update:

To reorder multiple nodes, you can use XQuery as follows:

 select @data.query(' element Main { for $j in Main/test return element test { for $i in $j/* order by $i/@abc return $i } } ') 

demo sql

update 2

To sort by integer attribute values, use cast as <type> :

 select @data.query(' element Main { for $j in Main/test return element test { for $i in $j/* order by $i/@abc cast as xs:integer? return $i } } ') 

demo sql