时间戳

首页 > 解决方案 > xml.exist - sql：要排序的列

sql - xml.exist - sql：要排序的列

问题描述

我尝试使用 sql:column-function 使用 xml.exist 创建一个查询，其中列的值应转换为可用的 XQuery 序列。

这是具有静态序列的查询，它正在工作。

SELECT
    FieldA
    , FieldB
FROM
    MyTable
WHERE
    FieldC.exist('DSAuth/role[@id=("195", "267", "350")')

为了获得动态序列，我将使用一个表函数返回一个表，其中包含一个列“IDsequence”，其中所有 id 都作为字符串。例如'“195”、“267”、“350”'。

表函数应该只返回一行！它可以使用多行，但我必须最后对结果进行分组，这对性能不利。

SELECT
    FieldA
    , FieldB
FROM
    MyTable
CROSS APPLY
    dbo.MyFunc(0) AS f
WHERE
    FieldC.exist('DSAuth/role[@id=sql:column("f.IDsequence")]')

有没有办法从 sql:column("f.IDsequence") 获取 XQuery 的可用序列？

感谢帮助。

编辑：

性能问题是 FieldB（以及更多字段）是一个 xml 列，所以我必须将其转换为 group by。

SELECT
    FieldA
    , CAST(CAST(FieldB AS nvarchar(max)) AS xml) AS FieldB
FROM
    MyTable
CROSS APPLY
    dbo.MyFunc(0) AS f
WHERE
    FieldC.exist('DSAuth/role[@id=sql:column("f.IDsequence")]')
GROUP BY
    FieldA
    , CAST(FieldB AS nvarchar(max))

标签： sqlxmlxquerysequence

解决方案

我不知道这是否是最简单的方法，但您可以尝试将您的列表包含到 XML 中并在谓词中使用它，如下所示：

--展示原理的模型表

DECLARE @tbl TABLE(TheXml XML)
INSERT INTO @tbl VALUES
(
    N'<root>
        <a>1</a>
        <a>2</a>
      </root>'
)
,(
    N'<root>
        <a>1</a>
      </root>'
)
,(
    N'<root>
        <a>3</a>
        <a>4</a>
      </root>'
);

--这很简单：只有一个值

SELECT * FROM @tbl WHERE TheXml.exist('/root/a[. cast as xs:int?=1]')=1;

——这是你的顺序。
--但是你不能用sql:column()or引入值列表sql:variable()。

SELECT * FROM @tbl WHERE TheXml.exist('/root/a[. cast as xs:int?=(2,3)]')=1;

--但是您可以在此cte之前将值添加到您的 XML

DECLARE @values TABLE(val INT);
INSERT INTO @values VALUES(2),(3);

WITH cte(NewXml) AS
(
    SELECT (SELECT (SELECT val AS [@val] FROM @values FOR XML PATH('values'),TYPE)
                  ,TheXml AS [*] 
            FOR XML PATH(''),TYPE
           )
    FROM @tbl t 
)
SELECT NewXml.query('/root') TheXml
FROM cte
WHERE NewXml.exist('/root/a[. cast as xs:int?=/values/@val]')=1;

中间 XML 如下所示：

<values val="2" />
<values val="3" />
<root>
  <a>1</a>
  <a>2</a>
</root>

最终.query('/root')将返回以前的 XML 不变。

更新

同样适用于字符串基础的介绍，如下所示：

WITH cte(NewXml) AS
(
    SELECT (SELECT CAST(N'<values val="2" /><values val="3" />' AS XML)
                  ,TheXml AS [*] 
            FOR XML PATH(''),TYPE
           )
    FROM @tbl t 
)
SELECT NewXml.query('/root') TheXml
FROM cte
WHERE NewXml.exist('/root/a[. cast as xs:int?=/values/@val]')=1

推荐阅读