SQL Server - 仅使用 .modify() 合并两个 XML

The .modify(insert Expression1 ... ) does not allow to get data within an XML passed in via @sql:variable() or sql:column()

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

Read here: https://msdn.microsoft.com/en-us/library/ms175466.aspx at Expression1 -> "constant XML or stand alone sql:column / sql:variable or XQuery (to the same instance)

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:

You might declare the second XML as such:

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 过滤器

But than you'll have no chance to use the id's value as XQuery filter

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

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

And last but not least I think there is no chance to combine two different XML_DML statements within one call of .modify().

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

The only idea I had was this, but it doesn't work. IF seems to be usable only within an Expression, but not two distinguish between two execution paths

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');

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

So my conclusion: No, this is not possible...

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

The solution I provided here https://stackoverflow.com/a/35060150/5089204 in the second section ("If you want to 'merge' two Books-structures") would be my way to solve this.

这篇关于SQL Server - 仅使用 .modify() 合并两个 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！