主页 / dba / 问题 / 250599
Accepted
Lava Viperidae
Asked: 2019-10-09 15:19:48 +0800 CST

如果 XML 子元素值包含字符串，则返回同级的其他子元素值

  • 772

我有一个名为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），如果找到它，则返回与其关联的计数。因此，如果我在Warehouses 中的给定行的itemStats中有上述数据，我希望它返回以下内容以及来自其他行的其他匹配项：

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
sql-server-2012 xml

1 个回答

  1. Best Answer

    使用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%';
    • 0

相关问题