SQL Server : FOR XML sorting control by attribute

Question

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

I have some problem about sorting elements issues.

For example, there is simple code as below.

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

As you can see tree and 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 has good idea to solve this problem?

Thank you.

Sorry I have detail question as 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>

To 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>

Thank you.

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>

sql fiddle demo

update:

To reorder multiple nodes, you can use XQuery like this:

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

sql fiddle demo

update 2

To order by integer values of attributes, 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
            }
    }
')

sql fiddle demo