Início / dba / Perguntas / 250599
Accepted
Lava Viperidae
Asked: 2019-10-09 15:19:48 +0800 CST

Se o valor do elemento filho XML contiver string, retorne outro valor do elemento filho no mesmo nível

  • 772

Eu tenho uma tabela de banco de dados chamada Warehouses que contém linhas de dados relacionados a armazéns específicos. Uma coluna nessa tabela é denominada itemStats e contém dados XML no seguinte formato:

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

Gostaria de consultar a tabela para verificar nessa coluna em cada linha a existência de alguma string (exemplo: Forno) e se encontrar, retornar a contagem associada a ela. Portanto, se eu tivesse os dados acima em itemStats para uma determinada linha em Warehouses , gostaria que ele retornasse o seguinte junto com outras correspondências de outras linhas:

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

Eu tentei várias vezes usando SQL value() e nodes(), mas estou tendo problemas para produzir os resultados desejados. Eu sei que o [1] está errado na consulta abaixo, mas não tenho certeza de como fazer referência a um local dinâmico:

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

  1. Best Answer

    Use nodes()e value()para obter os nomes e a contagem em uma tabela derivada e filtre suas linhas na consulta principal.

    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

relate perguntas