Sql-server – If XML child element value contains string return other child element value at same level

sql-server-2012xml

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

Best Answer

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%';
Related Question