有时直接从数据库内部发送信件非常方便,例如,有关某些操作成功/失败的通知,有关系统状态的信息,用户操作日志等。看起来像是野蛮人,一辆怪异的自行车,歪斜的解决方案等-但请想象一下。使用此方法的字母内容必须以纯文本形式形成,并且邮件要么通过xp_sendmail发送,要么(更灵活地)通过COM邮件对象(例如CDO.Message)发送,并通过SQL包装器实例化和管理它以与OLE sp_OAxxxx一起使用。只要您有足够的表达方式来绘制文本,它们都可以工作,数据的层次结构几乎为零,并且报告仅由老式技术文凭使用,+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
如果相似的格式开始变得紧张起来,并且在服务器上注册了组件,或者从数据库级别“涌现”到应用程序级别以发送更漂亮的内容,该怎么办,我真的不想:通常,仍然需要对字母进行格式化(例如,如果发送了一部分审计表),并且至少以最小的(公司)设计以HTML格式发送-并且这里不愉快的例程已经开始-使用T-SQL逐行生成HTML非常繁琐,特别是如果有几种不同类型的邮件报告,则报告结构是分层的,设计是通用的,并且其中的CSS正在扩展。在应用程序级别的世界中,二十年前就发明了一个惊人的XML / XSL偶极子,它是如此连贯,以至于从MSDN读取示例到编写其自己的模板元素之间的时间几乎没有记住其最后一个组件的语法描述。好吧,好的-最后,层次结构信息也更方便地以XML表示,而MS SQL在过去15年中表现得很好。因此,漂亮的字母只是一箭之遥-剩下的就是寻找一种方法来向这辆自行车添加XSL转换。不幸的是,开箱即用,MS SQL无法执行此操作,因此我们的自行车将包含另一个OLE轮。在内存和Internet中翻遍后,我们回想起我们拥有Microsoft.XMLDOM,它可以通过调用transformNode方法来穿越刺猬和蛇,看来我们的任务是正确安装所有内容,获取结果,处理可能的错误并修复所有内容,以便不要流动内存。我们走在正确的道路上,sp_OACreate,sp_OAMethod,sp_OAGet / SetProperty和sp_OADestroy将为我们提供帮助,但是有一个小细节-如果该方法返回一个指针,那么一切都很好,但是如果一个标量全部是不好的,因为字符串标量不能超过8kB的基本常数。有了这个限制,我们的例子将永远具有教育意义-因为在我们这个世纪中产生的四(8)个千个字符的文档令人发笑,仅此而已。我们经历了一个漫长的迷宫,离出口仅一步之遥,已经看到了阳光,但是很烂! -用炉排关闭输出。无法从OLE包装器中获取长度超过8K的字符串。尝试将(n)varchar(MAX)的类型指定为接收参数会导致一些听不见的OLE错误。我们知道,某种程度上我们需要返回的不是标量,而是返回结果的指针(因为指向SQL的指针只是一个数字)。但是不可能强制transformNode方法返回指针。绝望之后,我们进入了MSDN(或任何在线),我们发现XMLDOM在过去15年中得到了发展-MS现在提供了transformNodeToObject方法-BINGO!该方法获取一个指向将文档内容倒入其中的流的指针!然后很简单-我们创建一个流对象,将其传递给方法,告诉它将文档倒入流中。结果-与我们一起流-一个文档。如何将其拉成标量?还可以轻松地用不超过8KB的小块圆圈将海捞出,并将结果成块粘贴到varchar(MAX)中。不要忘记,在减去所有内容之前,您需要a)从流中读取其当前大小-这是我们需要从中获取的字符数,b)将指针放在读取流的开头。否则,将没有任何内容可供阅读。好吧,毕竟,您需要自己仔细清理。希望所有创建的东西都被销毁,是否是一个错误,如果是,那么什么阶段发生了。结果如下:CREATE FUNCTION [dbo].[f_Helper_XSLTransform]
(
@XMLData XML,
@XSLTemplate XML
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF @XMLData IS NULL OR @XSLTemplate IS NULL
RETURN 'XML or XSL data is NULL'
DECLARE @OLEActionName VARCHAR(128),
@PropOrMethodName VARCHAR(128)
DECLARE @hr INT,
@dummy INT,
@ObjXML INT,
@ObjXSL INT,
@ObjStream INT,
@BuffSize INT
DECLARE @Result NVARCHAR(MAX) = N'',
@Chunk NVARCHAR(4000),
@ChunkCharSize INT = 4000,
@ResultBuff NVARCHAR(MAX) = N''
SET @OLEActionName = 'sp_OACreate'
SET @PropOrMethodName = 'Microsoft.XMLDOM'
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXML OUT
IF @hr <> 0
GOTO FUN_ERROR
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXSL OUT
IF @hr <> 0
GOTO FUN_ERROR
SET @PropOrMethodName = 'ADODB.Stream'
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjStream OUT
IF @hr <> 0
GOTO FUN_ERROR
SET @OLEActionName = 'sp_OAMethod'
SET @PropOrMethodName = 'LoadXML'
EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, @dummy OUT, @XMLData
IF @hr <> 0
GOTO FUN_ERROR
EXEC @hr = sp_OAMethod @ObjXSL, @PropOrMethodName, @dummy OUT, @XSLTemplate
IF @hr <> 0
GOTO FUN_ERROR
SET @PropOrMethodName = 'Open'
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
IF @hr <> 0
GOTO FUN_ERROR
SET @PropOrMethodName = 'TransformNodeToObject'
EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, NULL, @ObjXSL, @ObjStream
IF @hr <> 0
GOTO FUN_ERROR
SET @OLEActionName = 'sp_OAGetProperty'
SET @PropOrMethodName = 'Size'
EXEC @hr = sp_OAGetProperty @ObjStream, @PropOrMethodName, @BuffSize OUT
IF @hr <> 0
GOTO FUN_ERROR
SET @OLEActionName = 'sp_OASetProperty'
SET @PropOrMethodName = 'Position'
EXEC @hr = sp_OASetProperty @ObjStream, 'Position', 0
IF @hr <> 0
GOTO FUN_ERROR
SET @OLEActionName = 'sp_OAMethod'
SET @PropOrMethodName = 'ReadText'
WHILE @BuffSize > @ChunkCharSize
BEGIN
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT, @ChunkCharSize
IF @hr <> 0
GOTO FUN_ERROR
SET @ResultBuff += @Chunk
SET @BuffSize -= @ChunkCharSize
END
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT
IF @hr <> 0
GOTO FUN_ERROR
SET @ResultBuff += @Chunk
SET @PropOrMethodName = 'Close'
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
IF @hr <> 0
GOTO FUN_ERROR
SET @Result = @ResultBuff
GOTO OLE_RELEASE
FUN_ERROR:
SET @Result = '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'
OLE_RELEASE:
SET @OLEActionName = 'sp_OADestroy'
SET @PropOrMethodName = 'Microsoft.XMLDOM'
IF ISNULL(@ObjXML, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjXML
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
IF ISNULL(@ObjXSL, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjXSL
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
SET @PropOrMethodName = 'ADODB.Stream'
IF ISNULL(@ObjStream, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjStream
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
RETURN @Result
OLE_RELEASE_ERROR:
RETURN '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'
END
现在检查整个香肠:测试数据:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
基本模式:declare @templatetext varchar(max) = '<?xml version=''1.0'' encoding=''UTF-8''?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match = ''item''>
<tr><td><xsl:value-of select = ''@id'' /></td><td><xsl:value-of select = ''@name'' /></td></tr>
</xsl:template>
<xsl:template match = ''/''>
<HTML>
<BODY>
<TABLE>
<TR><TD colspan=''2''>Item List</TD></TR>
<xsl:apply-templates select = ''root'' />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>'
最后,我们将模板从文本转换为XML,然后调用我们的函数:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
然后我们得到输出:<HTML><BODY><TABLE><TR><TD colspan="2">Item List</TD></TR><tr><td>1</td><td>john</td></tr><tr><td>2</td><td>bob</td></tr></TABLE></BODY></HTML>
关于如何通过邮件发送所有这些信息(甚至还有附件-最后一个-带有肮脏的hack),如果有趣的话,我可以写另一篇文章