XQuery 在单个 SQL 更新命令中添加或替换属性
我有一个带有 XML 列的表，如果属性已经存在，我想更新 xml 以插入属性或更改属性值.
I have a Table with an XML column, I want to update the xml to insert attribute or to change the attribute value if the attribute already exists.
假设起始 xml 是:</>
Let's say the starting xml is: < d />
插入:
UPDATE Table
set XmlCol.modify('insert attribute att {"1"} into /d[1]')
变化:
UPDATE Table
set XmlCol.modify('replace value of /d[1]/@att with "1"')
如果属性已经存在，插入将失败，如果属性不存在，替换将失败.我曾尝试使用 'if'，但我认为它行不通，出现错误:XQuery [modify()]:'attribute' 附近的语法错误，预期为 'else'."
insert will fail if the attribute already exists, replace will fail if the attribute doesn't exists. I have tried to use 'if' but I don't think it can work, there error I get: "XQuery [modify()]: Syntax error near 'attribute', expected 'else'."
IF 尝试
UPDATE Table
set XmlCol.modify('if empty(/d[1]/@att)
then insert attribute att {"1"} into /d[1]
else replace value of /d[1]/@att with "1"')
目前我选择 xml 到一个变量中，然后使用 T-SQL 修改它，然后用新的 xml 更新列，这需要我锁定事务中的行，并且对于数据库来说可能更昂贵.
Currently I select the xml into a variable and then modify it using T-SQL and then updating the column with new xml, this requires me to lock the row in a transaction and is probably more expensive for the DB.
推荐答案
据我所知，单条语句无法做到这一点.您可以使用 exist() 方法通过两个更新语句来完成此操作.
From what I can tell, you can't do this with single statement. You can use the exist() method to accomplish that with two update statements.
DECLARE @TestTable TABLE
(
Id int,
XmlCol xml
);
INSERT INTO @TestTable (Id, XmlCol)
VALUES
(1, '<d att="1" />'),
(2, '<d />'),
(3, '<d att="3" />');
SELECT * FROM @TestTable;
UPDATE @TestTable
SET XmlCol.modify('replace value of /d[1]/@att with "1"')
WHERE XmlCol.exist('(/d[1])[not(empty(@att))]') = 1;
UPDATE @TestTable
SET XmlCol.modify('insert attribute att {"1"} into /d[1]')
WHERE XmlCol.exist('(/d[1])[empty(@att)]') = 1;
SELECT * FROM @TestTable;
最终选择的输出是:
Id XmlCol
----------- -------------------
1 <d att="1" />
2 <d att="1" />
3 <d att="1" />
相关文章