Início / dba / Perguntas / 215491
Accepted
mheptinstall
Asked: 2018-08-22 06:56:20 +0800 CST

Como consultar valores exatos no conjunto de colunas XML

  • 772

Eu tenho uma tabela que contém mais de 80 colunas esparsas junto com uma coluna de conjunto de colunas, este é um breve exemplo:

DROP TABLE IF EXISTS #ColumnSet
GO

CREATE TABLE #ColumnSet
(
    Id        INT          NOT NULL
  , Value1    VARCHAR(100) SPARSE NULL 
  , Value2    VARCHAR(100) SPARSE NULL 
  , Value3    VARCHAR(100) SPARSE NULL 
  , Value4    VARCHAR(100) SPARSE NULL 
  , AllValues XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

INSERT INTO #ColumnSet
(Id, Value1, Value2, Value3, Value4)

VALUES
(1, 'POSITIVE', NULL, NULL, NULL),
(2, 'NEGATIVE', NULL, 'NEGATIVE', NULL),
(3, NULL, NULL, 'NEGATIVE', 'POSITIVE'),
(4, 'NEGATIVE', NULL, 'THIS IS NOT A POSITIVE RESULT', NULL)
GO

Eu quero consultar o conjunto de colunas para identificar as linhas em que qualquer uma das colunas tem um valor POSITIVO.

Usar o valuemétodo no conjunto de colunas concatenará todos os valores em uma string e eu poderia usar LIKE, mas não quero resultados em que o valor esteja dentro de outra string.

SELECT
    *

FROM
    #ColumnSet

WHERE
    AllValues.value('/', 'nvarchar(4000)') LIKE '%POSITIVE%'

Existem métodos alternativos de consultar um conjunto de colunas para alcançar o acima? Usar APPLYjunto com o nodesmétodo fornece a mesma saída de string concatenada, embora minha sintaxe possa estar incorreta.

A saída necessária:

id
1
3
sql-server sql-server-2016

3 respostas

  1. Best Answer

    Especificar o text()nó antes do predicado será mais eficiente do que ter text()no predicado.

    select *
from #ColumnSet as C
where AllValues.exist('*/text()[. = "POSITIVE"]') = 1

    insira a descrição da imagem aqui

    Plano de consulta com texto no predicadoAllValues.exist('*[text() = "POSITIVE"]') = 1

    insira a descrição da imagem aqui

    • 8

  2. Aqui está uma solução XPath, embora eu não saiba o quão eficiente ela será.

    SELECT *
FROM #ColumnSet
WHERE AllValues.exist('//*[text() = "POSITIVE"]') = 1
    • 5

  3. Aqui está outra solução xquery:

    SELECT *, cs.AllValues.query('. [contains(., "POSITIVE")]')
FROM #ColumnSet AS cs
WHERE cs.AllValues.exist('. [contains(., "POSITIVE")]') = 1

    Como você alterou seus dados de amostra, o procedimento acima não funcionará.

    Você poderia fazer isso em vez disso:

    SELECT      cs.*
FROM        #ColumnSet AS cs
CROSS APPLY cs.AllValues.nodes('/*') AS x(c)
WHERE       x.c.exist('text()[. = "POSITIVE"]') = 1;

    Mas não tenho certeza de como isso competirá com a resposta de Mikael em escala.

    NOZES

    • 4

relate perguntas