动态解析xml，并生成excel，然后发邮件。
直接贴代码了！
DECLARE @CurrentServer NVARCHAR(100)
DECLARE @CurrentDatabase NVARCHAR(100)
DECLARE @CurrentLoginName NVARCHAR(100)
DECLARE @CurrentLoginPwd NVARCHAR(100)
-----配置数据库连接(【【需要用户自己设置】】)
SET @CurrentServer='.'
SET @CurrentDatabase='test'
SET @CurrentLoginName='sa'
SET @CurrentLoginPwd='www123'
-----
--启用xp_cmdshell组件
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
----------开启正则验证组件
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
----------
----------读取报表数据
SELECT IDENTITY(INT,1,1) AS id_index,ID,SENDINGTIME,NAME INTO #xmlReport FROM dbo.REPORTS WHERE SENDINGTIME IS NOT NULL
----------
DECLARE @xmlReportCount INT=(SELECT COUNT(*) FROM #xmlReport)
DECLARE @j INT=1
WHILE @j<=@xmlReportCount
BEGIN
DECLARE @fileID NVARCHAR(500),@SENDINGTIME DATETIME,@REPORTNAME NVARCHAR(500)
SELECT @fileID=ID,@SENDINGTIME=SENDINGTIME,@REPORTNAME=NAME FROM #xmlReport WHERE id_index=@j
IF @SENDINGTIME=CONVERT(NVARCHAR(500),GETDATE(),23)
BEGIN
--------------------------原来的xml解析出sql语句
DECLARE @StrSql NVARCHAR(max)
DECLARE @str NVARCHAR(max)
---txt文件用SINGLE_CLOB
---xml文件用SINGLE_BLOB
-----设置excel文件存放的目录(【【需要用户自己设置】】)
DECLARE @xlsFilePath NVARCHAR(2000)
DECLARE @xlsPath NVARCHAR(500)
SET @xlsPath='d:\'
DECLARE @xlsName NVARCHAR(500)
SET @xlsName=@fileID+'.xls'
SET @xlsFilePath=@xlsPath+@xlsName
------
------读取xml的文件目录(【【需要用户自己设置】】)
DECLARE @path NVARCHAR(2000)
DECLARE @filePath nvarchar(600)
DECLARE @fileName nvarchar(400)
SET @filePath='F:\projects\GroupProcess\Web Site\Upload\'
SET @fileName=@fileID+'.xml'
set @path=@filePath+@fileName
------
------根据xml文件的目录,判断当前目录下是否存在这个xml文件,存在为1,不存在为0
declare @result int
exec master.dbo.xp_fileexist @path,@result output
------
IF @result=1 --1文件存在 0文件不存在
BEGIN
DECLARE @cmd NVARCHAR(2000)
set @cmd='SELECT @str=CAST(CAST(BulkColumn AS XML) AS NVARCHAR(max))
FROM OPENROWSET (BULK '''+@path+''', SINGLE_BLOB) MyFile'
exec sp_executesql @cmd,N'@str NVARCHAR(max) output',@str OUTPUT
IF @str<>'' --当前的xml文档的内容不为空
BEGIN
SET @str=REPLACE(REPLACE(REPLACE(@str,'xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"',''),'xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"',''),'rd:','')
declare @XMLVar xml =CAST(REPLACE(@str,'encoding="UTF-8"','') AS XML)
select @StrSql=@XMLVar.value('/Report[1]/DataSets[1]/DataSet[1]/Query[1]/CommandText[1]','varchar(MAX)')
DECLARE @moduleName NVARCHAR(500)
DECLARE @moduleNameUpper NVARCHAR(500)
DECLARE @moduleNameLower NVARCHAR(500)
SELECT @moduleName=@XMLVar.value('/Report[1]/CustomProperties[1]/CustomProperty[1]/Value[1]','varchar(MAX)')
SELECT @moduleNameUpper='"'+UPPER(@moduleName)+'.'
SELECT @moduleNameLower='"'+@moduleName+'.'
--------------------------
--------------------------将sql的where条件的数据取出来
DECLARE @paramvalues XML
select @paramvalues=@XMLVar.query('/Report/DataSets/DataSet/Query/QueryParameters')
DECLARE @ItemTables TABLE(id INT IDENTITY(1,1) PRIMARY KEY,fieldname NVARCHAR(300),datafield NVARCHAR(300))
INSERT INTO @ItemTables
(
fieldname,
datafield
)
SELECT
T.c.value('@Name','NVARCHAR(300)'),
RTRIM(LTRIM(REPLACE(REPLACE(T.c.value('(Value/text())[1]','NVARCHAR(300)'),'=',''),'"','''')))
FROM @paramvalues.nodes('/QueryParameters/QueryParameter') AS T(c)
DECLARE @rows INT=(SELECT COUNT(*) FROM @ItemTables)
DECLARE @i INT=1
WHILE @i<=@rows
BEGIN
DECLARE @fieldname NVARCHAR(max),@datafield NVARCHAR(max)
SELECT @fieldname=fieldname,@datafield=datafield FROM @ItemTables WHERE id=@i
SET @StrSql=REPLACE(@StrSql,@fieldname,@datafield)
SET @i=@i+1
END
------------------------将sql的where条件的数据取出来
DECLARE @regexStr NVARCHAR(max)=''+@CurrentDatabase+'.dbo.regexReplace('+@CurrentDatabase+'.dbo.regexReplace('+@CurrentDatabase+'.dbo.regexReplace('+@CurrentDatabase+'.dbo.regexReplace('''+@StrSql+''','''+@moduleNameLower+''','''',1,0),'''+@moduleNameUpper+''','''',1,0),''"'','''',1,0),''from'','',2 as sort into #temps from'',1,0)'
SET @regexStr='SELECT @StrSql='+@regexStr
--SELECT @StrSql=dbo.regexReplace(dbo.regexReplace(dbo.regexReplace(dbo.regexReplace(@StrSql,@moduleNameLower,'',1,0),@moduleNameUpper,'',1,0),'"','',1,0),'from',',2 as sort into #temps from',1,0)
EXEC sp_executesql @regexStr,N'@StrSql NVARCHAR(MAX) OUTPUT',@StrSql OUTPUT
------------------------查询报表表头
select @paramvalues=@XMLVar.query('/Report/Body/ReportItems/Table/Header/TableRows/TableRow/TableCells')
SELECT IDENTITY(INT,1,1) AS id,'groupByName' AS groupByName,* INTO #temp FROM (
SELECT
RTRIM(LTRIM(REPLACE(REPLACE(T.c.value('(Value/text())[1]','NVARCHAR(300)'),'=',''),'"',''''))) b
FROM @paramvalues.nodes('/TableCells/TableCell/ReportItems/Textbox') AS T(c)) a
------------------------查询报表表头
------------------------最终导出excel的临时表
DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @sql_where NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = '#temp'
SET @groupColumn = 'groupByName'
SET @row2column = 'id'
SET @row2columnValue = 'b'
SET @sql_where = 'WHERE groupByName = ''groupByName'''
--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col
SET @sql_str = N'
SELECT *,1 as sort into #temp1 FROM (
SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']'+@sql_where+') p PIVOT
(max(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.['+@groupColumn+']'
--PRINT (@sql_str)
EXEC (
'
use '+@CurrentDatabase+'
'+@sql_str+'
alter table #temp1 drop column groupByName
'+@StrSql+'
insert into #temp1 select * from #temps
select * into testTable from #temp1 order by sort asc
alter table testTable drop column sort
drop table #temps
'
)
SET @sql_col=NULL
---------------------最终导出excel的临时表
DECLARE @bcpStr NVARCHAR(500)
SET @bcpStr='bcp "select * from '+@CurrentDatabase+'..testTable" queryout "'+@xlsFilePath+'" -c -w -S"'+@CurrentServer+'" -U"'+@CurrentLoginName+'" -P"'+@CurrentLoginPwd+'"'
EXEC master..xp_cmdshell @bcpStr
exec(' use '+@CurrentDatabase+'; DROP TABLE testTable')
DROP TABLE #temp
-------------send email(【【需要用户自己设置】】)
use msdb
DECLARE @bodyContent NVARCHAR(max)='This '+@REPORTNAME+' report has been sent successfully.'
Exec dbo.sp_send_dbmail @profile_name='linxianfeng',
@recipients='850519458@qq.com',
@subject=@REPORTNAME,
@body=@bodyContent,
@file_attachments = @xlsFilePath
-------------
--------更改report报表的ISSENDED为1，标记为已发送过邮件
EXEC('UPDATE '+@CurrentDatabase+'.dbo.REPORTS SET ISSENDED=1 WHERE ID='''+@fileID+'''')
END
END
END
SET @j=@j+1
END
DROP TABLE #xmlReport
-------关闭xp_cmdshell组件
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
-------
-------关闭正则验证组件
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
-------
正则函数
USE [test]
GO
/****** Object: UserDefinedFunction [dbo].[regexReplace] Script Date: 08/13/2015 11:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[regexReplace]
(
@source ntext, --原字符串
@regexp varchar(8000), --正则表达式
@replace varchar(8000), --替换值
@globalReplace bit = 1, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(8000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(8000)
exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
end
return @result
end
GO
