Parses the XML dynamically, generates Excel, and then sends the message.

Directly paste the code!

DECLARE @CurrentServer NVARCHAR (100)
DECLARE @CurrentDatabase NVARCHAR (100)
DECLARE @CurrentLoginName NVARCHAR (100)
DECLARE @CurrentLoginPwd NVARCHAR (100)

-----Configuring the Database connection ("requires the user to set itself" ")
SET @CurrentServer = '. '
SET @CurrentDatabase = ' Test '
SET @CurrentLoginName = ' sa '
SET @CurrentLoginPwd = ' www123 '
-----

--Enable the xp_cmdshell component
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

----------turn on the regular validation component
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
----------

----------Reading report data
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<[email protected]
BEGIN
DECLARE @fileID NVARCHAR (+), @SENDINGTIME DATETIME, @REPORTNAME NVARCHAR (500)
SELECT @fileID =id, @SENDINGTIME =sendingtime, @REPORTNAME =name from #xmlReport WHERE [email protected]

IF @SENDINGTIME =convert (NVARCHAR (+), GETDATE (), 23)
BEGIN

Parse the SQL statement--------------------------the original XML
DECLARE @StrSql NVARCHAR (max)
DECLARE @str NVARCHAR (max)

---TXT file use single_clob
---XML file with Single_blob

-----Set the directory where the Excel file is stored ("requires user to set" ")
DECLARE @ Xlsfilepath NVARCHAR (+)
DECLARE @xlsPath NVARCHAR (+)
SET @xlsPath = ' d:\ '
DECLARE @xlsName NVARCHAR (500)
set @[email protected]+ '. xls '
set @[email protected][email protected]
------

--- ---read the XML file directory ("requires the user to set" ")
DECLARE @path NVARCHAR ()
DECLARE @filePath NVARCHAR (+)
DECLARE @fileName nvarchar (+)
Set @filePath = ' F:\projects\GroupProcess\Web site\upload\ '
Set @[email protected]+ '. Xml '
Set @[email protected][email protected]
------

------Determine if the XML file exists in the current directory based on the directory of the XML file, which exists as 1 , there is no @path for 0
Declare @result int
exec master.dbo.xp_fileexist, @result output
------

IF @result = 1--1 file exists 0 file does not exist
BEGIN
DECLARE @cmd NVARCHAR (2000)
Set @cmd = ' SELECT @str =cast (CAST (Bulkcolumn as XML) as NVARCHAR (max))
From OPENROWSET (BULK "[Email protected]+", single_blob) MyFile '
EXEC sp_executesql @cmd, N ' @str NVARCHAR (max) output ', @str output

IF @str <> '--the contents of the current XML document are not empty
BEGIN
SET @str =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 @[email protected] ('/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 @[email protected] ('/report[1]/customproperties[1]/customproperty[1]/value[1] ', ' varchar (MAX) ')
Select @moduleNameUpper = ' ' ' +upper (@moduleName) + '. '
Select @moduleNameLower = ' ' [email protected]+ '. '
--------------------------

--------------------------take out the data for the Where condition of SQL
DECLARE @paramvalues XML
Select @[email protected] ('/report/datasets/dataset/query/queryparameters ')

DECLARE @ItemTables TABLE (id INT IDENTITY (PRIMARY) key,fieldname NVARCHAR, 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<[email protected]
BEGIN
DECLARE @fieldname NVARCHAR (max), @datafield NVARCHAR (max)
SELECT @fieldname =fieldname, @datafield =datafield from @ItemTables WHERE [email protected]
SET @StrSql =replace (@StrSql, @fieldname, @datafield)
SET @[email protected]+1
END
------------------------take out the data for the Where condition of SQL

DECLARE @regexStr NVARCHAR (max) = ' [email protected]+ '. Dbo.regexreplace (' [email protected]+ '. Dbo.regexreplace (' [ Email protected]+ '. Dbo.regexreplace (' [email protected]+ '. Dbo.regexreplace ("Email protected]+", "[email] Protected]+ ', ', ', 1,0, ' [email protected]+ ', ' ', ' ', ' 1,0 ', ' ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', , 1,0) '
SET @regexStr = ' SELECT @StrSql = ' [email protected]

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

------------------------Querying Report headers
Select @[email protected] ('/report/body/reportitems/table/header/tablerows/tablerow/tablecells ')

SELECT IDENTITY (int,1,1) as ID, ' groupbyname ' as groupbyname,* to #temp from (
SELECT
RTRIM (LTRIM (replace (replace (T.c.value (' (Value/text ()) [1] ', ' NVARCHAR '), ' = ', '), ' "', '")) b
From @paramvalues. Nodes ('/tablecells/tablecell/reportitems/textbox ') as T (c)) a
------------------------Querying Report headers

------------------------Final export of Excel temporary tables
DECLARE @sql_str NVARCHAR (MAX)
DECLARE @sql_col NVARCHAR (MAX)
DECLARE @sql_where NVARCHAR (MAX)
DECLARE @tableName SYSNAME--line-to-go list
DECLARE @groupColumn SYSNAME--Group field
DECLARE @row2column SYSNAME--Row Variable column field
DECLARE @row2columnValue SYSNAME--field for row variable column values
SET @tableName = ' #temp '
SET @groupColumn = ' Groupbyname '
SET @row2column = ' id '
SET @row2columnValue = ' B '
SET @sql_where = ' where groupbyname = ' groupbyname '

--get columns that may exist from the row data
SET @sql_str = N '
SELECT @sql_col_out = ISNULL (@sql_col_out + "," "," ") + QUOTENAME ([' [email protected]+ '])
from [' [E-mail protected]+ '] ' [email protected]_where+ ' GROUP by [' [email protected]+ '] '
--print @sql_str
EXEC sp_executesql @sql_str, N ' @sql_col_out NVARCHAR (MAX) output ', @[email protected]_col output
--print @sql_col

SET @sql_str = N '
SELECT *,1 as sort into #temp1 from (
SELECT [' [Email protected]+ '],[' [email protected]+ '],[' [email protected]+ '] from [' [email protected]+ '] ' [email Protected]_where+ ') P PIVOT
(Max ([' [email protected]+ ']) for [' [E-mail protected]+ '] in (' + @sql_col + ')) As Pvt
ORDER by Pvt. [' [Email protected]+ '] '
--print (@sql_str)

EXEC (
‘
Use ' [Email protected]+ '

' [Email protected]_str+ '
ALTER TABLE #temp1 drop column Groupbyname

' [Email protected]+ '
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

---------------------Final export of Excel temporary tables

DECLARE @bcpStr NVARCHAR (500)
SET @bcpStr = ' bcp ' select * from ' [email protected]+ '. TestTable "Queryout" ' [email protected]+ ' "-c-w-S" ' [email protected]+ ' "-u" ' [email protected]+ ' "-P" ' [email protected]+ ‘"‘
EXEC Master. xp_cmdshell @bcpStr

EXEC (' use ' [email protected]+ '; DROP TABLE testtable ')
DROP TABLE #temp

-------------Send Email ("Requires user to set" ")
Use msdb
DECLARE @bodyContent NVARCHAR (max) = ' This ' [e-mail protected]+ ' report has been sent successfully. '
Exec dbo.sp_send_dbmail @profile_name = ' Linxianfeng ',
@recipients = ' [email protected] ',
@[email protected],
@[email protected],
@file_attachments = @xlsFilePath
-------------

--------Change the issended of report reports to 1, marked as sent messages
EXEC (' UPDATE ' [email protected]+ '. dbo. REPORTS SET issended=1 WHERE id= ' [email protected]+ ']

END
END
END

SET @[email protected]+1
END

DROP TABLE #xmlReport

-------Close the xp_cmdshell component
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
-------

-------close the regular validation component
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
-------

Regular functions

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,--the original string
@regexp varchar (8000),--Regular expression
@replace varchar (8000),--Replacement value
@globalReplace bit = 1,--whether it is a global substitution
@ignoreCase bit = 0-whether case is ignored
)
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

Parses the XML dynamically, generates Excel, and then sends the message.