Bug: MS SQL, XML column, Data tab fails on big xml in row

[expired user #9270]'s profile image [expired user #9270] posted 9 years ago in General Permalink
I have XML column in table and I added row with 3600 lines in that xml column. Then, when I refreshed Data tab to check Id of this row, I saw error window with SQL Error 6354. It's because refresh query contains cast of this xml to varchar(256).
ansgar's profile image ansgar posted 9 years ago Permalink
Ok, MSSQL. So, is it possible to cast XML columns to VARCHAR in some way? If not, I would change the relevant variable in HeidiSQL to false, so it does not attempt to cast. That casting makes sense for minimizing the data you are loading, but I know it can't be done in some cases.
[expired user #9270]'s profile image [expired user #9270] posted 9 years ago Permalink
Oh, yea, that may be one of that cases when cast can't be done.
When I click refresh button or "Data" tab, following query executed:
SELECT TOP 1000  "Id",  "FileName",  LEFT(CAST("Body" AS NVARCHAR(256)), 256),  "QueueDate",  "ProcessDate",  "Success" FROM "TestDB"."Express"."RouteImportQueue";

Body column's type is XML. And that query is ok, when Body contains less than 256 symbols, but if it contains more, query fails.
I'm not sure how it will affect on performance but if cast will be to nvarchar(max) against of nvarchar(256) it will work fine
Code modification/commit from ansgarbecker, 9 years ago, revision 9.3.0.4992
Support MSSQL's XML data type, and do not cast it to VARCHAR in data grid. See http://www.heidisql.com/forum.php?t=19372
ansgar's profile image ansgar posted 9 years ago Permalink
Should be fixed in r4992, which now supports MSSQL's XML data type, and does not cast that any longer.
[expired user #9270]'s profile image [expired user #9270] posted 9 years ago Permalink
Wow, thanks! It probably was the fastest fix after my report that I saw)

Please login to leave a reply, or register at first.