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?

1 Answer 1


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.

  • To clarify I understand the pattern in your first example- 1. Get a count of nodes meeting the criteria 2. Build an accumulator loop/reference count 3. On each iteration call modify() using the accumulator as the ordinal in an xpath expression Aug 21, 2015 at 18:10
  • @JeffSacksteder Yes, that is what the code does in the linked question. If you want to change multiple rows you want the get the max node count for one row, not the total row count for all rows. You can modify only one value at a time in the XML but you can modify multiple rows for each turn in the while loop. Aug 21, 2015 at 18:21
  • And since there's no real identity, ordinal counting it is. Aug 21, 2015 at 18:24
  • @JeffSacksteder depending on how you update the XML value you could exclude nodes in the xpath expression that is already changed so you can use the ordinal 1 for each iteration and break when @@rowcount = 0. In that case you don't need to know the count beforehand. Aug 21, 2015 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.