問題描述

我有一個帶有 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" />

這篇關于XQuery 在單個 SQL 更新命令中添加或替換屬性的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！