tsql - Converting varchar to XML and parsing the XML failing -

i trying design query search though audit logs in emr database. problem audit information stored in varchar column text description of happened (which don't care about) , other times contains valid xml (i know design flaw can't change because didn't create emr)

i created table valued function parses xml , returns data select statement fails execute because xml conversion in function fails. can't try/catch on conversion in function , can's call stored procedure function conversion try/catch either i'm not sure go this.

select top 1 * audit with(nolock) outer apply dbo.cus_getdeletedattachmentinfo(audit.audituid) detail

error xml parsing: line 1, character 136, illegal xml character

you have illegal character in markup need have catch potentially when trying convert xml. did this:

declare @text varchar(max);  select @text = '<root><stuff>&</stuff></root>'   begin try     select cast(@text xml) end try begin catch     select error_message()      select 'let''s account ampersand manually converting'      select cast(replace(@text, '&', '&amp;') xml) end catch 

you need account things ampersands , other characters not marked otherwise default behavior of cast or convert in sql server fail , state why there failure. xml needs markup special characters. there lists markup here (have not tried of these example):

http://rabbit.eng.miami.edu/info/htmlchars.html