SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (XmlData xml);
INSERT YourTable VALUES ('<MainStuff>
    <TypeOneItem>
        <Name>Not this one</Name>
        <Value>Do not get this value</Value>
    </TypeOneItem>
    <TypeOneItem>
        <Name>Not this one either</Name>
        <Value>Do not get this value</Value>
    </TypeOneItem>
    <TypeOneItem>
        <Name>Look for this name</Name>
        <Value>Get this value</Value>
    </TypeOneItem>
</MainStuff>'), ('<MainStuff>
    <TypeTwoItem>
        <Name>Not this one</Name>
        <Value>Do not get this value</Value>
    </TypeTwoItem>
    <TypeTwoItem>
        <Name>Not this one either</Name>
        <Value>Do not get this value</Value>
    </TypeTwoItem>
    <TypeTwoItem>
        <Name>Look for this name</Name>
        <Value>Get this value</Value>
    </TypeTwoItem>
</MainStuff>');
2 rows affected

SELECT
  x1.item.value('(Value/text())[1]', 'varchar(100)')
FROM YourTable AS t
CROSS APPLY t.XmlData.nodes('MainStuff/*[Name/text() = "Look for this name"]') AS x1(item);


(No column name)


Get this value

Get this value


SELECT
  t.XmlData.value('(MainStuff/*[Name/text() = "Look for this name"]/Value/text())[1]', 'varchar(100)')
FROM YourTable AS t;


(No column name)


Get this value

Get this value


SELECT
  x1.item.value('(Value/text())[1]', 'varchar(100)')
FROM YourTable AS t
CROSS APPLY t.XmlData.nodes('MainStuff/*[substring(local-name(), 1, 4) = "Type"][Name/text() = "Look for this name"]') AS x1(item);


(No column name)


Get this value

Get this value