sql-server - SQL Server 2014 - XQuery - 获取逗号分隔的列表

Question

我在 SQL Server 2014 中有一个只有 ID 的数据库表列 ( int ) 和一列 xmldata类型 XML .

这个xmldata列包含例如:

<book>
    <title>a nice Novel</title>
    <author>Maria</author>
    <author>Peter</author>
</book>

正如预期的那样，我有多本书，因此有 xmldata 的多行.

我现在想对所有书籍执行查询，其中彼得是作者。我在一些 xPath2.0 测试人员中进行了尝试，得出的结论是:

/book/author/concat(text(), if(position() != last())then ',' else '')

作品。

如果您尝试将此成功移植到 SQL Server 2014 Express 中，它看起来像这样，它是正确转义的语法等:

SELECT id
FROM books
WHERE 'Peter' IN (xmldata.query('/book/author/concat(text(), if(position() != last())then '','' else '''')'))

然而，SQL Server 似乎不支持像 /concat(...) 这样的结构。因为:

The XQuery syntax '/function()' is not supported.

但是我很茫然，为什么 /text()将在:

SELECT id, xmldata.query('/book/author/text()') 
FROM books

它确实如此。

我的限制:

我一定会使用 SQL Server

我被绑定(bind)到 xpath 或其他可以像上面的语句“注入(inject)”的东西(如果 xml 或数据库的结构发生变化，上面的 xpath 可以单独更改，上面构造 Where 子句的应用程序逻辑将不可触摸)见编辑

有没有办法使这项工作？

问候，

比尔门

编辑:

我的第二个约束归结为:

应用程序通过以下方式构造 Where 子句

expression <operator> value(s)

表达式存储在数据库中并由 xmlTag 映射，例如:

   | tokenname|  querystring
   | "author" | "xmldata.query(/book/author/text())"

这些值由请求用户提供。因此，如果用户使用运算符“EQUALS”询问作者“Peter”，则应用程序构造:

xmaldata.query(/book/author/text()) = "Peter"

作为 where 子句。

如果客户现在决定需要将作者嵌套在 <authors>元素，我可以简单地更改构造数据库中的表达式，整台机器保持运行，无需更改任何代码，易于管理。

所以我需要一种方法来实现这一点

<xPath> <operator> "Peter"

或这三个独立组件的任何其他组合 (见上文: "Peter" IN <xPath>... )即使有多个未排序的作者，我也能得到 Peters 的所有书籍。

这也不够(它不是 sqlserver 语法，但你明白了):

WHERE xmldata.exist('/dossier/client[text() = "$1"]', "Peter") = 1;

因为运算符仍然嵌套在表达式中，我无法请求 <> "Peter" .

我知道这很奇怪，请不要质疑整个概念 - 它有历史:/

编辑:进一步澄清:

过滤规则基本上以 XML 结构进入应用程序:

运算符:“情商”

字段:“名称”

值(value)“彼得”

评估为:

表达式 = lookupExpressionForField("name") --> "table2.xmldata.value('book/author/name[1]', 'varchar')"

运算符 = lookUpOperatorMapping("EQ") --> "="

值(value) = FormatValues("Peter") --> "Peter"(如果传递了多个值，FormatValues 构成一个逗号分隔的列表)

然后应用程序构建:
- constructClause(String expression,String operator,String value)
"table2.xmldata.value('book/author/name[1]', 'varchar')"+ "="+ "Peter"

然后构造一个 Select 语句，其结果为 WHERE 子句。

它不会像这样构建它，未转义，未过滤以进行注入(inject)等，但这是基本思想。

我可以影响输入的转换方式，这意味着我可以实现以下方法:

lookupExpressionForField(String field)

lookUpOperatorMapping(String operator)

Formatvalues(List<String> values) | Formatvalues(String value)

constructClause(String expression,String operator,String value)

但是我选择这样做，我可以更改参数类型，我可以自由地实现它们。当然越少越好。因此，简单地用 xPath 构建一个逗号分隔的列表将是最佳的(就像我可以在 sqlserver 中的某个地方勾选“在 xPath 中启用/function()-syntax”并且/concat(if...) 会起作用)

Answer 1

像这样的东西怎么样:

SET NOCOUNT ON;

DECLARE @Books TABLE (ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, BookInfo XML);

INSERT INTO @Books (BookInfo)
VALUES (N'<book>
    <title>a nice Novel</title>
    <author>Maria</author>
    <author>Peter</author>
</book>');

INSERT INTO @Books (BookInfo)
VALUES (N'<book>
    <title>another one</title>
    <author>Bob</author>
</book>');

SELECT *
FROM @Books bk
WHERE bk.BookInfo.exist('/book/author[text() = "Peter"]') = 1;

这仅返回第一个“书”条目。从那里您可以使用“值”函数提取 XML 字段的任何部分。

“exist”函数返回一个 bool 值/BIT。这将扫描“book”中的所有“author”节点，因此无需连接到逗号分隔的列表中，仅用于 IN 列表，这无论如何都不起作用;-)。

有关“值”和“存在”函数以及用于 XML 数据的其他函数的更多信息，请参阅:

xml Data Type Methods