SQL Server-仅使用.modify（）合并两个XML

一尘不染

SQL Server-仅使用.modify（）合并两个XML

sql

假设我们有：

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](https://data.stackexchange.com/stackoverflow/query/429347)**

现在，我想基于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将其合并为一个语句（无派生表/ dynamic-sql *）的表达式：

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

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


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

阅读 199

2021-05-16

共1个答案

一尘不染

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

在.modify(insert Expression1 ... )不允许获得的数据中
经由通过一个XML@sql:variable()或sql:column()

在此处阅读：https :
//msdn.microsoft.com/zh-
cn/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?**]');

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

我唯一的想法就是这个，但是它不起作用。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');

所以我的结论是：不，这是不可能的…

2021-05-16