If XML child element value contains string return other child element value at same level I have a database table named Warehouses that contains rows of data related to specific warehouses. One column in that table is named itemStats and contains XML data in the following format:

&lt;itemStats xmlns="http://www.blahblahblah.com/blah"&gt;
  &lt;itemCounts&gt;
    &lt;item&gt;
      &lt;name&gt;Toaster Oven Device&lt;/name&gt;
      &lt;count&gt;6&lt;/count&gt;
    &lt;/item&gt;
    &lt;item&gt;
      &lt;name&gt;Washing Machine&lt;/name&gt;
      &lt;count&gt;2&lt;/count&gt;
    &lt;/item&gt;
    &lt;item&gt;
      &lt;name&gt;Microwave Oven&lt;/name&gt;
      &lt;count&gt;4&lt;/count&gt;
    &lt;/item&gt;
  &lt;/itemCounts&gt;




I'd like to query the table to check that column in each row for the existence of some string (example: Oven) and if it finds it, return the count associated with it. So if I had the above data in itemStats  for a given row in Warehouses, I'd like it to return the following along with other matches from other rows:

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4


I've attempted several times using SQL value() and nodes() but am having trouble producing the desired results. I know the [1] is wrong in the below query, but I'm not sure how to reference a dynamic location:

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1
Use nodes() and value() to get the names and count in a derived table and filter your rows in the main query.

declare @T table(itemStates xml not null);

insert into @T (itemStates)
values ('&lt;itemStats xmlns="http://www.blahblahblah.com/blah"&gt;
           &lt;itemCounts&gt;
             &lt;item&gt;
               &lt;name&gt;Toaster Oven Device&lt;/name&gt;
               &lt;count&gt;6&lt;/count&gt;
             &lt;/item&gt;
             &lt;item&gt;
               &lt;name&gt;Washing Machine&lt;/name&gt;
               &lt;count&gt;2&lt;/count&gt;
             &lt;/item&gt;
             &lt;item&gt;
               &lt;name&gt;Microwave Oven&lt;/name&gt;
               &lt;count&gt;4&lt;/count&gt;
             &lt;/item&gt;
           &lt;/itemCounts&gt;
         &lt;/itemStats&gt;');

with xmlnamespaces(default 'http://www.blahblahblah.com/blah')
select T.Name,
       T.Count
from (
     select I.X.value('(name/text())[1]', 'nvarchar(max)') as Name,
            I.X.value('(count/text())[1]', 'int') as Count
     from @T as T
       cross apply T.itemStates.nodes('/itemStats/itemCounts/item') as I(X)
     ) as T
where T.Name like '%Oven%';