MsSQL XML

-- 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('alex@example.com', 'Alex'); INSERT INTO #MailDataTmp VALUES('oleg@example.com', 'Oleg'); -- @TPL will contain selected template for email DECLARE @TPL VARCHAR(MAX); SELECT @TPL = TplHtml FROM #MailTplTmp; -- will return: -- +------+------------------+ -- | Alex | alex@example.com | -- | Oleg | oleg@example.com | -- +------+------------------+ SELECT * FROM #MailDataTmp; -- will return xml representation of table above (select into xml): -- <Item><Email>Alex</Email><Name>alex@example.com</Name></Item><Item><Email>Oleg</Email><Name>oleg@example.com</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;