I have a process step where I have an XML document in a SQL Server column and I need to alter the text of a node that could occur multiple times. Modify() with 'replace' DML only operates on single nodes. To complicate matters, the replacement needs to happen based on a lookup table maintained locally.
I can use nodes() to get a nodelist and contrive a query to join the data appropriately, but at that point, I no longer have a reference that modify() can act on.
It appears that my only choice is to create a CLR procedure or function and use some sort of dotnet native XML iteration. That's less appealing, since the business logic would be locked up in the source code, not visible in the procedure definition. Is there any way to do this transformation using the XML methods exclusively?
Best Answer
Three options come to mind.
Use a while loop and modify one node at a time
Shred the XML using nodes(), modify the values and then rebuild using for xml.
Cast the XML to a string and use replace().
Whatever works best in your case depends on the shape of the XML and what kind of modification you need to do. If you add the table structures with sample data and expected output to the question you might get a better answer.