Can I transform 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?

Glorfindel · Accepted Answer · 2022-12-03 10:16:23Z

2

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

edited Dec 3, 2022 at 10:16

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Jul 6, 2010 at 16:07

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Smith Over a year ago

@spinon. Yep. "apply xslt sql server" came up trumps for me :-)

spinon Over a year ago

Yeah sometimes I wonder if people even search for a minute before they post things. So many answers if I don't know off the top of my head I can find in less than a minute of searching on Google.

Glorfindel · Accepted Answer · 2022-12-03 10:16:25Z

1

One way would be to write a clr proc. Here is an article showing how: Link

edited Dec 3, 2022 at 10:16

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Jul 6, 2010 at 16:09

spinon

10.9k5 gold badges45 silver badges62 bronze badges

1 Comment

Irawan Soetomo Over a year ago

The link is gone. This might help, c-sharpcorner.com/article/….

Irawan Soetomo · Accepted Answer · 2020-03-06 10:10:21Z

0

SELECT 
Col1 as 'td', '', 
Col2 as 'td', '', 
Col3 as 'td' 
FROM SomeTable FOR XML PATH('tr'), ELEMENTS

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/05404640-6eed-458e-9514-80ff8bd002f8/sending-mail-with-html-format-in-send-mail-task

answered Mar 6, 2020 at 10:10

Irawan Soetomo

1,3251 gold badge15 silver badges35 bronze badges

Collectives™ on Stack Overflow

Can I transform XML to HTML in SQL Server?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related