SQL Server:FOR XML按属性排序控制

如果要重新排序现有xml,可以使用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>')

结果:

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

sql fiddle demo

更新:

要重新排序多个节点,您可以像这样使用XQuery:

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

更新2

要按属性的整数值排序,请使用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