Converting relational data (dynamic table) into xml for sending email in SQL Server

Question

CREATE TABLE #Temp 
( 
    [Rank] [int],
    [Player Name] [varchar](128),
    [Ranking Points] [int],
    [Country] [varchar](128)
)

INSERT INTO #Temp
    SELECT 1, 'Rafael Nadal', 12390, 'Spain'
    UNION ALL
    SELECT 2, 'Roger Federer', 7965, 'Switzerland'
    UNION ALL
    SELECT 3, 'Novak Djokovic', 7880, 'Serbia'


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
       [Ranking Points] AS 'td','', Country AS 'td'
FROM  #Temp ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1> 
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
     @body = @body,
     @body_format ='HTML',
     @recipients = 'bruhaspathy@hotmail.com', -- replace with your email address
     @subject = 'E-mail in Tabular Format' ;

I have requirement where #Temp table going to be dynamic in structure i.e. columns keeps changing. Is there anyway building @xml variables for dynamic table? Thanks in advance. Any help is highly appreciated!

In [this answer](http://stackoverflow.com/a/39487565/5089204) I provide an approach, how you can create an XHTML table out of any SELECT with full CSS/Hyperlink support as an *easy-to-call* function. — Shnugo, Apr 11 '17 at 10:03

Max Szczurek · Answer 1 · 2017-04-11T05:10:33.350

This seems to work - I added a loop through the column names, built a query string for the xml, and used sp_executesql to execute it into an output variable. I added a few columns to #Temp to test it out and make sure the headers match the table columns. (Edited to add the sp_send_dbmail call - I was able to send the email in my own SQL instance.)

CREATE TABLE #Temp
( 
  [Rank]  [int],
  [Player Name]  [varchar](128),
  [Ranking Points] [int],
  [Country]  [varchar](128),
  [Aces] [int],
  [Errors] [int],
  [Sponsor] [varchar](25)
)


INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain',2500,1200,'Adidas'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland',3200,900,'Rolex'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia',2200,1100,'Nike'

SELECT CAST(( SELECT [Rank] AS 'td',[Player Name] AS 'td',[Ranking Points] AS 'td',[Country] AS 'td' FROM  #Temp ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @headers NVARCHAR(MAX)
DECLARE @cnt INT
DECLARE @idx INT

CREATE TABLE #colnames (id int identity(1,1), name varchar(50))
INSERT INTO #colnames (name)
select name as td from tempdb.sys.columns where object_id =
object_id('tempdb..#Temp')

SELECT @cnt = COUNT(*) FROM #colnames
SET @idx = 1


SELECT @xml = 'SET @retxml = CAST(( SELECT '
SET @headers = ''
WHILE @idx < @cnt + 1
BEGIN
    SELECT @headers += '<th>' + (SELECT name FROM #colnames WHERE id = @idx) + '</th>'

    IF @idx > 1
        SELECT @xml += ','''','
    SELECT @xml += '[' + (SELECT name FROM #colnames WHERE id = @idx) + '] AS ''td'''
    SELECT @idx += 1
END
SELECT @xml += ' FROM  #Temp ORDER BY Rank 
FOR XML PATH(''tr''), ELEMENTS ) AS NVARCHAR(MAX))'

print @xml

SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1> 
<tr>' + @headers + '</tr>'    

DECLARE @parm nvarchar(500)
DECLARE @retxml xml

SET @parm = '@retxml xml OUTPUT'

exec sp_executesql @xml, @parm, @retxml OUTPUT

SET @body = @body + CAST(@retxml AS NVARCHAR(MAX)) +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
     @body = @body,
     @body_format ='HTML',
     @recipients = 'bruhaspathy@hotmail.com', -- replace with your email address
     @subject = 'E-mail in Tabular Format' ;

In [this answer](http://stackoverflow.com/a/39487565/5089204) you'll find a function, which will create an XHTML table out of any SELECT in one single call generically. — Shnugo, Apr 11 '17 at 10:04

Converting relational data (dynamic table) into xml for sending email in SQL Server

1 Answers1