如果 XML 子元素值包含字元串，則返回同一級別的其他子元素值

我有一個名為Warehouses的數據庫表，其中包含與特定倉庫相關的數據行。該表中的一列名為itemStats並包含以下格式的 XML 數據：

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

我想查詢該表以檢查每一行中的該列是否存在某個字元串（例如：Oven），如果找到它，則返回與其關聯的計數。因此，如果我在itemStats中有****Warehouses 中給定行的上述數據，我希望它返回以下內容以及來自其他行的其他匹配項：

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

我已經嘗試過多次使用 SQL value() 和 nodes() 但無法產生所需的結果。我知道**$$ 1 $$**在下面的查詢中是錯誤的，但我不確定如何引用動態位置：

;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

使用nodes()andvalue()獲取派生表中的名稱和計數，並在主查詢中過濾您的行。

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

引用自：https://dba.stackexchange.com/questions/250599