If XML child element value contains string return other child element value at same level

Question

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:

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

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

Answer 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 ('<itemStats xmlns="http://www.blahblahblah.com/blah">
 <itemCounts>
 <item>
 <name>Toaster Oven Device</name>
 <count>6</count>
 </item>
 <item>
 <name>Washing Machine</name>
 <count>2</count>
 </item>
 <item>
 <name>Microwave Oven</name>
 <count>4</count>
 </item>
 </itemCounts>
 </itemStats>');
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%';

• Lava Viperidae: Thank you for providing this and the brief explanation. It did help me better understand the path to the solution.