Manchmal ist es sehr praktisch, Briefe direkt aus der Datenbank zu senden, z. B. Benachrichtigungen über den Erfolg / Misserfolg einiger Aktionen, Informationen zum Systemstatus, Benutzeraktionsprotokolle usw. Es mag wie Wildheit erscheinen, ein monströses Fahrrad, eine krumme Lösung usw. - aber stellen Sie sich vor, es ist so.Der Inhalt des Briefes mit dieser Methode muss im Klartext erstellt werden, und E-Mails werden entweder über xp_sendmail oder (flexibler) über ein COM-Mail-Objekt (z. B. CDO.Message) gesendet, um ihn über SQL-Wrapper zu instanziieren und zu verwalten, damit er mit OLE sp_OAxxxx funktioniert.Beide funktionieren, solange Sie über genügend Ausdrucksmittel für Painttext verfügen, die Hierarchie Ihrer Daten nahe Null ist und der Bericht ausschließlich von einem technischen Diplom der alten Schule verwendet wird+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
Was tun, wenn ein ähnliches Format anfängt, Ihre Komponenten zu belasten und auf dem Server zu registrieren, oder von der Datenbankebene zur Anwendungsebene "auftaucht", um etwas Schöneres zu senden? Ich möchte wirklich nicht:Oft müssen Briefe noch formatiert werden (z. B. wenn ein Teil der Prüftabelle gesendet wird) und in HTML gesendet werden, zumindest in einem minimalen (Unternehmens-) Design - und hier beginnt bereits eine unangenehme Routine - es ist sehr mühsam, HTML Zeile für Zeile mit T-SQL zu generieren. Insbesondere wenn es mehrere verschiedene Arten von E-Mail-Berichten gibt, ist die Berichtsstruktur hierarchisch und das Design ist allgemein und das darin enthaltene CSS verbreitet sich.In der Welt der Anwendungsebene wurde vor 20 Jahren ein erstaunlicher XML / XSL-Dipol erfunden, der so kohärent ist, dass die Syntaxbeschreibung seiner letzten Komponente für die Zeit zwischen dem Lesen eines Beispiels aus MSDN und dem Schreiben eines Elements einer eigenen Vorlage kaum berücksichtigt wird.Nun gut - letztendlich sind hierarchische Informationen auch viel bequemer in XML darzustellen, und MS SQL macht dies in den letzten 15 Jahren von Haus aus sehr gut.Schöne Buchstaben sind also nur einen Steinwurf entfernt - alles, was bleibt, ist einen Weg zu finden, um diesem Fahrrad die XSL-Transformation hinzuzufügen.Leider ist MS SQL nicht in der Lage, dies zu tun, und daher wird unser Fahrrad ein weiteres OLE-Rad enthalten.Nachdem wir im Speicher und im Internet gestöbert haben, erinnern wir uns, dass wir Microsoft.XMLDOM haben, das den Igel und die Schlange durch Aufrufen der transformNode-Methode kreuzen kann, und es scheint, dass unsere Aufgabe darin besteht, alles korrekt zu installieren, das Ergebnis aufzunehmen, mögliche Fehler zu verarbeiten und alles zu beheben, um dies zu erreichen Speicher nicht fließen.Wir sind auf dem richtigen Weg und sp_OACreate, sp_OAMethod, sp_OAGet / SetProperty und sp_OADestroy werden uns helfen, aber es gibt ein kleines Detail - wenn die Methode einen Zeiger zurückgibt, ist alles in Ordnung, aber wenn ein Skalar schlecht ist, weil Ein String-Skalar kann nicht länger als die Grundkonstante von 8 kB sein. Mit dieser Einschränkung wird unser Beispiel für immer lehrreich bleiben - da das resultierende Dokument mit 4 (8) tausend Zeichen in unserem Jahrhundert ein Lachen ist, und das ist alles.Wir gingen durch ein langes Labyrinth und einen Schritt vom Ausgang entfernt, sehen schon das Licht, aber bams! - Der Ausgang ist durch ein Gitter verschlossen. Es gibt keine Möglichkeit, einen String, der länger als 8 KB ist, von einem OLE-Wrapper zu erhalten. Der Versuch, den Typ von (n) varchar (MAX) als Empfangsparameter anzugeben, führt zu einem unhörbaren OLE-Fehler.Wir verstehen, dass wir irgendwie etwas brauchen, das keinen Skalar zurückgibt, sondern einen Zeiger auf das Ergebnis (weil ein Zeiger auf SQL nur eine Zahl ist). Es ist jedoch nicht möglich, die transformNode-Methode zu zwingen, einen Zeiger zurückzugeben. Nachdem wir verzweifelt sind, steigen wir in MSDN (oder was auch immer online ist) ein und sehen, dass sich XMLDOM in den letzten 15 Jahren weiterentwickelt hat - MS bietet jetzt die transformNodeToObject-Methode an - BINGO !!! Die Methode nimmt einen Zeiger auf den Stream, in den sie den Inhalt des Dokuments gießt!Dann ist es ganz einfach: Wir erstellen ein Stream-Objekt, übergeben es an die Methode und weisen es an, das Dokument in den Stream zu gießen.Als Ergebnis - im Stream mit uns - ein Dokument. Wie zieht man es in einen Skalar? Es ist auch einfach , das Meer mit Kreisen in Stücken von nicht mehr als 8 KB zu schöpfen und das Ergebnis in Stücken in Varchar (MAX) zu kleben.Vergessen Sie nicht, dass Sie, bevor Sie alles subtrahieren, Folgendes tun müssen: a) Lesen Sie die aktuelle Größe aus dem Stream - dies ist die Anzahl der Zeichen, die wir daraus abrufen müssen, und b) setzen Sie den Zeiger, um den Stream am Anfang zu lesen. Andernfalls gibt es nichts zu lesen.Nun, schließlich müssen Sie sorgfältig nach sich selbst aufräumen. Es ist wünschenswert, dass alles, was geschaffen wurde, zerstört wird und ob es ein Fehler war oder nicht, und wenn ja, welche Phase ist dann passiert.Hier ist das Ergebnis: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
Überprüfen Sie nun die gesamte Wurst:Testdaten:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Primitives Muster: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>'
Schließlich konvertieren wir die Vorlage von Text in XML und rufen unsere Funktion auf:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
Und wir bekommen die Ausgabe:<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>
Wie man das alles per Post verschickt (und sogar mit Anhängen - und dem letzten - mit einem schmutzigen Hack), kann ich einen weiteren Beitrag schreiben, wenn es interessant ist