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

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数据库。错误的描述存储在“image”数据类型列中。我们需要编写一个查询来将数据显示为html表。我们有这个查询从相应的表中读取数据，以使用For XML将信息显示为xml。但是将image数据类型转换为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.

这会为每一行返回奇怪的字符。任何人都可以知道如何使这件事工作。

5 个解决方案

#1

You can convert also like this

你也可以像这样转换

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

#2

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）可能会更好。

#3

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.

如果数据已作为Unicode数据存储在图像字段中，则替换行选择CONVERT（nvarchar（1000），convert（varbinary（1000），tpcommanddetail））来自TestPartnerDB.TP_RESULTS_RECORDS并选择CONVERT（ varchar（1000），convert（varbinary（1000），tpcommanddetail））来自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.

二进制（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');

#4

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

#5

Try this code:

试试这段代码：

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

#1