Database XML columns containing invalid XML cause XMLException in Linqpad queries.

Ash

I really like the database querying features of Linqpad however I have hit a bit of problem. I'm trying to query a table with an XML data type column that contains both empty values and plain text instead of valid XML.

When I try to access this table in any way through LinqPad I receive a "Data at the root level is invalid." XMLException. SQL Management studio is able to display the column and its contents without any problems.

In Linqpad the following actions all throw this exception:
1. Writing linq queries that reference the table.
2. Right clicking on the table in the database explorer window and choosing one of the 'Take' options.
3. Querying a related table and then clicking on the "problem" table via a link in the dump view.

I believe Linq to SQL is being used as the data access layer and it could be a problem at that level. Is that correct?

Does anyone know of any workarounds that will allow me to access a table containing invalid XML columns in my queries?

Sorax

I've run into this issue before. It's a mistake that Sql Server allows whatever to be dumped into xml columns.

When you use an ORM, like Linq-to-Sql, it has a reasonable expectation of data being in the form it was told. Here you're running into the DataContext trying to parse invalid xml into a XElement. But you can get around this by creating a projection that calls ToString on the property:

SomeTable
.Select(a => new
{
    Xml = a.Xml.ToString(),
})
.Dump();

Ash

Thanks, that's handy to know for linq queries. I'd love to know why the creators of these database tables thought using an xml column to store non-xml data was a good idea!