Sometimes it is very convenient to send letters directly from inside the database, for example, notifications about the success / failure of some actions, information about the state of the system, user action logs, etc. It may seem like savagery, a monstrous bike, a crooked-slanting solution, etc. - but just imagine that it is.The content of the letter with this method has to be formed in plain text, and mail is sent either through xp_sendmail, or (more flexibly) through the COM mail object (for example, CDO.Message), instantiating and managing it through SQL wrappers to work with OLE sp_OAxxxx.Both of them work as long as you have enough expressive means of painttext, the hierarchy of your data is near zero, and the report is consumed exclusively by an old-school technical diploma, which+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
What to do if a similar format starts to strain, and register your components on the server, or โemergeโ from the database level to the application level to send something more beautiful, I really donโt want to:Often, letters still need to be formatted (say, if a piece of the audit table is sent), and sent in HTML, at least in a minimal (corporate) design - and here an unpleasant routine is already beginning - it is very tedious to generate HTML line-by-line using T-SQL, especially if there are several different types of mail reports, the report structure is hierarchical, and the design is general, and the CSS in it is spreading.In the world of application level, an amazing XML / XSL dipole was invented 20 years ago, so coherent that the syntax description of its last component is barely kept in mind for a while between reading an example from MSDN and writing an element of its own template.Well, all right - in the end, hierarchical information is also much more convenient to represent in XML, and MS SQL natively does this very well for the last 15 years.So, beautiful letters are just a stone's throw away - all that remains is to find a way to add the XSL transformation to this bike.Unfortunately, out of the box, MS SQL is not able to do this, and therefore our bike will contain another OLE-wheel.Having rummaged in memory and on the Internet, we recall that we have Microsoft.XMLDOM, which can cross the hedgehog and the snake by calling the transformNode method, and it seems that our task is to correctly install everything, pick up the result, process possible errors, and fix everything in order to do not flow memory.We are on the right track, and sp_OACreate, sp_OAMethod, sp_OAGet / SetProperty and sp_OADestroy will help us, but there is one small detail - if the method returns a pointer, then everything is fine, but if a scalar is all bad, because a string scalar cannot be longer than the fundamental constant of 8kB. With this limitation, our example will remain forever educational - since the resulting document of 4 (8) thousand characters in our century is a laugh, and that's all.We went through a long maze, and a step away from the exit, already sees the light, but bams! - the exit is closed by a lattice. There is no way to get a string longer than 8K from an OLE wrapper. Attempting to specify the type (n) varchar (MAX) as the receiving parameter leads to some inaudible OLE error.We understand that somehow we need something that returns not a scalar, but a pointer to the result (because a pointer to SQL is just a number). But to force the transformNode method to return a pointer is not possible. Having despaired, we climb into MSDN (or whatever it is online), and we see that XMLDOM has evolved over the past 15 years - MS now offers the transformNodeToObject method - BINGO !!! The method takes a pointer to the stream into which it pours the contents of the document!Then itโs simple - we create a stream object, pass it to the method, tell it to pour the document into the stream.As a result - in the stream with us - a document. How to pull it into a scalar? Itโs also easy to scoop out the sea with circles in chunks not exceeding 8KB and glue the result in chunks into varchar (MAX).Do not forget that before you subtract everything, you need to a) read its current size from the stream - this will be the number of characters that we need to get from it, and b) put the pointer to read the stream at the beginning. Otherwise, there will be nothing to read.Well, after all, you need to carefully clean up after yourself. It is desirable that everything that was created is destroyed and whether it was a mistake or not, and if it was, then what phase happened.Here is the result: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
Now check the whole sausage:Test data:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Primitive pattern: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>'
Finally, we convert the template from text to XML, and call our function:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
And we get the output:<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>
About how to send all this by mail (and even with attachments - and the last one - with a dirty hack), I can write another post, if it is interesting