时间戳

首页 > 解决方案 > 为什么 SQL Server 的 xml.modify 不使用 JOIN 更新所有指定的元素？

sql - 为什么 SQL Server 的 xml.modify 不使用 JOIN 更新所有指定的元素？

问题描述

我已经用谷歌搜索并阅读了很多内容，但我仍然不明白为什么这段代码不起作用。我知道 .modify() 是一个“单例”，但它没有向我解释为什么它没有在返回的集合中每行执行一次。这对我来说是零意义，我只是希望能有所了解。老实说，这感觉像是一个错误，而不是预期的行为。

基本上，我正在尝试根据我无法提前预测的输入表更新 xml（每个客户端都会有所不同）。我想通过加入一个描述更新的表并让它工作来更新。但事实并非如此。

我把它归结为这个例子。在本例中，我希望代码更新使用veget="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 语句。我想，用 JOIN 更新应该做正确的事情。我很困惑为什么它没有。

标签： sqlsql-serverxml

解决方案

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

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 Server 2019 仍然不支持基于 w3c 标准的 XQuery 3.0 或 3.1。

有用的链接：

(1) XQuery 更新 (2) SQL Server vNext（2019 年后）和 NoSQL 功能

推荐阅读