Apr 27, 2014

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

No comments:

Post a Comment