XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

If we will execute following XQuery in sql server:

DECLARE @xml AS XML = ''

SELECT @xml.query('

      let $value := (1,2,3,5,6)

      order by $value descending

      return $value'

)

    

We may get error message something like this:

Msg 2389, Level 16, State 1, Line 5

XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

Cause: We cannot user order by clause using let since it needs singleton value. So we must have to user for clause to use order by clause.

Solution:

Correct your XQuery like this:

DECLARE @xml AS XML = ''

SELECT @xml.query('

      for $value in (1,2,3,5,6)

      order by $value descending

      return $value'

)

Output:

6 5 3 2 1