I have a table "table1" that has an XML column (by the name of VariableData) in it. I need to update one part of this VariableData XML column. The XML looks like the following:
I need to change the <txtfilename> from file1.pdf_file2.txt to be just file2.txt. Below is the UPDATE statement that I am attempting to use:
SET VariableData.modify = ('replace value of (/txtfilename) with "file2.txt"')
WHERE ProcessID = 123
and I am getting the following error:
Msg 8173, Level 15, State 1, Line 1
Incorrect syntax was used to invoke the xml data type method 'modify'.
I've been Googling this and trying to get the column updated different ways, but I am not having any luck. If anyone can point me in the right direction on this, I would greatly appreciate it.