A veces es muy conveniente enviar cartas directamente desde el interior de la base de datos, por ejemplo, notificaciones sobre el éxito / fracaso de algunas acciones, información sobre el estado del sistema, registros de acciones del usuario, etc. Puede parecer un salvajismo, una bicicleta monstruosa, una solución inclinada torcida, etc., pero imagínelo.El contenido de la carta con este método debe formarse en texto plano, y el correo se envía a través de xp_sendmail o (de manera más flexible) a través del objeto de correo COM (por ejemplo, CDO.Message), instanciando y administrando a través de contenedores SQL para trabajar con OLE sp_OAxxxx.Ambos funcionan siempre que tenga suficientes medios expresivos de painttext, la jerarquía de sus datos es casi cero y el informe es consumido exclusivamente por un diploma técnico de la vieja escuela, que+-----------+--------------+--------------+
| | | |
| | | <EOT> |
+-----------+--------------+--------------+
Qué hacer si un formato similar comienza a agotarse y registrar sus componentes en el servidor, o "emerger" del nivel de la base de datos al nivel de la aplicación para enviar algo más hermoso, realmente no quiero:A menudo, las cartas aún deben formatearse (por ejemplo, si se envía una parte de la tabla de auditoría) y enviarse en HTML, al menos en un diseño mínimo (corporativo), y aquí ya está comenzando una rutina desagradable, es muy tedioso generar HTML línea por línea usando T-SQL, especialmente si hay varios tipos diferentes de informes de correo, la estructura del informe es jerárquica y el diseño es general, y el CSS en él se está extendiendo.En el mundo del nivel de aplicación, se inventó un sorprendente dipolo XML / XSL hace 20 años, tan coherente que la descripción de la sintaxis de su último componente apenas se tiene en cuenta por el tiempo entre leer un ejemplo de MSDN y escribir un elemento de su propia plantilla.Bueno, está bien: al final, la información jerárquica también es mucho más conveniente de representar en XML, y MS SQL lo hace muy bien de forma nativa durante los últimos 15 años.Por lo tanto, las letras hermosas están a tiro de piedra, todo lo que queda es encontrar una manera de agregar la transformación XSL a esta bicicleta.Desafortunadamente, fuera de la caja, MS SQL no puede hacer esto y, por lo tanto, nuestra bicicleta contendrá otra rueda OLE.Habiendo hurgado en la memoria y en Internet, recordamos que tenemos Microsoft.XMLDOM, que puede cruzar el erizo y la serpiente llamando al método transformNode, y parece que nuestra tarea es instalar todo correctamente, recoger el resultado, procesar posibles errores y arreglar todo para No fluya la memoria.Estamos en el camino correcto, y sp_OACreate, sp_OAMethod, sp_OAGet / SetProperty y sp_OADestroy nos ayudarán, pero hay un pequeño detalle: si el método devuelve un puntero, entonces todo está bien, pero si un escalar está mal, porque un escalar de cadena no puede ser más largo que la constante fundamental de 8kB. Con esta limitación, nuestro ejemplo seguirá siendo educativo para siempre, ya que el documento resultante de 4 (8) mil caracteres en nuestro siglo es una risa, y eso es todo.Atravesamos un largo laberinto y, a un paso de la salida, ya vemos la luz, pero ¡bams! - la salida está cerrada por una celosía. No hay forma de obtener una cadena de más de 8K de un contenedor OLE. Intentar especificar el tipo de (n) varchar (MAX) como el parámetro receptor conduce a algún error OLE inaudible.Entendemos que de alguna manera necesitamos algo que no devuelva un escalar, sino un puntero al resultado (porque un puntero a SQL es solo un número). Pero forzar el método transformNode para que devuelva un puntero no es posible. Habiendo desesperado, subimos a MSDN (o lo que sea que esté en línea), y vemos que XMLDOM ha evolucionado en los últimos 15 años. ¡MS ahora ofrece el método transformNodeToObject - BINGO! ¡El método lleva un puntero a la secuencia en la que vierte el contenido del documento!Entonces es simple: creamos un objeto de flujo, lo pasamos al método, le decimos que vierta el documento en el flujo.Como resultado, en la transmisión con nosotros, un documento. ¿Cómo meterlo en un escalar? También es fácil sacar el mar con círculos en trozos que no superen los 8 KB y pegar el resultado en trozos en varchar (MAX).No olvide que antes de restar todo, debe: a) leer su tamaño actual de la secuencia: este será el número de caracteres que necesitamos obtener de él, yb) colocar el puntero para leer la secuencia al principio. De lo contrario, no habrá nada que leer.Bueno, después de todo, debes limpiar cuidadosamente después de ti. Es deseable que todo lo que se creó se destruya y si fue un error o no, y si lo fue, entonces qué fase ocurrió.Aquí está el resultado: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
Ahora verifique toda la salchicha:Datos de prueba:declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Patrón primitivo: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>'
Finalmente, convertimos la plantilla de texto a XML y llamamos a nuestra función:declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
Y obtenemos la salida:<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>
Sobre cómo enviar todo esto por correo (e incluso con archivos adjuntos, y el último, con un truco sucio), puedo escribir otra publicación, si es interesante