XSL-трансформация на MS SQL без CLR

Иногда очень удобно отправлять письма прямо изнутри БД, например, оповещения об успешности/неуспешности каких-то действий, информацию о состоянии системы, логи действий пользователей итд. Это может показаться дикостью, чудовищным велосипедом, кривым-косым решением, итд — но просто представьте, что это так.

Содержимое письма при таком способе приходится формировать 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>

Про то, как все это отправить почтой (и даже с аттачментами — причем последнее — грязным хаком), могу написать еще один пост, если будет интересно

FavoriteLoadingДобавить в избранное
Posted in Без рубрики

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *