MSSQL导出表/视图中的数据为html文件

)

SELECT

[H1] = N''' + REPLACE(ISNULL(@title, @object_name), N'''', N'''''') + N''',

[table/@border] = 1,

[tr] = (

SELECT

[*] = field_name

FROM FIELD

FOR XML PATH(''th''), TYPE

),

[*] = (

SELECT ' + STUFF(@sql_body, 1, 2, N'') + N'

FROM DATA

FOR XML PATH(''tr''), TYPE

)

FOR XML PATH(''html''), TYPE;

';

-- 生成临时处理存储过程

EXEC sp_executesql

@sql;

-- 输出结果到文件 本文来自:http://www.52mvc.com

SET @cmd = N'bcp "EXEC ' + QUOTENAME(DB_NAME())

+ N'.' + @temp_proce_name

+ N'" queryout ' + QUOTENAME(@file_name, N'"')

+ N' /T /w';

DECLARE @tb_re TABLE(

col nvarchar(4000)

);

INSERT @tb_re

EXEC sys.xp_cmdshell @cmd;

DECLARE

@file_exist int;

EXEC master.sys.xp_fileexist @file_name, @file_exist OUTPUT;

-- 删除临时存储过程

IF OBJECT_ID(@temp_proce_name) IS NOT NULL

EXEC(N'DROP PROC ' + @temp_proce_name + N';');

-- 检查文件生成是否成功

IF @file_exist = 1

BEGIN

RAISERROR(N'object "%s" output to "%s" succeed.', 10, 1, @object_name, @file_name);

END

ELSE

BEGIN

SELECT * FROM @tb_re;

RAISERROR(N'some error occur when generate html file, please check output information', 16, 1);

RETURN -1;

END

GO