How to use in XML for aggregate functions

Question

In SQL Server 2008.

I need to execute a query like this:

DECLARE @x AS xml
SET @x=N'<r><c>First Text</c></r><r><c>Other Text</c></r>'
SELECT @x.query('fn:max(r/c)')

But don't return anything (apparently because convert xdt: untypedAtomic to numeric)

How to "drop" r / c in varchar?

Sort of

SELECT @x.query('fn:max(«CAST(r/c «AS varchar(20))»)')

Edit: Using nodes, the MAX function is a T-SQL function no fn: max In this code:

DECLARE @x xml;
SET @x = '';
SELECT @x.query('fn:max((1, 2))');
SELECT @x.query('fn:max(("First Text", "Other Text"))');

expected request return: 2 and Other text fn: max can evaluate the ad hoc string expression. But the first request does not work. How to force string arguments to fn: max?

Answer 1

This will perform the aggregate max function in T-SQL:

DECLARE @x AS xml
 SET @x=N'<r><c>First Text</c></r><r><c>Other Text</c></r>'

  SELECT 
  MAX(r.value('.','varchar(25)'))
  FROM @x.nodes('/r/c') r([r])

Returns

Other Text

, , . , , MS SQL Server 2008 R2.

DECLARE @x AS xml
SET @x=N'<r><c>First Text</c></r><r><c>Other Text</c></r>'
SELECT @x.query('fn:max(xs:string(r/c))') 

:

Msg 2365, Level 16, State 1, Line 3
XQuery [query()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:string'

Microsoft cast , , .

Answer 2

SLoW... ...

DECLARE @x AS xml
    , @val nvarchar(100) = 'Other Text'
SET @x=N'<r><c>First Text</c></r><r><c>Other Text</c></r>'
SELECT @x.query('fn:max(for $r in //r return xs:string ($r))')