XQuery doesn't update XML data

Question

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?

Answer 1

You'll need to declare the namespaces in the modify function.

Something like this:

DECLARE @xml xml = N'<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>';
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):

<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <CentralData>
        <SMData>
            <CentralSDItem>
                <ControlData>6789</ControlData>
            </CentralSDItem>
        </SMData>
    </CentralData>
</SMObjInfo>

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

Answer 2

If you have the XML in table you should use UPDATE instead of SET and there you can put the namespace declaration outside the XML_DML expression using WITH XMLNAMESPACES.

with xmlnamespaces(default 'DataService/1.0.0.0')
update DataImpTable
set serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem
                                            /ControlData/text())[1] with "9876"')
where identifier=5