SQL get value from XML in tag, by tag value

I have the following XML:

<Main>
    <ResultOutput>
        <Name>TEST1</Name>
        <Value>D028</Value>
    </ResultOutput>
    <ResultOutput>
        <Name>TEST2</Name>
        <Value>Accept</Value>
    </ResultOutput>
    <ResultOutput>
        <Name>TEST3</Name>
        <Value />
    </ResultOutput>
</Main>

What I want is to get the value of the <value> tag in SQL.

Basically want to say get <value> where <Name> has the value of TEST1, as an example

This is what I have at the moment, but this depends on the position of the XML tag:

XMLResponse.value(Main/ResultOutput/Value)[5]', nvarchar(max)')

Answer

The best way to do this is not to put extra where .value clauses, but to do it directly in XQuery.

Use [nodename] to filter by a child node, you can even nest such predicates. text() gets you the inner text of the node:

XMLResponse.value('(/Main/ResultOutput[Name[text()="TEST1"]]/Value/text())[1]', 'nvarchar(max)')