create table #tmp(id bigint primary key clustered, x xml)

insert into #tmp(id, x)
values
(
5708960193365165161,
'<collaborator SPXML-FORM="x-local://wtv/wtv_collaborator.xmd">
<id>5708960193365165161</id>
<lastname>Фамилия1</lastname>
<firstname>Имя1</firstname>
<middlename>Отчество1</middlename>
<email>фио1@mail.ru</email>
<login>login</login></collaborator>'),
(
5708960193365165162,
'<collaborator SPXML-FORM="x-local://wtv/wtv_collaborator.xmd">
<id>5708960193365165162</id>
<lastname>Фамилия2</lastname>
<firstname>Имя2</firstname>
<middlename>Отчество2</middlename>
<email>фио2@mail.ru</email>
<login>login</login></collaborator>'),
(5708960193365165163,
'<collaborator SPXML-FORM="x-local://wtv/wtv_collaborator.xmd">
<id>5708960193365165163</id>
<lastname>Фамилия3</lastname>
<firstname>Имя3</firstname>
<middlename>Отчество3</middlename>
<email>фио3@mail.ru</email>
<login>login</login></collaborator>')

select * from #tmp

UPDATE #tmp
SET x.modify('
    replace value of(/collaborator/email/text())[1]
    with "НовоеИмя@mail.ru"')
where x.exist('/collaborator[id="5708960193365165162"]') = 1

select * from #tmp