How to ignore exception when cast to xml from varchar
Hi, Using SqlServer 2008R2. I am trying to cast varchar data to xml so that I can query xml attributes. Is there a way to ignore exceptions as I am getting following error: ;with cteCastToXML as ( select id, subject, CAST(body as xml) as x from INFO ) select * from cteCastToXML Msg 9413, Level 16, State 1, Line 1 XML parsing: line 2, character 12, A string literal was expected The data causing the issue has invalid xml data.
It has to be valid XML to be `CAST`ed to XML datatype. Otherwise it is just a string of varchar data. You can't have it both ways - if you want to be able to query xml attributes, it will have to be xml data.