在 sql server 2008 中将图像数据类型转换为 varchar

Convert image datatype into varchar in sql server 2008

提问by Sri Reddy

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.".

我们在 SQL Server 中有 TestPartner 数据库。错误的描述存储在“图像”数据类型列中。我们需要编写一个查询来将数据显示为 html 表。我们有这个查询，它从相应的表中读取数据，以使用 .xml 将信息显示为 xml For XML。但是将图像数据类型转换为 varchar 会引发异常：“FOR XML 无法序列化节点 'TD' 的数据，因为它包含 XML 中不允许的字符 (0x0002)。要使用 FOR XML 检索此数据，请将其转换为二进制、varbinary 或 image 数据类型并使用 BINARY BASE64 指令。”。

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:

令人惊讶的是，它适用于某些记录，并且一旦将大小增加到 200，它就会再次引发错误。我也试过：

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

This return weird character for each row. Can anyone know how to get this thing work.

这为每一行返回奇怪的字符。谁能知道如何让这件事发挥作用。

回答by kristianp

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.

这会将列转换为 varchar 格式。如果您有 unicode 数据， nvarchar(max) 可能会更好。

回答by NIrav Modi

You can convert also like this

你也可以这样转换

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

回答by Ahmad Aghazadeh

Try this code:

试试这个代码：

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

回答by SylvainL

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_RECORDSwith Select CONVERT(varchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS.

如果数据已被存储在图像领域为Unicode数据，也不会工作，如果你更换线Select CONVERT(nvarchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS带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().

使用正确的排序规则和字符大小进行从二进制数据到文本的第一次转换非常重要：如果文本是 Ascii，则必须使用 varchar()，如果文本是 Unicode，则必须使用 nvarchar() .

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

从 nvarchar(100) 到 varchar(100) 的第二次转换对我来说似乎没用。

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

使用 binary(100) 而不是 varbinary(100) 在我看来也很可疑。

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.

最后，如果你得到像 0x0002 这样的奇怪字符，那么这可能就是为什么它被存储在图像字段而不是文本字段中的原因：这是一个特殊格式的字段，其中并非所有字符都是文本字符。但是，由于您没有向我们展示以二进制（或更确切地说，以十六进制）或任何结果打印原始字段的结果，因此无法再多说。

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');

回答by Black Light

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)?

我的猜测是存储在图像列中的数据不是“普通”文本 - 我猜它是某种任意数据结构（因此决定使用图像而不是 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