After some playing, I found a lot easier way to update my xml column.

The point of this is to update certain parts of my xml, to put a new node into all xml that doesnt have this node.

begin transaction

–replace the previous contents of the NTEXT column with the newly updated XML (converting back to text)
UPDATE my_table
SET form_data.modify(‘insert <my_node_cd_label>{(/some/path/in/xml/my_node_cd/text())} after (/some/path/in/xml/my_node_cd)[1]’)
WHERE form_number = ‘someCriteria’
AND form_data.exist(‘/some/path/in/xml/my_node_cd_label’) = 0
AND form_data.exist(‘/some/path/in/xml/my_node_cd’) = 1

SELECT form_data
FROM claim_filing
WHERE form_number = ‘ca7′
AND form_data.exist(‘/some/path/in/xml/my_node_cd_label’) = 1

— change rollback to commit to actually run this.
rollback transaction