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_txt	VARCHAR(8000),
	 @xsl_file_nm	VARCHAR(255),
	 @out_file_nm	VARCHAR(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

Rate

Share

Share

Rate