SQL Server XML select node having highest value

user_2818842 0
Viewed 42

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

sql-server sql
2 Answers

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

user_148689970

Xpath max() is an option and get the Name either with values() or cross apply if there are multiple nodes with Value = max(Value).

CREATE TABLE [dbo].[MyObjects](
    [Id] [bigint],
    [Details] [xml]);
    
insert into myobjects(id, details) values(1, '<Objects>
    <List>
        <e>
            <Name>Standard bike</Name>
            <Value>300</Value>
        </e>
        <e>
            <Name>Super bike</Name>
            <Value>1000</Value>
        </e>
        <e>
            <Name>Super bike X</Name>
            <Value>1000</Value>
        </e>
    </List>
</Objects>')



select Id, Details.value('(/Objects/List/e[Value[text() = max(/Objects/List/e/Value/text())]]/Name/text())[1]', 'varchar(100)') 
from MyObjects;

select Id, n.e.value('(Name/text())[1]', 'varchar(100)'), n.e.value('(Value/text())[1]', 'int')
from MyObjects as m
cross apply m.Details.nodes('/Objects/List/e[Value[text() = max(/Objects/List/e/Value/text())]]') as n(e);
user_124036120
Related