Содержимое письма при таком способе приходится формировать plain-текстом, а рассылать почту либо через xp_sendmail, либо (более гибко) через почтовый COM-объект (например, CDO.Message), инстанциируя и управляя им через SQL-обертки для работы с OLE sp_OAxxxx.
И то, и другое работает, пока вам хватает выразительных средств плейнтекста, иерархичность ваших данных — околонулевая, и отчет потребляется исключительно олдскульным техническим пиплом, который
+-----------+--------------+--------------+
| АБСОЛЮТНО | НЕ ВИДИТ ПРО | БЛЕМ В ТАКОМ |
| ОФОРМЛЕНИ | И СЛУЖЕБНОГО | ВЫВОДА <EOT> |
+-----------+--------------+--------------+
Что делать, если подобный формат начинает напрягать, а регистрировать на сервере свои компоненты, или «выныривать» из уровня БД на уровень приложения для отправки чего-то более красивого ну очень не хочется:
Часто письма все-таки нужно делать форматированными (скажем, если отсылается кусок таблицы аудита), и отсылать в HTML, хотя бы в минимальном (корпоративном) дизайне — и вот тут уже начинается неприятная рутина — формировать построчно HTML средствами T-SQL очень утомительно, особенно, если разных типов почтовых отчетов несколько, структура отчетов — иерархическая, а дизайн — общий, и CSS в нем — развесистый.
В мире уровня приложений для этого еще 20 лет назад придумали удивительный диполь XML/XSL, такой стройный, что описание синтаксиса последнего его компонента еле удерживается в голове на время между чтением примера из MSDN и написанием элемента собственного шаблона.
Ну да ладно — в конце концов, иерархическую информацию тоже представлять в XML гораздно удобнее, и MS SQL нативно делает это очень неплохо последние 15 лет.
Итак, до красивых писем рукой подать — остается лишь найти способ присобачить XSL-трансформацию к этому велосипеду.
К сожалению, из коробки MS SQL этого делать не умеет, и поэтому наш велосипед будет содержать еще одно OLE-колесо.
Покопавшись в памяти и в Интернете, вспоминаем, что у нас есть Microsoft.XMLDOM, который умеет скрещивать ежа и ужа посредством вызова метода transformNode, и вроде бы наша задача — грамотно все инстанциировать, забрать результат, обработать возможные ошибки, и все задестроить, чтобы не течь памятью.
Мы на верном пути, и sp_OACreate, sp_OAMethod, sp_OAGet/SetProperty и sp_OADestroy нам помогут, но есть одна маленькая деталь — если метод возвращает указатель, то все хорошо, а если скаляр — то все плохо, т.к. строковый скаляр не может быть длиннее чем фундаментальная константа в 8кБ. С этим ограничением наш пример останется навек учебным — так как результирующий документ в 4(8) тысяч символов в наш век — это смех, да и только.
Мы прошли длинный лабиринт, и в шаге от выхода, уже видет свет, но бамс! — выход закрыт решеткой. Нет никакой возможности получить из OLE-обертки строку, длиннее чем в 8К. Попытка указать в качестве принимающего параметра тип (n)varchar(MAX) приводит к какой-то невнятной OLE-ошибке.
Понимаем, что нам каким-то образом нужно что-то, что возвращает не скаляр, а указатель на результат (т.к. указатель для SQL — это просто число). Но заставить метод transformNode отдать указатель нет никакой возможности. Отчаившись, лезем в MSDN (или как его там в онлайн), и видим, что за последние 15 лет XMLDOM эволюционировал — MS теперь предлагает метод transformNodeToObject — БИНГО!!! Метод принимает указатель на поток, в который он выливает содержимое документа!
Дальше просто — создаем объект потока, передаем методу, говорим ему вылить документ в поток.
В итоге — в потоке у нас — документ. Как его вытащить в скаляр? Тоже несложно — вычерпать море кружками вычитать чанками, не превышающими 8Кбайт, и склеить из чанков результат в varchar(MAX).
Не забываем, что перед тем, как все вычитать, нужно а) считать из потока его текущий размер — это будет количество символов, которые нам нужно из него получить, и б) поставить указатель чтения потока на начало. Иначе читать будет нечего.
Ну и после всего, нужно аккуратно прибрать за собой. Желательно, чтобы все, что было создано, было и уничтожено — независимо от того, была ошибка или нет, и если была, то на какой фазе произошла.
Вот что в итоге получилось:
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), -- VVVV should match VVVV @ChunkCharSize INT = 4000, -- ^^^^^^^^^^^^^^^^^^^^^^ @ResultBuff NVARCHAR(MAX) = N'' -- chunk concat buffer -- create XMLDOM object for @XMLData SET @OLEActionName = 'sp_OACreate' SET @PropOrMethodName = 'Microsoft.XMLDOM' EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXML OUT IF @hr <> 0 GOTO FUN_ERROR -- create XMLDOM object for @XSLData EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXSL OUT IF @hr <> 0 GOTO FUN_ERROR -- create ADODB.Stream object as transformation buffer SET @PropOrMethodName = 'ADODB.Stream' EXEC @hr = sp_OACreate @PropOrMethodName, @ObjStream OUT IF @hr <> 0 GOTO FUN_ERROR -- load XML data SET @OLEActionName = 'sp_OAMethod' SET @PropOrMethodName = 'LoadXML' EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, @dummy OUT, @XMLData IF @hr <> 0 GOTO FUN_ERROR -- load XSL data EXEC @hr = sp_OAMethod @ObjXSL, @PropOrMethodName, @dummy OUT, @XSLTemplate IF @hr <> 0 GOTO FUN_ERROR -- open the stream SET @PropOrMethodName = 'Open' EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL IF @hr <> 0 GOTO FUN_ERROR -- trying to do XSL transformation, using the stream as the receiver to work around 4/8K limitation SET @PropOrMethodName = 'TransformNodeToObject' EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, NULL, @ObjXSL, @ObjStream IF @hr <> 0 GOTO FUN_ERROR -- get the size of the stream to read back SET @OLEActionName = 'sp_OAGetProperty' SET @PropOrMethodName = 'Size' EXEC @hr = sp_OAGetProperty @ObjStream, @PropOrMethodName, @BuffSize OUT IF @hr <> 0 GOTO FUN_ERROR -- re-position the stream to the head.. SET @OLEActionName = 'sp_OASetProperty' SET @PropOrMethodName = 'Position' EXEC @hr = sp_OASetProperty @ObjStream, 'Position', 0 -- offset = 0 IF @hr <> 0 GOTO FUN_ERROR -- ..and then read chunk by chunk SET @OLEActionName = 'sp_OAMethod' SET @PropOrMethodName = 'ReadText' WHILE @BuffSize > @ChunkCharSize BEGIN -- read chunk EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT, @ChunkCharSize IF @hr <> 0 GOTO FUN_ERROR -- append it to the accumulated buffer contents.. SET @ResultBuff += @Chunk -- ..and correct the char counter by the # of chars retrieved SET @BuffSize -= @ChunkCharSize END -- read the last chunk EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT IF @hr <> 0 GOTO FUN_ERROR -- append the last chunk to the buffer SET @ResultBuff += @Chunk -- close the stream SET @PropOrMethodName = 'Close' EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL IF @hr <> 0 GOTO FUN_ERROR -- everything is ok, copying buffer to result SET @Result = @ResultBuff -- resulting doc is in @Result, cleaning up and exiting.. GOTO OLE_RELEASE FUN_ERROR: SET @Result = '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####' -- fall through OLE release OLE_RELEASE: -- destroying XML.. 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 -- ..and XSL objects IF ISNULL(@ObjXSL, 0) <> 0 BEGIN EXEC @hr = sp_OADestroy @ObjXSL IF @hr <> 0 GOTO OLE_RELEASE_ERROR END -- and the stream obj SET @PropOrMethodName = 'ADODB.Stream' IF ISNULL(@ObjStream, 0) <> 0 BEGIN EXEC @hr = sp_OADestroy @ObjStream IF @hr <> 0 GOTO OLE_RELEASE_ERROR END -- exiting with returning the resulting document RETURN @Result OLE_RELEASE_ERROR: -- OLE release error, exiting with error info 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>Про то, как все это отправить почтой (и даже с аттачментами — причем последнее — грязным хаком), могу написать еще один пост, если будет интересно
ссылка на оригинал статьи https://habr.com/ru/post/487490/