Menu
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?
359
The IMAGE data type in SQL Server has been used to store the image files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS SQL Server.
SQL Server allows storing files. In this article, we learned how to insert a single image file into a SQL Server table using T-SQL. We also learned how to copy multiple files from a folder into SQL Server in a table. Finally, we worked in SQL Server Reporting Services to view the images inserted.
How to view images stored in your database Start SQL Image Viewer and connect to your database. For SQL Server databases, tables containing blob columns will be highlighted in green in the list of database objects. Write the query to retrieve your images, and execute the query.
The CAST() function converts a value (of any type) into a specified datatype.
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.
116
You can convert also like this
convert (varchar(max) , convert (varbinary (max) , blob_data)), cast(cast(blob_data as binary) as varchar(max)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
