問題描述
假設我們有:
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>';
游樂場
現在我想根據 id 屬性合并 2 個 XML:
Now I want to merge 2 XMLs based on id attribute:
id = 1 的用戶的最終結果:
Final result for user with 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"操作.
Please note that it is not combining 2 XMLs but "upsert" operation.
備注:
- 我知道這種模式違反了數據庫規范化，規范化是可行的方法(但在這種情況下不是)
- 我知道使用派生表的解決方案，
.nodes()和.value()函數首先解析兩個 XML，然后合并并寫回
- I know that this kind of schema violates database normalization and normalizing it is the way to go (but not in this case)
- I know solution that utilize derived tables,
.nodes()and.value()functions first to parse both XML, then merge and write back
我正在尋找的是 XPath/XQuery 表達式，它將合并到一個語句中(無派生表/動態 sql*):
I am searching for is XPath/XQuery expression that will merge it in one statement (no derived tables/dynamic-sql*):
* 如果絕對需要，可以使用動態 SQL，但我想避免使用.
UPDATE #Users
SET suggestions.modify(... sql:variable("@suggestions") ...); --changes only here
WHERE id = @userId;
/* replace ... for ... where ... with sql:variable */
推薦答案
嘗試了一段時間后，我認為這是不可能的...
After trying around a while I think this is not possible...
這里有類似的問題:XQuery 在單個 SQL 更新命令中添加或替換屬性
.modify(insert Expression1 ... ) 不允許在通過 @sql:variable() 傳入的 XML 內獲取數據code> 或 sql:column()
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模板網！