Can I transform XML to HTML in SQL Server?
I have a query that returns data as XML using the "for XML Clause" and then sends the result as the body of an email like so.
Declare @messBody as nvarchar(max) Set @messBody = (Select * from tablehere where state = 1 for xml Auto) Begin Exec msdb.dbo.ap_send_dbmail @profile_name = 'ProfileNameHere' @recipients = 'email@example.com' @body = '@messBody @subject = 'SubjectHere' End
It works and I get the email with the info I need but the body of the email is ugly XML. I would like to be able to transform it into pretty HTML. Can this be done in SQL Server?
I think you would need to use the CLR for that to do it by applying an XSLT transform.
One way would be to write a clr proc. Here is an article showing how: http://blogs.msdn.com/b/mrorke/archive/2005/06/28/433471.aspx