By S S


2019-05-13 12:49:52 8 Comments

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?

2 comments

@Max Vernon 2019-05-13 22:06:02

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

@Mikael Eriksson 2019-05-14 05:42:24

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

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Update XML metadata in SQL Server

1 Answered Questions

[SOLVED] Simplest way to edit single entry in XML column?

0 Answered Questions

why an update doesn't update a row if the where clause exists?

1 Answered Questions

[SOLVED] Trim a value in an xml column in MS SQL Server

2 Answered Questions

1 Answered Questions

3 Answered Questions

2 Answered Questions

[SOLVED] Query to search for a substring in xml

2 Answered Questions

[SOLVED] Fast XML, slow XML

4 Answered Questions

[SOLVED] Is it possible to get SQL Server 2008 to download a file from a URL

Sponsored Content