Início / dba / Perguntas / 208715
Accepted
Peter
Asked: 2018-06-05 07:41:14 +0800 CST

Valor específico da lista de colunas XML

  • 772

Eu tenho uma coluna xml que se parece com isso:

<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......

Em vez de listar todo o conteúdo da coluna, quero listar apenas o valor de <string>football</string>quando o primeiro valor for <string>World Cup 2018</string>.

O uso value('(/document/item/key/string)[7]', 'nvarchar(max)')não é adequado, pois "Copa do Mundo 2018" pode aparecer em qualquer lugar.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Se você quiser apenas um único valor, basta usar o valuemétodo do tipo de dados XML com um pouco de XPath que se traduz em:

    Obtenha-me o primeiro valor de ...key/string que tem um elemento irmão de ...value/string sob o mesmo elemento de item com o valor "football"

    Algo assim:

    DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @yourTable ( yourXML )
SELECT 
'<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......  
</document>'


SELECT *, yt.yourXML.value('(document/item[key/string[.="World Cup 2018"]]/value/string/text())[1]', 'VARCHAR(50)') result
FROM @yourTable yt
    • 3

  2. Espero ter entendido seu pedido. A chave é

    where
     x.u.exist('key/string[text()="World Cup 2018"]') = 1

    Aqui está o selecionado:

     select
    x.u.value('(key/string/text())[1]','varchar(50)') as myKey
    ,x.u.value('(value/string/text())[1]','varchar(50)') as myValue
 from @xml_Text.nodes('document/item') as x(u)
 where
     x.u.exist('key/string[text()="World Cup 2018"]') = 1

    resultado:

    myKey           myValue
World Cup 2018  football

    dbfiddle

    ou maneira antiga:

    SELECT
       a.myKey ,a.myValue
 from
 (
 select
    x.u.value('(key/string/text())[1]','varchar(50)') as myKey
    ,x.u.value('(value/string/text())[1]','varchar(50)') as myValue
 from @xml_Text.nodes('document/item') as x(u) 
 )a
 where a.myKey = 'World Cup 2018'
    • 2

relate perguntas