Process XML against XSL template and save output
Given SQL text (@sql_txt) that will return XML data, run that XML data against an XSL template (@xsl_file_nm), and save the output to the specified outputfile (@out_file_nm). If @xsl_file_nm is not specified, then output is just saved as XML.
This script is very usefull, but not secure. One could easily overload any of the script input parameters and compromise system security. you should'nt give anyone that does not normally have access to sp_OACreate access to this script.
The script is prefixed by sp_ so that we may place it master database and give certain of our "trusted" users access to it (regardless of database).
Feel free to change, but don't forget to mention its original author.
CREATE PROCEDURE sp_MDDMGRunXMLAgainstXSL
@sql_txtVARCHAR(8000),
@xsl_file_nmVARCHAR(255),
@out_file_nmVARCHAR(255)
AS
-- ***************************************************************************
-- * File: sp_MDDMGRunXMLAgainstXSL
-- * Author: Marc Brazeau
-- * Date: 2004/09/15
-- * Purpose: Given SQL text that will return XML data, run that XML
-- * data against an XSL, and save the output to the specified
-- * file.
-- * Reviewed By:
-- * Review Date:
-- * Revisions: 2004/09/15 -- Marc Brazeau
-- * Initial Release
-- * ------------------------------------------------------------
-- * Notes:
-- ***************************************************************************\
DECLARE @VBScript VARCHAR(8000)
DECLARE @con_str VARCHAR(255)
DECLARE @oScriptCOntrol INTEGER
DECLARE @result INTEGER
IF @xsl_file_nm IS NULL SET @xsl_file_nm = ''
SET @VBScript = '
Set oStrm = CreateObject("ADODB.Stream")
With oStrm
.LineSeparator = -1 ''adCRLF
.Mode = 1 ''adModeRead
.Type = 2 ''adTypeText
.Open
End With
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = "{@con_str}"
.CommandText = "{@sql_txt}"
.CommandType = 1 ''adCmdText
.Properties.Item("Output Stream").Value = oStrm
.Properties("Output Encoding").Value = "UTF-16"
.Properties("XML Root").Value = "XMLROOT"
.Properties("XSL").Value = "{@xsl_file_nm}"
Call .Execute(,,1024) ''AdExecuteStream
End With
Call oStrm.SaveToFile("{@out_file_nm}", 2) ''adSaveCreateOverWrite)
oStrm.Close
Set oStrm = Nothing
Set oCmd = Nothing
'
SET @con_str = 'Provider=SQLOLEDB;Server=' + @@SERVERNAME + ';Database=' + db_name() + ';Trusted_Connection=yes'
SET @VBScript = replace(@VBScript, '{@con_str}', @con_str)
SET @sql_txt = REPLACE(@sql_txt, CHAR(13), ' ')
SET @sql_txt = REPLACE(@sql_txt, CHAR(10), ' ')
SET @VBScript = replace(@VBScript, '{@sql_txt}', @sql_txt)
SET @VBScript = replace(@VBScript, '{@xsl_file_nm}', @xsl_file_nm)
SET @VBScript = replace(@VBScript, '{@out_file_nm}', @out_file_nm)
EXEC @result = sp_OACreate 'MSScriptControl.ScriptControl', @oScriptCOntrol OUTPUT
IF @result <> 0 EXEC sp_displayoaerrorinfo @oScriptCOntrol, @result
EXEC @result = sp_OASetProperty @oScriptCOntrol, 'Language', 'VBScript'
IF @result <> 0 EXEC sp_displayoaerrorinfo @oScriptCOntrol, @result
EXEC @result = sp_OAMethod @oScriptCOntrol, 'ExecuteStatement', NULL, @VBScript
IF @result <> 0 EXEC sp_displayoaerrorinfo @oScriptCOntrol, @result
EXEC sp_OADestroy @oScriptCOntrol 
