Convert image datatype into varchar in sql server 2008

Question

We have TestPartner database in SQL Server. The descriptions of the bugs are stored in "image" datatype column. We need to write a query to display the data as html table. We have this query that reads the data from the respective tables to display the information as xml using For XML. But converting image datatype to varchar throws an exception: "FOR XML could not serialize the data for node 'TD' because it contains a character (0x0002) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.".

Query:

DECLARE @ResultsTable nvarchar(MAX) --Create the XML table with the query results SET @ResultsTable = N'<H3>QA Automation Tests Results Summary </H3>' + N'<table border="1">' + N'<tr><th>Test Name</th><th>Execution Date</th>' + N'<th>Check Name</th><th>Description</th></tr>' + CAST ( ( select distinct Name as TD, '', (Select CAST(CONVERT(nchar(100),CAST( TPCommandDetail AS BINARY(100) )) as VARCHAR(100)) ) as TD, '' FROM TestPartnerDB.TP_RESULTS_RECORDS FOR XML PATH('tr'), TYPE  ) AS nvarchar(max) ) + N'</table>'  SELECT @ResultsTable 

Surprisingly it works for some records and as soon as you bump the size to say 200. It throws error again. I also tried:

Select CONVERT(varchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS 

This returns weird characters for each row. Does anyone know how to get this thing to work?

Answer 1

The simple answer is

select cast(cast(my_column as varbinary(max)) as varchar(max)) as column_name from   my_table 

This converts the column to varchar format. nvarchar(max) might be better if you have unicode data.

Answer 2

You can convert also like this

convert (varchar(max) , convert (varbinary (max) , blob_data)), cast(cast(blob_data as binary) as varchar(max))