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>

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