我在sql Server 2008中有一个表,它有一些列.其中一列是Xml格式
我想更新一些属性.
我想更新一些属性.
例如,我的Xml列的名称是XmlText,它在5个第一行中的值如下：
<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属性,如下所示：
<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实际上位于表中的不同行上.如果是这种情况,您可以使用它.
update YourTable set
XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]','int') = 30
使用表变量的工作示例.
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
