假设我们有:

CREATE TABLE #Users(id INT PRIMARY KEY, name VARCHAR(100), suggestions XML);

INSERT INTO #Users(id, name, suggestions)
SELECT 1, 'Bob', N'<Products>
                     <Product id="1" score="1"/>
                     <Product id="2" score="5"/>
                     <Product id="3" score="4"/>
                   </Products>'
UNION ALL
SELECT 2, 'Jimmy', N'<Products>
                       <Product id="6" score="3"/>
                     </Products>';

DECLARE @userId INT = 1,
        @suggestions XML = N'<Products>
                              <Product id="2" score="5"/>
                              <Product id="3" score="2"/>
                              <Product id="7" score="1" />
                             </Products>';

Playground

现在我想根据 id 属性合并 2 个 XML:

id = 1 用户的最终结果:

<Products>
  <Product id="1" score="1"/> -- nothing changed (but not exists in @suggestions)
  <Product id="2" score="5"/> -- nothing changed (but exists in @suggestions)
  <Product id="3" score="2"/> -- update score to 2
  <Product id="7" score="1"/> -- insert new element
</Products>

请注意，它不是合并 2 个 XML，而是“upsert”操作。

备注:

• 我知道这种模式违反了数据库规范化并且规范化它是可行的方法(但在这种情况下不是)
• 我知道使用派生表的解决方案，.nodes() 和 .value() 函数首先解析两个 XML，然后合并并写回

我正在搜索的是 XPath/XQuery 表达式，它将把它合并到一个语句中(无派生表/动态 sql*):

^{* 如果绝对需要，可以使用动态 SQL，但我想避免它。}

UPDATE #Users
SET suggestions.modify(... sql:variable("@suggestions") ...); --changes only here
WHERE id = @userId;


/* replace ... for ... where ... with sql:variable */

最佳答案

经过一段时间的尝试，我认为这是不可能的......

这里有类似的问题:XQuery adding or replacing attribute in single SQL update command

.modify(insert Expression1 ... )不允许从通过 @sql:variable() 传入的 XML 中获取数据或 sql:column()

阅读此处:https://msdn.microsoft.com/en-us/library/ms175466.aspx在 Expression1 -> “常量 XML 或独立的 sql:column/sql:variable 或 XQuery(到同一实例)

DECLARE @xml1 XML= --the existing XML
'<Products>
  <Product id="1" score="1" />
  <Product id="2" score="5" />
  <Product id="3" score="4" />
</Products>';

DECLARE @xml2 XML= --the XML with new or changed data
'<Products>
  <Product id="2" score="5" />
  <Product id="3" score="2" />
  <Product id="7" score="1" />
</Products>';

SET @xml1.modify('insert sql:variable("@xml2") as first into /Products[1]');

SELECT @xml1;

/* The full node is inserted!
Without any kind of preparation there is NO CHANCE to get the inner nodes only

<Products>
  <Products>
    <Product id="2" score="5" />
    <Product id="3" score="2" />
    <Product id="7" score="1" />
  </Products>
  <Product id="1" score="1" />
  <Product id="2" score="5" />
  <Product id="3" score="4" />
</Products>
*/

您可以这样声明第二个 XML:

DECLARE @xml2 XML= --the XML with new or changed data
'<Product id="2" score="5" />
 <Product id="3" score="2" />
 <Product id="7" score="1" />';

但是你将没有机会使用 id 的值作为 XQuery 过滤器

SET @xml1.modify('insert sql:variable("@xml2") as first into /Products[**How should one filter here?**]');

最后但同样重要的是，我认为没有机会在一次调用 .modify() 中组合两个不同的 XML_DML 语句。 .

我唯一的想法就是这个，但它不起作用。 IF 似乎只能在表达式内 中使用，但不能区分两个执行路径

SET @xml1.modify('if (1=1) then
                     insert sql:variable("@xml2") as first into /Products[1]
                  else
                     replace value of /Products[1]/Product[@id=1][1]/@score with 100');

所以我的结论是:不，这是不可能的......

我这里提供的解决方案https://stackoverflow.com/a/35060150/5089204在第二部分(“如果你想‘合并’两本书结构”)将是我解决这个问题的方法。

关于SQL Server - 仅使用 .modify() 合并两个 XML，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35080942/