如何在sql中更新xml字段表？

I have a XML column in a table that looks like this:

我在表中有一个XML列，如下所示：

<word A="al"   B="h"   C="Ps" />
<word A="has"  B="es"  C="Pf" /> 
<word A="mom"  B="es"  C="Ph" />

I want to update this field like this:

我想像这样更新这个字段：

<word A="al"   B="B1"   C="C1" /> 
<word A="has"  B="B2"  C="C1" /> 
<word A="mom"  B="B2"  C="C2" />

I want to do by a function in SQL Server.

我想通过SQL Server中的函数来完成。

Thanks!

谢谢！

2 个解决方案

#1

As Mikael states you need to describe the logic behind the update. But for the expected output above, the following should work:

正如Mikael所说，你需要描述更新背后的逻辑。但对于上面的预期输出，以下应该有效：

DECLARE @Words xml
SELECT @Words = '
<word A="al"   B="h"   C="Ps" />
<word A="has"  B="es"  C="Pf" />
<word A="mom"  B="es"  C="Ph" />'

SET @Words.modify('replace value of(/word[@A = "al"]/@B)[1] with "B1"')
SET @Words.modify('replace value of(/word[@A = "al"]/@C)[1] with "C1"')
SET @Words.modify('replace value of(/word[@A = "has"]/@B)[1] with "B2"')
SET @Words.modify('replace value of(/word[@A = "has"]/@C)[1] with "C1"')
SET @Words.modify('replace value of(/word[@A = "mom"]/@B)[1] with "B2"')
SET @Words.modify('replace value of(/word[@A = "mom"]/@C)[1] with "C2"')

SELECT @Words

#2

Please try the "replace value of" in your XQuery.
Ref: http://msdn.microsoft.com/en-us/library/ms190675.aspx

请尝试在XQuery中“替换值”。参考：http：//msdn.microsoft.com/en-us/library/ms190675.aspx

#1