I want to do an "update" to a field of type xml in sql. Example: If I have a DummyColumn column of type xml in the DummyTable with the following xml:
<appSettings>
<add key="DummyKey1" value="10" />
<add key="DummyKey2" value="20" />
<add key="DummyKey3" value="30" />
<add key="DummyKey4" value="40" />
<appSettings>
How do I update the value of DummyKey4 to 4?
After several searches on google I have tried:
Update DummyTable
set DummyColumn.modify('replace value of (/appSettings/@DummyKey4)[1] with "1"')
It executes correctly but it does not change the value. The expected result would be:
<appSettings>
<add key="DummyKey1" value="10" />
<add key="DummyKey2" value="20" />
<add key="DummyKey3" value="30" />
<add key="DummyKey4" value="1" />
<appSettings>
But it's not working.
Try a syntax like this:
The solution is:
Xml data modification language