使用 SQL Server 2008 表中的新值更新 Xml 属性 [英] Updating Xml attributes with new values in a SQL Server 2008 table

问题描述

我在 SQL Server 2008 中有一个表，它有一些列.这些列之一是 Xml 格式我想更新一些属性.

I have a table in SQL Server 2008 that it has some columns. One of these columns is in Xml format and I want to update some attributes.

例如我的 Xml 列的名称是 XmlText 并且它在前 5 行中的值是这样的:

For example my Xml column's name is XmlText and it's value in 5 first rows is such as:

 <Identification Name="John"  Family="Brown"     Age="30" /> 
 <Identification Name="Smith" Family="Johnson"   Age="35" /> 
 <Identification Name="Jessy" Family="Albert"    Age="60" />
 <Identification Name="Mike"  Family="Brown"     Age="23" />
 <Identification Name="Sarah" Family="Johnson"   Age="30" />

并且我想更改所有从 30 到 40 的 Age 属性，如下所示:

and I want to change all Age attributes that are 30 to 40 such as below:

 <Identification Name="John"  Family="Brown"     Age="40" /> 
 <Identification Name="Smith" Family="Johnson"   Age="35" /> 
 <Identification Name="Jessy" Family="Albert"    Age="60" />
 <Identification Name="Mike"  Family="Brown"     Age="23" />
 <Identification Name="Sarah" Family="Johnson"   Age="40" />

推荐答案

从您问题的早期版本来看，您的 XML 实际上位于表中的不同行上.如果是这种情况，您可以使用它.

From the early versions of your question it looks like your XML actually is on different rows in a table. If that is the case you can use this.

update YourTable set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

使用表变量的工作示例.

Working sample using a table variable.

declare @T table(XMLText xml)

insert into @T values('<Identification Name="John"  Family="Brown"   Age="30" />')
insert into @T values('<Identification Name="Smith" Family="Johnson" Age="35" />') 
insert into @T values('<Identification Name="Jessy" Family="Albert"  Age="60" />')
insert into @T values('<Identification Name="Mike"  Family="Brown"   Age="23" />')
insert into @T values('<Identification Name="Sarah" Family="Johnson" Age="30" />')

update @T set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

select *
from @T 

这篇关于使用 SQL Server 2008 表中的新值更新 Xml 属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！