patternsqlModerate
XQuery doesn't update XML data
Viewed 0 times
xqueryupdatexmldoesndata
Problem
I am trying to run the script to update an XML column:
Change value of
Is it anything to do with typed and untyped XML?
UPDATE DataImpTable
SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"')
WHERE identifier=5
1234
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?
Solution
You'll need to declare the namespaces in the
Something like this:
In your original xml fragment, you have declared the following namespace that is never used:
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
Results (formatted for readability):
I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com
modify function.Something like this:
DECLARE @xml xml = N'
1234
';
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):
6789
I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com
Code Snippets
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);<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>Context
StackExchange Database Administrators Q#238013, answer score: 11
Revisions (0)
No revisions yet.