SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[MyObjects](
    [Id] [bigint] identity,
    [Details] [xml])

INSERT MyObjects(Details) VALUES('<Objects>
    <List>
        <e>
            <Name>Standard bike</Name>
            <Value>300</Value>
        </e>
        <e>
            <Name>Super bike</Name>
            <Value>1000</Value>
        </e>
    </List>
</Objects>')
1 rows affected

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)


Id
most expensive object


1
Super bike


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;


Id
most expensive object


1
Super bike