How cast in XML for aggregate functions
- by renegm
In SQL Server 2008.
I need execute a query like that:
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 return nothing (apparently because convert xdt:untypedAtomic to numeric)
How to "cast" r/c to varchar?
Something like
SELECT @x.query('fn:max(«CAST(r/c «AS varchar(20))»)')
Edit:
Using Nodes the function MAX is from T-SQL no fn:max function
In this code:
DECLARE @x xml;
SET @x = '';
SELECT @x.query('fn:max((1, 2))');
SELECT @x.query('fn:max(("First Text", "Other Text"))');
both query return expected: 2 and "Other Text"
fn:max can evaluate string expression ad hoc. But the first query dont work.
How to force string arguments to fn:max?
