Update Single XML Node Value of XML Column using SQL Server

Question

I want to update single value of XML Node in SQl Server

Below is the table structure

XML Structure

<PayDetails>
  <Column Name="FG" DataType="float" Value="7241" />
  <Column Name="SKILL" DataType="float" Value="3" />
  <Column Name="PI" DataType="float" Value="87" />
  <Column Name="MD" DataType="float" Value="30" />
  <Column Name="LD" DataType="float" Value="4" />
  <Column Name="WEEKOFF_DAYS" DataType="float" Value="4" />   
  <Column Name="NETPAY" DataType="float" Value="5389" />    
</PayDetails>

I want to update value of FG from 7241 to 8000

Jesse Good · Accepted Answer · 2016-08-27 12:39:38Z

5

You want to use replace value of...with keywords:

Try something like the following:

update tablename
set TransactionFieldDetails.modify(
'replace value of
(/PayDetails/Column[@Name="FG"]/@Value)[1]
with "8000"');

answered Aug 27, 2016 at 12:39

Jesse Good

52.8k15 gold badges133 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pரதீப் Over a year ago

Good to know, never heard of it

Collectives™ on Stack Overflow

Update Single XML Node Value of XML Column using SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related