Parfois, il est très pratique d'envoyer des lettres directement depuis l'intérieur de la base de données, par exemple, des notifications sur le succès / l'échec de certaines actions, des informations sur l'état du système, des journaux d'actions utilisateur, etc. Cela peut sembler de la sauvagerie, un vélo monstrueux, une solution oblique, etc. - mais imaginez que c'est le cas.Le contenu de la lettre avec cette méthode doit être formé en texte brut et le courrier est envoyé soit via xp_sendmail, soit (de manière plus flexible) via un objet de messagerie COM (par exemple, CDO.Message), en l'instanciant et en le gérant via des wrappers SQL pour fonctionner avec OLE sp_OAxxxx.Les deux fonctionnent tant que vous disposez de suffisamment de moyens expressifs de peinture, la hiérarchie de vos données est proche de zéro et le rapport est consommé exclusivement par un diplôme technique de la vieille école, qui+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
Que faire si un format similaire commence à se fatiguer et à enregistrer vos composants sur le serveur, ou "émerge" du niveau de la base de données au niveau de l'application pour envoyer quelque chose de plus beau, je ne veux vraiment pas:Souvent, les lettres doivent encore être formatées (par exemple, si un morceau de la table d'audit est envoyé) et envoyées en HTML, au moins dans une conception minimale (entreprise) - et ici une routine désagréable commence déjà - il est très fastidieux de générer HTML ligne par ligne à l'aide de T-SQL, surtout s'il existe plusieurs types de rapports de messagerie, la structure du rapport est hiérarchique, la conception est générale et le CSS s'y propage.Dans le monde de l'application, un incroyable dipôle XML / XSL a été inventé il y a 20 ans, si cohérent que la description syntaxique de son dernier composant est à peine gardée à l'esprit entre la lecture d'un exemple à partir de MSDN et l'écriture d'un élément de son propre modèle.Eh bien, très bien - au final, les informations hiérarchiques sont également beaucoup plus pratiques à représenter en XML, et MS SQL le fait très bien nativement depuis 15 ans.Ainsi, de belles lettres sont à portée de main - il ne reste plus qu'à trouver un moyen d'ajouter la transformation XSL à ce vélo.Malheureusement, MS SQL n'est pas en mesure de le faire, et notre vélo contiendra donc une autre roue OLE.Après avoir fouillé dans la mémoire et sur Internet, nous rappelons que nous avons Microsoft.XMLDOM, qui peut traverser le hérisson et le serpent en appelant la méthode transformNode, et il semble que notre tâche consiste à tout installer correctement, à ramasser le résultat, à traiter les erreurs possibles et à tout réparer afin de ne faites pas couler la mémoire.Nous sommes sur la bonne voie, et sp_OACreate, sp_OAMethod, sp_OAGet / SetProperty et sp_OADestroy nous aideront, mais il y a un petit détail - si la méthode renvoie un pointeur, alors tout va bien, mais si un scalaire est tout mauvais, parce que un scalaire de chaîne ne peut pas être plus long que la constante fondamentale de 8 Ko. Avec cette limitation, notre exemple restera pour toujours éducatif - puisque le document résultant de 4 (8) mille caractères dans notre siècle est un rire, et c'est tout.Nous avons traversé un long labyrinthe, et à deux pas de la sortie, on voit déjà la lumière, mais bams! - la sortie est fermée par un treillis. Il n'existe aucun moyen d'obtenir une chaîne de plus de 8 Ko à partir d'un wrapper OLE. Tenter de spécifier le type (n) varchar (MAX) comme paramètre de réception entraîne une erreur OLE inaudible.Nous comprenons que d'une manière ou d'une autre, nous avons besoin de quelque chose qui ne renvoie pas un scalaire, mais un pointeur vers le résultat (car un pointeur vers SQL n'est qu'un nombre). Mais forcer la méthode transformNode à renvoyer un pointeur n'est pas possible. Après avoir désespéré, nous montons dans MSDN (ou quoi que ce soit en ligne), et nous voyons que XMLDOM a évolué au cours des 15 dernières années - MS propose maintenant la méthode transformNodeToObject - BINGO !!! La méthode prend un pointeur sur le flux dans lequel elle verse le contenu du document!Ensuite, c'est simple - nous créons un objet de flux, le transmettons à la méthode, lui disons de verser le document dans le flux.En conséquence - dans le flux avec nous - un document. Comment le tirer dans un scalaire? Il est également facile de creuser la mer avec des cercles en morceaux ne dépassant pas 8 Ko et de coller le résultat en morceaux dans le varchar (MAX).N'oubliez pas qu'avant de tout soustraire, vous devez a) lire sa taille actuelle dans le flux - ce sera le nombre de caractères que nous devons en obtenir, et b) mettre le pointeur pour lire le flux au début. Sinon, il n'y aura rien à lire.Eh bien, après tout, vous devez nettoyer soigneusement après vous-même. Il est souhaitable que tout ce qui a été créé soit détruit et que ce soit une erreur ou non, et si c'était le cas, alors quelle phase s'est produite.Voici le résultat: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
Maintenant, vérifiez la saucisse entière:Données de test:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Motif primitif: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>'
Enfin, nous convertissons le modèle de texte en XML et appelons notre fonction:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
Et nous obtenons la sortie:<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>
Sur la façon d'envoyer tout cela par mail (et même avec des pièces jointes - la dernière - avec un hack sale), je peux écrire un autre post, si c'est intéressant