获取和更新具有特定属性的XML值

Question

我有一个SQL表，其中xml列的值是这样的，跨多行:

<array>
<foo>
<property name="Name">Foo 1</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-01</property>
</foo>
<foo>
<property name="Name">Foo 2</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-02</property>
</foo>
<foo>
<property name="Name">Foo 3</property>
<property name="Gender">F</property>
<property name="DOB">2020-01-03</property>
</foo>
</array>

我想写一个SQL语句，可以将所有出现的property["Name"]值更新为1(删除Foo)。

我一直在尝试SQL，如:

select xmlcolumn.value('/array/foo[@name="Name"]/', 'nvarchar(max)')
from xmltable

和该XML查询的一些变体，但没能弄清楚。结果应该是一个包含如下XML值的表(注意，对于所有出现的情况，带有Name属性的属性都被设置为1):

<array>
<foo>
<property name="Name">1</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-01</property>
</foo>
<foo>
<property name="Name">1</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-02</property>
</foo>
<foo>
<property name="Name">1</property>
<property name="Gender">F</property>
<property name="DOB">2020-01-03</property>
</foo>
</array>

Answer 1

遗憾的是，MS SQL Server的XQuery实现并不完全符合XQuery标准。这就是为什么XQuery.modify()方法一次只更新一次。

因此，我们可以使用XQuery.exist()方法在循环中更新XML，直到没有什么可更新的。

请为我的建议投票:https://feedback.azure.com/d365community/idea/153a9604-7025-ec11-b6e6-000d3a4f0da0

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<array>
<foo>
<property name="Name">Foo 1</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-01</property>
</foo>
<foo>
<property name="Name">Foo 2</property>
<property name="Gender">M</property>
<property name="DOB">2020-01-02</property>
</foo>
</array>'),
(N'<array>
<foo>
<property name="Name">Foo 3</property>
<property name="Gender">F</property>
<property name="DOB">2020-01-03</property>
</foo>
</array>');
-- DDL and sample data population, end
DECLARE @from VARCHAR(30) = '1'
, @to VARCHAR(30) = '1'
, @UPDATE_STATUS BIT = 1;
-- before
SELECT * FROM @tbl
WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;
WHILE @UPDATE_STATUS > 0
BEGIN
UPDATE t
SET xmldata.modify('replace value of (/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]/text())[1]
with (sql:variable("@to"))')
FROM @tbl AS t
WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;

SET @UPDATE_STATUS = @@ROWCOUNT;
PRINT @UPDATE_STATUS;
END;
-- after
SELECT * FROM @tbl;

Answer 2

正如@YitzhakKhabinsky所解释的，SQL Server不支持很多XQuery特性。

尽管有几种方法可以在不运行循环的情况下完成此操作。您基本上需要重新构建整个XML。

可以使用XQuery

UPDATE xmltable
SET xmlcolumn = xmlcolumn.query('
<array>
{
for $foo in array/foo
return
<foo>
{
let $prop := ($foo/property[@name="Name"])[1]
return
<property name="{$prop/@name}">
{ substring(($prop/text())[1], 5) }
</property>
}
{
$foo/property[@name != "Name"]
}
</foo>
}
</array>
')

,db&lt的在小提琴

或者你可以使用FOR XML

UPDATE xmltable
SET xmlcolumn = (
SELECT
(
SELECT
v.[@name],
CASE WHEN v.[@name] = 'Name'
THEN SUBSTRING(v.text, 5, LEN(v.text))
ELSE v.text
END [text()]
FROM x1.foo.nodes('property') x2(prop)
CROSS APPLY (SELECT
x2.prop.value('@name','nvarchar(1000)') [@name],
x2.prop.value('text()[1]','nvarchar(max)') text
) v
FOR XML PATH('property'), TYPE
)
FROM xmlcolumn.nodes('array/foo') x1(foo)
FOR XML PATH('foo'), ROOT('array'), TYPE
);

,db&lt的在小提琴