3

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?

Josiah Ruddell
  • 29,697
  • 8
  • 65
  • 67
renegm
  • 610
  • 2
  • 5
  • 12
  • Can you provide an example of the output your looking for? – Demitrius Nelon Dec 23 '10 at 17:15
  • I edit post. I need Max(First Text, Other Text) =Other Text as output of SELECT @x.query('fn:max(r/c)'). With numeric data this query works – renegm Dec 23 '10 at 19:50
  • 1
    Sorry, I don't think you can achieve what you want - using .nodes() is the only way to go as far as I can see..... – marc_s Dec 23 '10 at 21:29

2 Answers2

3

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

After your update, I think I better understand your question. Unfortunately It looks like this may not be doable in 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))')

Yields the error:

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

According to Microsoft the type cast is valid, but I haven't been able to find a syntax that will work.

Demitrius Nelon
  • 1,210
  • 1
  • 10
  • 29
  • Sorry but using nodes is not an answer is a workaround. With nodes the MAX function is from T-SQL not XML agregate funtion – renegm Dec 23 '10 at 19:33
  • The message is confusing or wrong: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:string'. The real problem is not converting from one type to another. xs:string works fine over ONE VALUE, not over colection. This works: SELECT @x.query('fn:max(xs:string((r/c)[1]))') – renegm Dec 23 '10 at 23:12
3
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))')
Dale K
  • 25,246
  • 15
  • 42
  • 71
Steven
  • 46
  • 2