CODEWITHSUNDEEP
sql-server-2008xquery-sql
Daniel
Daniel

Reputation: 155

xquery in sql server 2008

what is the best xquery method to get the max attribute value from the document. Eg

    <organizations>
       <organization id="1"/>
       <organization id="6"/>
       <organization id="11"/>
    </organizations>

I want the query to return 11.

Thanks

Upvotes: 0

Views: 469

Answers (3)

Jens Erat
Jens Erat

Reputation: 38672

The XQuery snippet you're looking for is max(/organizations/organization/@id).

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @xml xml = '
<organizations>
  <organization id="1"/>
  <organization id="6"/>
  <organization id="11"/>
</organizations>'

select @xml.value('max(/organizations/organization/@id)', 'int')

Upvotes: 3

marc_s
marc_s

Reputation: 754388

Try this:

DECLARE @input XML = '<organizations>
       <organization id="1"/>
       <organization id="6"/>
       <organization id="11"/>
    </organizations>'


SELECT
    MAX(org.value('(@id)[1]', 'int'))
FROM
    @input.nodes('/organizations/organization') AS Orgs(Org)

You need to use .nodes() to get a list of all possible values, and then apply MAX() to that list of integer values.

Upvotes: 1

Related Questions