SQL Server XML select node having highest value-CodePudding

I have a SQL Table

CREATE TABLE [dbo].[MyObjects](
    [Id] [bigint],
    [Details] [xml])

content of XML column:

<Objects>
    <List>
        <e>
            <Name>Standard bike</Name>
            <Value>300</Value>
        </e>
        <e>
            <Name>Super bike</Name>
            <Value>1000</Value>
        </e>
    </List>
</Objects>

How do I select an object name with highest value?

This is as far as I went:

SELECT Id, objectDetails.e.value('(Name/text())[1]','varchar(max)')  as 'most expensive object'
FROM MyObjects
CROSS APPLY MyObjects.Details.nodes('/Objects/List/e') objectDetails(e)

Ps. If you know more effitient way then CROSS APPLY then let me know

CodePudding user response：

You can use a FLWOR expression containing an order by. You need to cast your value to xs:int first, otehrwise it will be interpreted as text.

SELECT
  Id,
  objectDetails.e.value('(Name/text())[1]','varchar(max)')  as [most expensive object]
FROM MyObjects
CROSS APPLY MyObjects.Details.nodes('
   (
    for $e in /Objects/List/e
    order by xs:int(($e/Value/text())[1]) descending
    return $e
   )[1]
  ') objectDetails(e);

If you just want a single value from the XML, you can place the whole thing inside .value. This will generally be faster for a single one, but slower if you need multiple .values.

SELECT
  Id,
  Details.value('
  (
    for $e in /Objects/List/e
    order by xs:int(($e/Value/text())[1]) descending
    return $e/Name/text()
   )[1]','varchar(max)')  as [most expensive object]
FROM MyObjects;

db<>fiddle