Can I tranform XML to HTML in SQL Server?

Question

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 protected]'
      @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?

Answer 1

I think you would need to use the CLR for that to do it by applying an XSLT transform.

Answer 2

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