create table tb_student (studentno varchar(5),studentname varchar(30),photos image null)insert into tb_student (studentno,studentname)
select '0001','张三'
union all
select '0002','李四'
union all
select '0003','王五'
有一个表 tb_student (studentno varchar(5),studentname varchar(30),photos image null)
三个字段,我想形成XML文件select space(2)+'<record>' +
(case when studentno is null then '' else space(2)+'<studentno>'+isnull(cast(studentno as varchar(8000)),'')+'</studentno>' end)+
(case when studentname is null then '' else space(2)+'<studentname>'+isnull(cast(studentname as varchar(8000)),'')+'</studentname>' end)
+(case when photos is null then '' else space(2)+'<photos >'+isnull(cast(photos as varchar(8000)),'')+'</photos >' end)
+space(2)+'</record>'
from tb_student 提示消息 529,级别 16,状态 2,第 1 行
不允许从数据类型 image 到 varchar 的显式转换。
SELECT studentno, studentname, CAST(ISNULL(photos, 'images') AS VARBINARY) AS photos
FROM tb_student FOR XML PATH('record')
/* <record>
<studentno>0001</studentno>
<studentname>张三</studentname>
<photos>aW1hZ2Vz</photos>
</record>
<record>
<studentno>0002</studentno>
<studentname>李四</studentname>
<photos>aW1hZ2Vz</photos>
</record>
<record>
<studentno>0003</studentno>
<studentname>王五</studentname>
<photos>aW1hZ2Vz</photos>
</record>
* */
--try
cast(cast(imageCol as varbinary(max))as varchar(max))
IF OBJECT_ID('x') IS NOT NULL
DROP TABLE x
GO
CREATE TABLE x
(
imageCol IMAGE
)
DECLARE @string VARCHAR(MAX)
SET @string = '别人叫我阿汤哥'
INSERT INTO x
SELECT @stringSELECT CAST(CAST(imageCol AS VARBINARY(MAX))AS VARCHAR(MAX))
FROM x
/*(无列名)
别人叫我阿汤哥
*/