June 4, 2012 at 5:01 am
Hello,
I am using SQL Server 2008 R2 and facing an issue to update XML attribute value. I have a requirement to update XML attribute value as per the maching data.
For example my sample XML is as below:
declare @p1 xml
set @p1=convert(xml,
'
<Data>
<Datas>
<Item Section="1" primaryKeyId="11" name="HD1">
<ExtraDetails>
<Column surname="dd" salary="10000" />
</ExtraDetails>
</Item>
<Item Section="2" primaryKeyId="12" name="HD2">
<ExtraDetails>
<Column surname="dd" salary="8000" />
</ExtraDetails>
</Item>
<Item Section="2" primaryKeyId="14" name="HD3">
<ExtraDetails>
<Column surname="dd" salary="8000" />
</ExtraDetails>
</Item>
</Datas>
</Data>
')
select @p1
I need to modify salary attribute value of primaryKeyId=12 & primaryKeyId=14 from 8000 to 12000. I know I need to use Modify method and use [replace value of ] syntax for the same. But don't know how to use it.
Can you please help me to resolve the issue or guide me for the same?
Thanks
June 4, 2012 at 8:50 am
set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("12")]/ExtraDetails/Column/@salary)[1] with 1200')
set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("14")]/ExtraDetails/Column/@salary)[1] with 1200')
_________________________________
seth delconte
http://sqlkeys.com
June 4, 2012 at 11:26 pm
seth delconte (6/4/2012)
set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("12")]/ExtraDetails/Column/@salary)[1] with 1200')set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("14")]/ExtraDetails/Column/@salary)[1] with 1200')
Thanks
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply