为什么带有联接的SQL Server的xml.modify不更新所有指定的元素？

Question

我在谷歌上搜索并阅读了一大堆这篇文章，但我仍然不明白为什么这段代码不能工作。我知道.modify()是一个“单例”，但是它不能解释为什么它不能在返回的集合中每行执行一次。这对我来说毫无意义，我只是希望能达成一些理解。老实说，这感觉像是一个bug，而不是预期的行为。

基本上，我尝试基于一个输入表来更新xml，这些输入表是我不能提前预测的(对于每个客户端来说都是不同的)。我想要通过连接到描述更新的表并使其工作来进行更新。但事实并非如此。

我已经把它归结为这个例子。在本例中，我期望代码更新使用vegetable="false“属性指定的每个节点。注意，我使用"sql:Column()“语法导航到要更新的正确节点。但它只更新第一个：

SET NOCOUNT ON;
DECLARE @xml xml = '<top>
    <element name="apple">red</element>
    <element name="pear">green</element>
    <element name="banana">yellow</element>
</top>';

DECLARE @xmlData TABLE (ID int, Contents xml);
DECLARE @elements TABLE (ID int, Element varchar(50));

INSERT INTO @xmlData (ID, Contents) VALUES (1, @xml);
INSERT INTO @elements(ID, Element)
VALUES (1, 'apple'), (2, 'pear'), (3, 'banana');

SET NOCOUNT OFF;
SELECT *
  FROM @xmlData CROSS JOIN @elements;

UPDATE x
   SET Contents.modify('insert attribute vegetable {"false"} into (/top/element[@name=sql:column("y.Element")])[1]')
  FROM @xmlData x
       CROSS JOIN @elements y

SELECT * FROM @xmlData;

我将select放入其中，以显示它返回三行，因此我预计会有三次更新。但是SET NOCOUNT OFF显示只更新了一行。这没有任何意义。

我发现解决这个问题的唯一方法是放入一个WHILE循环，但这似乎很荒谬，因为其意图已经在上面的代码中得到了完美而明确的说明……它就是不起作用。

这段代码可以工作，但我不知道为什么我必须经历这种黑客攻击和性能杀手，才能做一些痛苦的显而易见的事情：

SET NOCOUNT ON;
DECLARE @xml xml = '<top>
    <element name="apple">red</element>
    <element name="pear">green</element>
    <element name="banana">yellow</element>
</top>';

DECLARE @xmlData TABLE (ID int, Contents xml);
DECLARE @elements TABLE (ID int, Element varchar(50));

INSERT INTO @xmlData (ID, Contents) VALUES (1, @xml);
INSERT INTO @elements(ID, Element)
VALUES (1, 'apple'), (2, 'pear'), (3, 'banana');

SET NOCOUNT OFF;
SELECT *
  FROM @xmlData CROSS JOIN @elements;

DECLARE @maxElement int = (SELECT MAX(ID) FROM @elements);
DECLARE @currentElement int = 1;

WHILE (@currentElement <= @maxElement)
BEGIN
    UPDATE x
       SET Contents.modify('insert attribute vegetable {"false"} into (/top/element[@name=sql:column("y.Element")])[1]')
      FROM @xmlData x
           CROSS JOIN @elements y
     WHERE y.ID = @currentElement
     SET @currentElement += 1;
END

SELECT * FROM @xmlData;

有人能给我解释一下为什么在第一个代码块中不是每行执行一次修改吗？我在第二个块中的解决方案是这里的最佳解决方案吗？对我来说，这真的像是一个粗俗的黑客攻击。但是我事先不知道元素名称或元素名称的数量，所以我不能像大多数文章推荐的那样只编写多个UPDATE语句。我认为，使用连接进行更新应该是正确的。我很难理解为什么它不会。

Answer 1

这是一种通过使用XQuery FLWOR表达式来实现的方法。它是通过XML重构来实现的。此外，如果@vegetable属性已经存在，我还添加了一些保护。XQuery contains()函数检查元素是否在更新列表中。我添加了一个新的图元素来测试它。

SQL

-- DDL and sample data population, start
DECLARE @xmlData TABLE (ID INT IDENTITY PRIMARY KEY, Contents XML);
INSERT INTO @xmlData (Contents) 
VALUES ('<top>
    <element name="apple" vegetable="true">red</element>
    <element name="pear">green</element>
    <element name="fig">reddish</element>
    <element name="banana">yellow</element>
</top>');

DECLARE @elements TABLE (ID INT IDENTITY PRIMARY KEY, Element VARCHAR(50));
INSERT INTO @elements(Element)
VALUES ('apple'), ('pear'), ('banana');
-- DDL and sample data population, end

-- before
SELECT * FROM @xmlData;

DECLARE @element_list AS NVARCHAR(MAX) = '';

SELECT @element_list += ',[' + el.Element + ']'
FROM @elements AS el;

-- just to see
SELECT @element_list AS [element_list];

DECLARE @vegetable VARCHAR(10) = 'false';

UPDATE @xmlData
SET Contents = Contents.query('<top>
    {
        for $x in /top/element
        return if(empty($x/@vegetable)
                    and contains(sql:variable("@element_list"), concat("[",$x/@name,"]"))) then (
            <element name="{($x/@name)}">{data($x)}
                {attribute vegetable {sql:variable("@vegetable")}}
            </element>)
            else ($x)
    }
</top>');

-- after
SELECT * FROM @xmlData;

不幸的是，即使是最新的SQL Server2019仍然不支持基于w3c标准的XQuery 3.0或3.1。

有用的链接：

(1) XQuery Update (2) SQL Server vNext (post 2019) and NoSQL functionality