I have varying 'message' columns which is a varchar that should be an xml, but some of them may not be well-formed or valid. I am trying to weed out the rows that have a given input value to a node like this: Select * from messagelog where message like '%1234567%'
But when I filter those to try and lift another node (1234567) whos value I do not know, I come across the issue.
I've casting every entry to a xml wont work since like 1% of messages are not valid.
This code doesn't parse the varchar into xml, but returns a substring if it exists. However, I get a conversion error on the charindex = 0 case. Some MessageIds are these large varchars.
Is there anything that I'm missing here? Am I SOL for using SQL to parse not well-formed XML varchars?
select
case when CAST(charindex('<RelatesToMessageID>', message) as varchar(100)) = 0
then 1
else
substring(message, charindex('<RelatesToMessageID>', message)+20, charindex('</RelatesToMessageID>', message)-charindex('<RelatesToMessageID>', message)-20)
end
from messagelog
Conversion failed when converting the varchar value '959B91D824324108948261EC2A81CD92' to data type int.
Your CASE is returning both a VARCHAR and an INT. You should change your then 1 to then '1' so both parts of your CASE return a VARCHAR