Trigger to replace strings in the Text field.

--**********************************************************
--This trigger will replace < and > substrings
--in the field [Data] of [myTable] table with '<' and '>'
--characters.
--It assumes that myTable has pimary key on the [ID] field
--Please replace this with corresponding names from your 
--table.
--**********************************************************

CREATE TRIGGER decodeXML ON [dbo].[myTable] 
AFTER INSERT
AS

DECLARE @ptrData binary(16)
DECLARE @pos Integer
DECLARE @id Integer

select @id=id from inserted

SELECT @ptrData = TEXTPTR(data) 
   FROM [myTable] where id = @id

--Replace < string with <
select @pos=PATINDEX('%<%', data)-1
     FROM [myTable] where id = @id

WHILE @pos >=0
BEGIN
UPDATETEXT [myTable].[data] @ptrData @pos 4 '<' 
select @pos=PATINDEX('%<%', data)-1
      FROM [myTable] where id = @id
END

--Replace > string with >
select @pos=PATINDEX('%>%', data)-1
     FROM [myTable] where id = @id

WHILE @pos >=0
BEGIN
UPDATETEXT [myTable].[data] @ptrData @pos 4 '>' 
select @pos=PATINDEX('%>%', data)-1
      FROM [myTable] where id = @id
END