I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need).
I am trying to make a view for this table that allows the value to display as XML (with the nice link feature that comes with SSMS) when the data is valid XML, but still shows the string when it is invalid XML.
Like this:
SELECT TRY_CAST([RunLog] AS xml) as [Log], -- works
RunLog, -- Also Works
-- This is the one I want, but it tries to parse all of them
-- (even the ones that fail the cast)
Case
When Try_Cast(RunLog as xml) is NULL
Then NettinRunLoggLog else CAST(RunLog AS xml)
end as CombinedLog
FROM LoggingTable
I think that this is a lost cause, because SQL Server will not allow a result to have more than one datatype. But I thought I would ask the experts just to be sure.