Finding XML elements by value even when the parent element is different

I have a SQL Server database table with a XML column, and I want to extract some specific data from it. Unfortunately, the XML structure changes depending on what type of item is being used.

For example, the XML for a type one item would look like this:

<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>

The XML for a type two item would look like this:

<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>

In each case, I want to find the value for the record with a name of "Look for this name" - i.e. I will want to return "Get this value" for them both.

I can do it individually like this (where the column name is XmlData):

SELECT
    XmlData.value('(//TypeOneItem[Name = "Look for this name"]/Value/text())[1]', 'VarChar(100)')

SELECT 
    XmlData.value('(//TypeTwoItem[Name = "Look for this name"]/Value/text())[1]', 'VarChar(100)')

However, I want to be able to get a list of all of these values in one go, irrespective of whether they're Type One or Type Two:

SELECT
    XmlData.value('(//EveryItemType[Name = "Look for this name"]/Value/text())[1]', 'VarChar(100)')

There are actually far more than just Type One and Type Two, so a Coalesce would be impractical. And for what it's worth, I didn't come up with this structure and I can't change it.

I'm hoping there's a really easy answer to this, but I haven't been able to find anything useful by googling.

Solution

You can use /* to descend to a node of any name.

You can either do this in a .nodes call

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);

Or directly in a .values call

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

You can also do substring-ing on the local-name() function to identify nodes which begin Type

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);

db<>fiddle