XQuery在单个SQL更新命令中添加或替换属性

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列的表，我想更新xml以插入属性，或者如果属性已存在则更改属性值。

Let's say the starting xml is: < d />

假设起始xml是：

Inserting:

插入：

UPDATE Table 
set XmlCol.modify('insert attribute att {"1"} into /d[1]')

Changing:

更改：

UPDATE Table
set XmlCol.modify('replace value of /d[1]/@att with "1"')

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'."

如果属性已存在，则insert将失败，如果该属性不存在，则replace将失败。我试图使用'if'，但我不认为它可以工作，我得到错误：“XQuery [modify（）]：'attribute'附近的语法错误，期望'else'。”

IF attempt

如果尝试

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

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.

目前我将xml选择为变量，然后使用T-SQL修改它，然后使用新的xml更新列，这要求我在事务中锁定行，并且对于DB来说可能更昂贵。

2 个解决方案

#1

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.

据我所知，你不能用单一陈述来做到这一点。您可以使用exist（）方法通过两个更新语句来完成此操作。

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;

The output from the final select is:

最终选择的输出是：

Id          XmlCol
----------- -------------------
1           <d att="1" />
2           <d att="1" />
3           <d att="1" />

#2

There is a slightly better way than Tommys:

有一个比Tommys更好的方法：

DECLARE @TestTable TABLE
(
    Id int,
    XmlCol xml
);

INSERT INTO @TestTable (Id, XmlCol)
VALUES
    (1, '<UserSettings> </UserSettings>'),
    (2, '<UserSettings><timeout>3</timeout> </UserSettings>'),
    (3, '<UserSettings> </UserSettings>');

UPDATE @TestTable
SET XmlCol.modify('replace value of (/UserSettings/timeout/text())[1] with "1"')
WHERE Id = 3 and XmlCol.exist('/UserSettings/timeout') = 1;
IF @@ROWCOUNT=0
    UPDATE @TestTable
    SET XmlCol.modify('insert <timeout>5</timeout> into (/UserSettings)[1] ')
    WHERE Id = 3;

SELECT * FROM @TestTable;

The solution is a combination of Tommys and simple SQL and required only 1 SQL UPDATE if the column exist. Tommys always recuire two updates.

该解决方案是Tommys和简单SQL的组合，如果列存在，则只需要1个SQL UPDATE。 Tommys总是要求两次更新。

#1