Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
           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.

share|improve this question
up vote 5 down vote accepted

All output of the case expression must be of the same datatype.

If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:

cast('<?invalid ' + RunLog + ' ?>' as xml)

Also, you shouldn't need to use both try_cast and cast in the same expression.

coalesce(try_cast(RunLog as xml),cast('<?invalid ' + RunLog + ' ?>' as xml))
share|improve this answer
Perfect. Thanks for the idea of using a processing instruction! – Vaccano May 3 '13 at 19:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.