I am trying to run the script to update an XML column:
UPDATE DataImpTable
SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"')
WHERE identifier=5
<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<CentralData>
<SMData>
<CentralSDItem>
<ControlData>1234</ControlData>
</CentralSDItem>
</SMData>
</CentralData>
</SMObjInfo>
Change value of ControlData
to 9876 but the value doesn't seem to change in the XML for the /SMObjInfo/CentralData/SMData/CentralSDItem/ControlData
value.
Is it anything to do with typed and untyped XML?
Best Answer
You'll need to declare the namespaces in the
modify
function.Something like this:
In your original xml fragment, you have declared the following namespace that is never used:
xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
If your actual xml documents do make use of this namespace, and you want to modify those elements, you'd need to add the following declaration into the
@xml.modify
function:declare namespace i="http://www.w3.org/2001/XMLSchema-instance";
Results (formatted for readability):
I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com