sql server - Update Value of all XML tags in T-SQL -

is there way replace value of multiple occurrences of tag in xml in sql server?

i need check specific tag (firstname) , replace of values another.

here sample xml:

<root>   <patient>     <firstname>something</firstname>     <middleinitial>w</middleinitial>     <lastname>west</lastname>   </patient>   <patient>     <firstname>another</firstname>     <middleinitial>e</middleinitial>     <lastname>east</lastname>   </patient> </root> 

my desired output is:

<root>   <patient>     <firstname>test</firstname>     <middleinitial>w</middleinitial>     <lastname>west</lastname>   </patient>   <patient>     <firstname>test</firstname>     <middleinitial>e</middleinitial>     <lastname>east</lastname>   </patient> </root> 

i have modify statement change first occurrence ignores next 'firstname' tag comes up:

set @x.modify('   replace value of     (/root/patient/firstname[1]/text())[1]   "test" '); 

which gives me this:

<root>   <patient>     <firstname>test</firstname>     <middleinitial>w</middleinitial>     <lastname>west</lastname>   </patient>   <patient>     <firstname>another</firstname>     <middleinitial>e</middleinitial>     <lastname>east</lastname>   </patient> </root> 

i tried throwing while loop, execute when path exists looks created infinite loop:

while @x.exist('(/root/patient/firstname[1]/text())[1]') = 1 

any thoughts on getting around appreciated!

i'm sure there's better way 1 approach be

while @x.exist('(/root/patient/firstname[text() != "test"])') = 1 set @x.modify('   replace value of      (/root/patient/firstname[text() != "test"][1]/text())[1]   "test" ');