主页 / dba / 问题 / 215491
Accepted
mheptinstall
Asked: 2018-08-22 06:56:20 +0800 CST

如何查询 XML 列集中的确切值

  • 772

我有一个包含 80 多个稀疏列以及一个列集列的表，这是一个简短的示例：

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

我想查询列集以识别任何列的值为 POSITIVE 的行。

在列集上使用该value方法会将所有值连接到一个字符串中，我可以使用LIKE，但我不想要值在另一个字符串中的结果。

SELECT
    *

FROM
    #ColumnSet

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

是否有查询列集以实现上述目标的替代方法？APPLY与该方法一起使用可nodes提供相同的串联字符串输出，尽管我的语法可能不正确。

所需的输出：

id
1
3
sql-server sql-server-2016

3 个回答

  1. Best Answer

    在谓词之前指定text()节点比在谓词中指定节点更有效text()。

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

    在此处输入图像描述

    谓词中包含文本的查询计划AllValues.exist('*[text() = "POSITIVE"]') = 1

    在此处输入图像描述

    • 8

  2. 这是一个 XPath 解决方案，但我不知道它的效率如何。

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

  3. 这是另一个 xquery 解决方案：

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

    由于您更改了样本数据，因此上述内容将不起作用。

    你可以这样做：

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

    但我不确定它将如何在规模上与 Mikael 的答案竞争。

    坚果

    • 4

相关问题