sql - SQL Server XPath条件查询

我有一张表，其中一个名为“ configuration”的字段是XML类型：

<configuration>
  <element value="john" />
  <element value="kevin" />
  <element value="lisa" />
  <element value="david" />
  <element value="mike" />
</configuration>

我想做的是检索所有表记录，或者至少对具有包含至少一个“元素”属性的“配置”字段的表配置进行计数，其中“元素”属性的“值”属性等于“ lisa”。

目前，我所拥有的查询可以检索指定“元素”位置的“值”属性，例如：

select Configuration.value('(/configuration/element/@value)[0]', 'nvarchar(max)') // returns me 'john'
select Configuration.value('(/configuration/element/@value)[1]', 'nvarchar(max)') // returns me 'kevin'

最佳答案

在没有数据集的情况下，这有点伪SQL，而且有点猜测。在这种情况下（我想），您只想返回存在节点configuration/element具有value属性'lisa'的行（？）。如果我的猜测是正确的，那么类似的事情将起作用（您将不得不替换大括号（{}）中的对象名称：

SELECT {Columns}
FROM [{Your Table}] YT
WHERE EXISTS (SELECT 1
              FROM [{Your Table}] E
                   CROSS APPLY E.[{Your XML Column}].nodes('configuration/element') C(E)
              WHERE E.[{Your ID Column}] = YT.[{Your ID Column}]
                AND C.E.value('./@value','varchar(50)') = 'lisa');

例：

WITH VTE AS(
    SELECT 1 AS ID,
           CONVERT(xml,'<configuration>
  <element value="john" />
  <element value="kevin" />
  <element value="lisa" />
  <element value="david" />
  <element value="mike" />
</configuration>') AS XMlCol
    UNION ALL
    SELECT 2 AS ID,
           CONVERT(xml,'<configuration>
  <element value="craig" />
  <element value="donald" />
  <element value="jenny" />
  <element value="jayne" />
</configuration>') AS XMlCol)

SELECT *
FROM VTE YT
WHERE EXISTS (SELECT 1
              FROM VTE E
                   CROSS APPLY E.XMlCol.nodes('configuration/element') C(E)
              WHERE E.ID = YT.ID
                AND C.E.value('./@value','varchar(50)') = 'lisa');

这只会返回ID为1的行。