MsSQL XML

screenshot 

-- Defaine temporary MailTplTmp table for example purposes
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE LEFT(Name, CHARINDEX('_', Name)) = '#MailTplTmp_') DROP TABLE #MailTplTmp

CREATE TABLE #MailTplTmp (TplHtml VARCHAR(MAX));
INSERT INTO #MailTplTmp VALUES('<html><a href="{data(Email)}">{data(Name)}</a></html>');

-- Define temporary MailDataTmp table for example purposes
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE LEFT(Name, CHARINDEX('_', Name)) = '#MailDataTmp_') DROP TABLE #MailDataTmp

CREATE TABLE #MailDataTmp (Email VARCHAR(MAX), Name VARCHAR(MAX));
INSERT INTO #MailDataTmp VALUES('[email protected]', 'Alex');
INSERT INTO #MailDataTmp VALUES('[email protected]', 'Oleg');

-- @TPL will contain selected template for email
DECLARE @TPL VARCHAR(MAX); SELECT @TPL = TplHtml FROM #MailTplTmp;
-- will return:
-- +------+------------------+
-- | Alex | [email protected] |
-- | Oleg | [email protected] |
-- +------+------------------+

SELECT * FROM #MailDataTmp;
-- will return xml representation of table above (select into xml):
-- <Item><Email>Alex</Email><Name>[email protected]</Name></Item><Item><Email>Oleg</Email><Name>[email protected]</Name></Item>

SELECT ( SELECT * FROM #MailDataTmp FOR XML PATH('Item'), TYPE, ELEMENTS ) AS XmlRow;

-- will return transformed xml
SELECT x.y.query('<html><a href="{data(Email)}">{data(Name)}</a></html>') AS Html FROM ( SELECT ( SELECT * FROM #MailDataTmp FOR XML PATH('Item'), TYPE, ELEMENTS ) AS XmlRow ) AS data CROSS APPLY XmlRow.nodes('Item') x(y);

-- To avoid: "The argument 1 of the XML data type method "query" must be a string literal." -- error, we use sp_executesql

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT x.y.query(''' + @TPL + ''') AS Html_sp_executesql FROM ( SELECT ( SELECT * FROM #MailDataTmp FOR XML PATH(''Item''), TYPE, ELEMENTS ) AS XmlRow ) AS data CROSS APPLY XmlRow.nodes(''Item'') x(y)';
EXEC sp_executesql @SQL;