declare @str as varchar(max);
set @str = '[Name]''Вася''<br/>[Surname]''Топор''<br/>[Email]''vasyatheax@gmail.com''<br/>'

declare @str as varchar(max);
set @str = '[Name]''Вася''<br/>[Surname]''Топор''<br/>[Email]''vasyatheax@gmail.com''<br/>'
select b.b.query('./br[@t="Name"]').value('.','varchar(100)')   [Name]
      ,b.b.query('./br[@t="Surame"]').value('.','varchar(100)') [Surname]
      ,b.b.query('./br[@t="Email"]').value('.','varchar(100)')  [Email]
  from (select cast(replace(replace(replace(replace(@str,'[Name]','<br t="Name">'),'[Surname]','<br t="Surame">'),'[Email]','<br t="Email">'),'<br/>','</br>') as xml))a(a)
 cross apply a.a.nodes('.')b(b)

declare @str as varchar(max);
set @str = '[Name]''Вася''<br/>[Surname]''Топор''<br/>[Email]''vasyatheax@gmail.com''<br/>';

select
 t.n.value('@Name', 'varchar(100)'),
 t.n.value('@Surname', 'varchar(100)'),
 t.n.value('@Email', 'varchar(100)')
from
 (select cast('<a ' + replace(replace(replace(@str, '''<br/>', '" '), ']''', ' = "'), '[', '') + '/>' as xml)) a(x) cross apply
 a.x.nodes('a') t(n);