XQuery doesn't update XML data 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


&lt;SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;CentralData&gt;
    &lt;SMData&gt;
      &lt;CentralSDItem&gt;
        &lt;ControlData&gt;1234&lt;/ControlData&gt;
      &lt;/CentralSDItem&gt;
    &lt;/SMData&gt;
  &lt;/CentralData&gt;
&lt;/SMObjInfo&gt;


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?
You'll need to declare the namespaces in the modify function.

Something like this:

DECLARE @xml xml = N'&lt;SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;CentralData&gt;
    &lt;SMData&gt;
      &lt;CentralSDItem&gt;
        &lt;ControlData&gt;1234&lt;/ControlData&gt;
      &lt;/CentralSDItem&gt;
    &lt;/SMData&gt;
  &lt;/CentralData&gt;
&lt;/SMObjInfo&gt;';
SET @xml.modify('
    declare default element namespace "DataService/1.0.0.0";
    replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1]
    with "6789"
    ');
PRINT CONVERT(nvarchar(max), @xml);


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):

&lt;SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;CentralData&gt;
        &lt;SMData&gt;
            &lt;CentralSDItem&gt;
                &lt;ControlData&gt;6789&lt;/ControlData&gt;
            &lt;/CentralSDItem&gt;
        &lt;/SMData&gt;
    &lt;/CentralData&gt;
&lt;/SMObjInfo&gt;


I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com