How to update a table based on an XML parameter

[mheritani]

I have a table and I want to update one of its varchar fields based on the values in an XML parameter. I have the following table:\[code\]ID Constraint_Value1 (OldVal_1) (OldVal_2)2 (OldVal_2) (OldVal_1)\[/code\]and I want to use the following XML to update the \[code\]Constraint_Value\[/code\] field:\[code\]<qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate></qaUpdates>\[/code\]After the update, I am aiming for the following:\[code\]ID Constraint_Value1 (NewVal_1) (NewVal_2)2 (NewVal_2) (NewVal_1)\[/code\]The following SQL illustrates my problem (which you can run in SQL Management Studio without any set up) :\[code\]IF OBJECT_ID('tempdb..#tmpConstraint') IS NOT NULL DROP TABLE #tmpConstraintGO CREATE TABLE tempdb..#tmpConstraint ( constraint_id INT PRIMARY KEY, constraint_value varchar(256) )GOinsert into #tmpConstraintvalues (1, '(OldVal_1) (OldVal_2)')insert into #tmpConstraintvalues (2, '(OldVal_2) (OldVal_1)')select * from #tmpConstraintdeclare @myXML XMLset @myXML = N'<qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate></qaUpdates>'update cset constraint_value = http://stackoverflow.com/questions/10836092/REPLACE(constraint_value, Child.value('(old)[1]', 'varchar(50)'), Child.value('(new)[1]', 'varchar(50)'))from #tmpConstraint ccross join @myXML.nodes('/qaUpdates/qaUpdate') as N(Child) select * from #tmpConstraint\[/code\]This gives the results:\[code\](Before)1 (OldVal_1) (OldVal_2)2 (OldVal_2) (OldVal_1)(After)1 (NewVal_1) (OldVal_2)2 (OldVal_2) (NewVal_1)\[/code\]As you can see just \[code\]OldVal_1\[/code\] has been updated. \[code\]OldVal_2\[/code\] has remained the same.How do I update the field with all the elements specified in the xml parameter?