question

abhisheker avatar image
abhisheker asked

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.
xmlexceptioncast
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Only with a CLR function I would think.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

abhisheker avatar image abhisheker commented ·
Due to no clear design/strategy currently table has XML, HTML and strings data in the table. I have to do a clean up script and I need to identify all xml data. Is it possible to do that?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.