CODEWITHSUNDEEP
sqlsql-server-2008
Sri Reddy
Sri Reddy

Reputation: 7012

Convert image datatype into varchar in sql server 2008

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?

Upvotes: 26

Views: 121846

Answers (7)

Krishna Santosh Nidri
Krishna Santosh Nidri

Reputation: 440

This is what worked for me.

select cast(cast(imageVal as varbinary(max)) as varchar(max)) from table

Upvotes: 0

Cameron Castillo
Cameron Castillo

Reputation: 2832

What worked for me to convert IMAGE column is:

SELECT convert(varchar(max), cast(mypicture as varbinary(max)), 1)
FROM MyTable

Upvotes: 0

Ahmad Aghazadeh
Ahmad Aghazadeh

Reputation: 17131

Try this code:

Select  MASTER.dbo.Fn_varbintohexstr(tpcommanddetail) From TestPartnerDB.TP_RESULTS_RECORDS

Upvotes: 4

kristianp
kristianp

Reputation: 5895

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.

Upvotes: 33

NIrav Modi
NIrav Modi

Reputation: 6962

You can convert also like this

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

Upvotes: 8

SylvainL
SylvainL

Reputation: 3938

If the data has been stored in the image field as Unicode data, it won't work if you replace the line Select CONVERT(nvarchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS with Select CONVERT(varchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS.

It's very important that the first conversion from binary data to text be made with the right collation and size of characters: if the text is in Ascii, you must use varchar() and if the text is in Unicode, you must use nvarchar().

The second conversion from nvarchar(100) to varchar(100) looks useless to me.

The use of binary(100) instead of varbinary(100) looks also very suspicious to me.

Finally, if you get strange characters like 0x0002 then maybe this is why that this has been stored in an image field instead of a text field: this is a specially formated field where not all characters are text characters. However, as you didn't show us the result of printing the original field in binary (or more exactly, in hexadecimal) or of any of your results, it's impossible to say anything more.

I've just cooked up a few tests; with these, you should be able to understand what's happening:

select ascii ('A'), unicode(N'A');
select convert (binary(2), ascii('A')), convert (binary(2), unicode(N'A'));
--
declare @ab binary(10), @vab varbinary(10);
declare @nab binary(10), @vnab varbinary(10);
--
set @ab = convert (binary (10), 'AB');
set @vab = convert (varbinary (10), 'AB');
set @nab = convert (binary (10), N'AB');
set @vnab = convert (varbinary (10), N'AB');
--
select @ab, @vab, @nab, @vnab;
--
select convert(varchar(10), @ab) + '*', 
    convert(varchar(10), @vab) + '*', 
    convert(varchar(10), @nab) + '*', 
    convert(varchar(10), @vnab) + '*';
--
select len(convert(varchar(10), @ab)), 
    len(convert(varchar(10), @vab)), 
    len(convert(varchar(10), @nab)), 
    len(convert(varchar(10), @vnab));
--
select len(convert(varchar(10), @ab) + '*'), 
    len(convert(varchar(10), @vab) + '*'), 
    len(convert(varchar(10), @nab) + '*'), 
    len(convert(varchar(10), @vnab) + '*');
--
select convert(nvarchar(10), @ab) + '*', 
    convert(nvarchar(10), @vab) + '*', 
    convert(nvarchar(10), @nab) + '*', 
    convert(nvarchar(10), @vnab) + '*';
--
select len(convert(nvarchar(10), @ab)), 
    len(convert(nvarchar(10), @vab)), 
    len(convert(nvarchar(10), @nab)), 
    len(convert(nvarchar(10), @vnab));
--
select convert(varchar(10), convert(nvarchar(10), @ab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @vab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @nab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @vnab)) + '*';
--
select len(convert(varchar(10), convert(nvarchar(10), @ab))), 
    len(convert(varchar(10), convert(nvarchar(10), @vab))), 
    len(convert(varchar(10), convert(nvarchar(10), @nab))), 
    len(convert(varchar(10), convert(nvarchar(10), @vnab)));
--
select convert(nvarchar(10), @nab) for xml path('tr');
select convert(varchar(10), convert(nvarchar(10), @nab)) for xml path('tr');
select 'The Name' as td, '', convert(nvarchar(10), @nab) as td for xml path('tr');

Upvotes: 4

Black Light
Black Light

Reputation: 2404

My guess would be that the data stored in your image column is not 'normal' text - I would guess that it's some arbitrary data structure (hence the decision to use image rather than varchar) ?

I tried this without a problem:

    declare @data varchar(max)

    declare @fred table (d1 varchar(max), d2 xml, d3 image)

    set @data = 'here is some data'
    while (len(@data) < 200)    set @data = @data + ' ' + cast(rand() as varchar)

    insert into @fred (d1,d2,d3) values (@data,@data,@data)

    set @data = 'here is some more data'
    while (len(@data) < 200)    set @data = @data + ' ' + cast(rand() as varchar)

    insert into @fred (d1,d2,d3) values (@data,@data,@data)

    declare @xml xml
    set @xml = (select cast(cast(d3 as varbinary(max)) as varchar(max)) as 'td' from @fred FOR XML PATH('tr'), TYPE)

    select @xml

Upvotes: 2

Related Questions