Às vezes, é muito conveniente enviar cartas diretamente de dentro do banco de dados, por exemplo, notificações sobre o sucesso / falha de algumas ações, informações sobre o estado do sistema, logs de ações do usuário etc. Pode parecer selvageria, uma bicicleta monstruosa, uma solução inclinada e torta, etc. - mas imagine que sim.O conteúdo da carta com esse método deve ser formado em texto sem formatação e o email é enviado pelo xp_sendmail ou (com mais flexibilidade) pelo objeto de email COM (por exemplo, CDO.Message), instanciando e gerenciando-o através dos wrappers SQL para trabalhar com o OLE sp_OAxxxx.Ambos funcionam desde que você tenha meios expressivos suficientes de texto de pintura, a hierarquia de seus dados esteja próxima de zero e o relatório seja consumido exclusivamente por um diploma técnico da velha escola, que+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
O que fazer se um formato semelhante começar a sobrecarregar e registrar seus componentes no servidor ou "emergir" do nível do banco de dados para o nível do aplicativo para enviar algo mais bonito, eu realmente não quero:Muitas vezes, as letras ainda precisam ser formatadas (por exemplo, se uma parte da tabela de auditoria for enviada) e enviadas em HTML, pelo menos em um design mínimo (corporativo) - e aqui uma rotina desagradável já está começando - é muito entediante gerar HTML linha por linha usando T-SQL, especialmente se houver vários tipos diferentes de relatórios de email, a estrutura do relatório é hierárquica, o design é geral e o CSS está se espalhando.No mundo do nível de aplicativo, um incrível dipolo XML / XSL foi inventado há 20 anos, tão coerente que a descrição da sintaxe de seu último componente mal é lembrada durante o tempo entre ler um exemplo do MSDN e escrever um elemento de seu próprio modelo.Bem, tudo bem - no final, as informações hierárquicas também são muito mais convenientes para representar em XML, e o MS SQL nativamente faz isso muito bem nos últimos 15 anos.Portanto, lindas letras estão a poucos passos de distância - tudo o que resta é encontrar uma maneira de adicionar a transformação XSL a esta bicicleta.Infelizmente, fora da caixa, o MS SQL não pode fazer isso e, portanto, nossa bicicleta conterá outra roda OLE.Após vasculhar a memória e a Internet, lembramos que temos o Microsoft.XMLDOM, que pode atravessar o porco-espinho e a cobra chamando o método transformNode, e parece que nossa tarefa é instalar corretamente tudo, capturar o resultado, processar possíveis erros e corrigir tudo para não flua memória.Estamos no caminho certo e sp_OACreate, sp_OAMethod, sp_OAGet / SetProperty e sp_OADestroy nos ajudarão, mas há um pequeno detalhe - se o método retornar um ponteiro, tudo estará bem, mas se um escalar estiver ruim, porque um escalar de cadeia não pode ser maior que a constante fundamental de 8kB. Com essa limitação, nosso exemplo permanecerá para sempre educacional - já que o documento resultante de 4 (8) mil caracteres em nosso século é uma risada, e é tudo.Passamos por um longo labirinto, e um passo para fora da saída, já vê a luz, mas bams! - a saída é fechada por uma grade. Não há como obter uma seqüência maior que 8K de um wrapper OLE. Tentar especificar o tipo de (n) varchar (MAX) como o parâmetro de recebimento leva a algum erro OLE inaudível.Entendemos que, de alguma forma, precisamos de algo que retorne não um escalar, mas um ponteiro para o resultado (porque um ponteiro para SQL é apenas um número). Mas forçar o método transformNode a retornar um ponteiro não é possível. Tendo ficado desesperados, entramos no MSDN (ou o que quer que esteja online) e vemos que o XMLDOM evoluiu nos últimos 15 anos - a MS agora oferece o método transformNodeToObject - BINGO !!! O método leva um ponteiro para o fluxo no qual ele derrama o conteúdo do documento!Depois, é simples: criamos um objeto de fluxo, passamos para o método, solicitamos que despeje o documento no fluxo.Como resultado - no fluxo conosco - um documento. Como puxá-lo para um escalar? Também é fácil escavar o mar com círculos em pedaços que não excedem 8 KB e colar o resultado em pedaços em varchar (MAX).Não esqueça que antes de subtrair tudo, você precisa: a) ler o tamanho atual do fluxo - este será o número de caracteres que precisamos obter dele eb) colocar o ponteiro para ler o fluxo no início. Caso contrário, não haverá nada para ler.Bem, afinal, você precisa limpar cuidadosamente depois de si mesmo. É desejável que tudo o que foi criado seja destruído e se foi um erro ou não, e se foi, qual fase ocorreu.Aqui está o resultado: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
Agora verifique toda a lingüiça:Dados do teste:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Padrão primitivo: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>'
Por fim, convertemos o modelo de texto em XML e chamamos nossa função:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
E obtemos a saída:<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>
Sobre como enviar tudo isso por correio (e mesmo com anexos - o último - com um truque sujo), posso escrever outro post, se for interessante