Send email in a tabular format using SQL Server database mail

By:   |   Comments (73)   |   Related: 1 | 2 | 3 | > Database Mail


Problem

Some of our SQL Servers contain tables with summarized data (summarized on a weekly / monthly / quarterly basis). Previously this data was emailed as a flat file attachment to the recipients. I was asked to convert this in such a way that the email itself contains the table data. This way the recipients would be able to look at the data in a tabular format, making it easier for readability. Hence we arrived at the question of how to send email from SQL Server with data in a tabular format.

Solution

First let me explain the code used in this tip. 

I start by creating a temporary table and populating it with some sample data, it is a list of top Tennis players along with their Rank, Name, Ranking Points and Country. This temporary table is used only in the example, in real time the temp table would not be required, instead you would use the actual database table which contains the data.

The next section is responsible for converting the SQL Server table data that we created into a local variable "@xml".  In the select statement, each of the columns is going to be displayed as table data and rows in the email.

In another local variable "@body" we add the required HTML tags and text that is displayed in the email, in this case "Tennis Rankings Info". Then we include the column headers of the table inside the email.  Remember that these table headers (column names) can be changed as per your convenience.  In the example code, the column names of the temp table have been used as table headers. In you wish to display "Player" instead of "Player Name", you can do that by changing the table header tag to <th> Player </th>. Finally the closing HTML tags are added.

Once the HTML has been formatted I send the email using the system stored procedure sp_send_dbmail found in the msdb database and finally I drop the temporary table.

In order to use the code below, against your database table, you will have to replace the table name and column names of your table wherever necessary. Also, you will need to change the mail profile name and email address that you want to use.

Send HTML Table via SQL Server Database Mail - Example 1

Here is the sample code.

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 = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;

DROP TABLE #Temp

The HTML output from the above example looks like this:

<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>
 <tr>
  <td>1</td>
  <td>Rafael Nadal</td>
  <td>12390</td>
  <td>Spain</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Roger Federer</td>
  <td>7965</td>
  <td>Switzerland</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Novak Djokovic</td>
  <td>7880</td>
  <td>Serbia</td>
 </tr>
</table>
</body>
</html>

And this is how the email would look:

how to send an email from sql server with data in a tabular format

Send HTML Table via SQL Server Database Mail - Example 2

As another example, if you want to change this and select FirstName, LastName and EmailAddress from Person.Contact in the AdventureWorks database and order it by LastName, FirstName you would make these changes:

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

SET @xml = CAST(( SELECT [FirstName] AS 'td','',[LastName] AS 'td','', [EmailAddress] AS 'td'
FROM Person.Contact 
ORDER BY LastName, FirstName 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Contact Info</H3> <table border = 1> <tr> <th> First Name </th> <th> Last Name </th> <th> Email </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 = '[email protected]', -- replace with your email address @subject = 'E-mail in Tabular Format' ;
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bru Medishetty Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, September 29, 2023 - 10:50:56 AM - Lotus Back To Top (91610)
This is awesome, I modified the script and got the result I want at the first try; now I got another quest to give the column name an alias and Format the datetimes as mm/dd/yyyy hh:mm, I knew how to format the dattime, but I don't know how to enter in the script, can you help? Thank you.

Tuesday, September 12, 2023 - 6:02:28 PM - Kim Back To Top (91550)
This made my day. What an awesome bid of code....saved me hours. Thank you for posting!

Wednesday, January 25, 2023 - 11:47:59 AM - Kent Rice Back To Top (90854)
Bru, you made this insanely easy. Thank you for the perfect tutorial. A hundred Thank Yous!

Friday, September 3, 2021 - 12:19:03 PM - Ailton Back To Top (89204)
Hi,

All right here, it worked the first time.

From São Paulo, Brazil.

Monday, June 15, 2020 - 6:16:51 AM - Laury Back To Top (86004)

Thanks alot for this solution. awesome!!


Wednesday, November 6, 2019 - 3:59:03 PM - Dynnise E Littlefield Back To Top (82996)

This is awesome!  Slick way to send data that is in a temporary table (Example 1)!  Thanks!

Dynnise


Tuesday, July 2, 2019 - 11:27:36 AM - Joseph Morgan Back To Top (81657)

This worked very nicely for an e-mail I have to set up with a small result set included in the body. Excellent article. I would love to see you expand on the use of the @XML part, as all I really manage to understand about XML is how to spell it and the general gist. I can't make it do what I want. How did the " as 'td'" and the empty fields in between work? 

 Thank you for sharing your expertise on this.

Joey


Wednesday, August 22, 2018 - 1:53:29 PM - Sheri Back To Top (77273)

 

This tip was great.  I write code all day long in SQL, but I don't know squat about HTML. I've been struggling trying to find something that would tell me how to create my email body in a table format and this did the trick. It was very easy to understand and conform for my use.  Thanks so much for the post!


Tuesday, August 14, 2018 - 9:29:42 PM - Sean Tame Back To Top (77189)

 This is a very good and easy to follow article.  Thanks very much.

Is it possible to include hyperlinks in the SELECT data source?

I have a field similar to
LOWER('' + Click Me + '') AS [MyFieldName] and I've used that in the @xml select section but it comes out as the literal result, not the Clicke Me text as a hyperlink.

Is this possible?

Thanks!


Monday, April 23, 2018 - 9:57:52 AM - Nikaus Back To Top (75760)

 Hi,

I have been usingSQL  to send automated emails with tables to  external users. Currently the email size is exeeding the limit because of the large data in the table which cannot be shrinked  in any case. Is it advisible to create a new table ( that is continued from existing table) which will be linked to the current email table. This will help to send the complete email body without shrinking the email size. specially for oversized emails. Please advice.

 

 


Friday, December 29, 2017 - 2:57:51 PM - Angelica Back To Top (74577)

Hi, I am trying to format some emails that contain SQL database backup information.  While searching for a solution I came across your code.  It was pretty easy to manipulate for what I needed.  The only problem is, all of my table data is  coming in under one column.  Can you offer any ideas on how to fix this?

This is what I am using...

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


SET @xml = CAST((select max(backup_finish_date) as Backup_Finish_Date, substring(b.name,1,25) as "Database_Name", substring(Recovery_Model,1,14) as "TEXT", Type
                from msdb..backupset a right outer join master..sysdatabases b on a.database_name = b.name where database_name <> 'tempdb'
group by b.name, recovery_model, Type
               order by b.name, type
FOR XML PATH('tr'),ELEMENTS) AS NVARCHAR(MAX))


SET @body =''<html><body><table Border = 1>

<tr><th>Last Backup Time</th><th>Database Name</th><th>Recovery Model</th><th>Type</th>'

set @body = @body + @xml + ''</table></body></html?

 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL_Mail', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = xxxxxxxxxxx, -- replace with your email address
@subject = xxxxxxxxxxxxx' ;

 


Tuesday, December 12, 2017 - 4:41:45 PM - TartSql Back To Top (73937)

This is awesome, even after this many years your code is very useful. 

 


Thursday, October 5, 2017 - 12:31:49 PM - Terry Back To Top (66936)

I am using this method to generate a tabular form in a db email, but i cannot find how to set the column widths. How would I accomplish this? 

 


Wednesday, September 20, 2017 - 4:37:28 PM - Conrad Stanley Back To Top (66464)

 Wow! This is what Ive been looking for. I didn't know you could mash HTML into SQL.  You are a genious!! Thanks!!

 


Tuesday, August 8, 2017 - 5:14:44 AM - wong Back To Top (64289)

 hi there, this is very useful.

However,  I have the same question like Juju.

I have joint 5 DB tables to populate results in a HTML table which has 5 columns. 

When one of the columns has null value, i got blank email. Please help! thanks in advance.

 

 

 


Wednesday, March 1, 2017 - 9:37:01 PM - Dipesh Roy Back To Top (46885)

 

 Hi,

 

How can I send pdf as attachment or show portion of pdf (stored in the databas) as a query-result in the EMail to the Recipents?

 

Regards,

Dipesh Roy


Tuesday, October 11, 2016 - 7:27:20 AM - Augustine Back To Top (43535)

 Thanks for this script, it is perfect.

Is it possible that if these script is send to the various email, the sender can have a automatic return email telling him or her that _______ data was send to this customer.

 


Tuesday, September 27, 2016 - 12:39:15 AM - Santa Back To Top (43425)

Worked like a charm, thanks a lot.

 


Friday, September 2, 2016 - 10:30:54 AM - Savita Back To Top (43257)

 This is awesome! Thanks a lot!

 


Thursday, June 9, 2016 - 1:37:50 PM - Pedro Back To Top (41654)

Thanks for this post!

it is possible to format only the last row of the table to bold?


Tuesday, April 26, 2016 - 6:11:34 AM - FrankJames Back To Top (41338)

 First, great post, thanks.

i had the same limitations as Tom with the running out of rows. I changed the message variable to a varchar(max) and it seemed to correct the issue.

 

 


Monday, February 8, 2016 - 9:43:13 AM - Jan Back To Top (40622)

 Very helpful, thanks! Been searching for hours to find an easy solution that actually works. Your solution works great and fits my needs 100%..


Friday, December 4, 2015 - 3:27:44 PM - nick Back To Top (40198)

Hi, This is great post.

In my case I am checking data from yesterday in that table and if there is no data then dont send email.How do I do that? Thanks 


Wednesday, November 11, 2015 - 2:40:12 PM - vramos Back To Top (39052)

 Exelent !!

Thnks

 


Tuesday, June 30, 2015 - 7:21:28 AM - Arjun Back To Top (38078)

Hi All,

I need something like this to be sent an email. 

Date

Total_Num_OfEvents

Events_Ended

Events_InProgress

Events_NotStarted

EventState_Null

29/06/2015

58

50

1

7

0


 
Below is the sql script i have written,
 
DECLARE @ResulttableHTML  NVARCHAR(MAX) 
DECLARE @Sub varchar(500)
DECLARE @startDate VARCHAR(25)
Declare @Date VARCHAR(25)
Declare @Total_Num_OfEvents int
Declare @Events_Ended int
Declare @Events_InProgress int
Declare @Events_NotStarted int
Declare @EventState_Null int

set @startDate = (SELECT CONVERT(VARCHAR, dateadd(day,-1, cast(getdate() AS DATE))));
set @Date = @startDate
set @Total_Num_OfEvents = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_Ended = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='Ended' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_InProgress = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='In Progress' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_NotStarted = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='Not Started' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @EventState_Null = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock)WHERE event_state IS NULL AND start_datetime_tm LIKE (+'%'+@startDate+'%'));


BEGIN

SET @ResulttableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} 
table{font-size:11px; border-solid:solid;} 
td{background-color:#F1F1F1; border:1px solid black; padding:3px;} 
th{background-color:#99CCFF;}</style>' +
N'<H3>'+'Event traded statistics for'+@startDate +'</H3>' +
N'<H3>Football Production Environment</H3>' +
N'<table border="1">' +
N'<tr><th>Date</th>
<th>Total_Num_OfEvents</th>
<th>Events_Ended</th>
<th>Events_InProgress</th>
<th>Events_NotStarted</th>
<th>EventState_Null</th>
 </tr>' +
 
CAST ( ( select td=@Date,'',
td=@Total_Num_OfEvents,'',
td=@Events_Ended,'',
td=@Events_InProgress,'',
td=@Events_NotStarted,'',
td=@EventState_Null
 
  FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>'
 

SET @sub = 'Event traded statistics for'+@startDate 

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'SQLMailProfile',
  @recipients='TEst email',  
@subject = @sub,
@body = @ResulttableHTML,
@body_format = 'HTML',
@importance = 'high'
END


Could any one suggest, whther this is the right way to do it or not ?
 

Wednesday, May 27, 2015 - 11:53:13 AM - SteveG Back To Top (37288)

Really helpful thanks! I'd previously been using something similar except I used a cursor to assemble the email html a row at a time. This way is much better :-)


Monday, May 18, 2015 - 3:32:55 PM - TomG Back To Top (37210)

First - thanks for a very helpful post.

 

My question - I ran the query and formatted it as you suggested, but I can only get about 10-12 rows out in the email before the email breaks down. See below. In the last row, I get an exposed HTML tag and the email stops even though there are more rows.

 

Is this an inherent space limitation? What can I change to get all the rows?

 

Thanks,

 

Tom

Program Start Date

Missing Value Count

2012-12-27

2

2015-03-17

5

2013-09-04

1

2013-10-15

1

2014-03-12

2

2014-05-28

1

2014-10-03

1

2014-11-18

1

2014-10-27

1

2015-01-05

1

2015-01-09

1

2015-01-05

1<


Tuesday, May 5, 2015 - 8:33:13 PM - Gerardo Back To Top (37100)

Hello, I want to send an email using the databasemail..I have this query and tried to use but for some reason I cant use the distict where I put it. When I made the query on sql it worked fine. Im not an expert so I dont really know whats wrong. 

 

DECLARE @tableHTML NVARCHAR(MAX) ; 

DECLARE @s VARCHAR(max)

SET @s = 'Listado de Requisiciones Aprobadas por Gerencia, favor de revisarlas. El listado fue generado el ' +' '+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) + 

' '

       SET @tableHTML = 

    N'<html><body><h1>Listado de Requisiciones Aprobadas</h1>' + 

    N'<table border="1" width="100%">' + 

     N'<tr bgcolor="gray"><td>CODIGO_REQ</td><td>LINEA</td><td>URGENCIA</td><td>DEPARTAMENTO</td><td>NAC/IMP</td></tr>' +  

            CAST(( 

       SELECT     

       td=(distinct on T.EC_ID),'',

       td= U.LINE_NO,'',

       td=STRING_VAL,'',

       td=(SELECT     STRING_VAL

                            FROM          USER_DEF_FIELDS

                            WHERE      (PROGRAM_ID = 'VMREQENT') AND (LABEL IS NULL) AND (ID = 'UDF-0000039') AND (DOCUMENT_ID = T.EC_ID)),'',

       td=(SELECT     STRING_VAL

                            FROM          USER_DEF_FIELDS

                            WHERE      (PROGRAM_ID = 'VMREQENT') AND (LABEL IS NULL) AND (ID = 'UDF-0000018') AND (DOCUMENT_ID = T.EC_ID) AND 

                                                   (LINE_NO = PURC_REQ_LINE.LINE_NO)),''

FROM         TASK T INNER JOIN

                      USER_DEF_FIELDS U ON T.EC_ID = DOCUMENT_ID INNER JOIN

                      PURC_REQ_LINE ON U.LINE_NO = PURC_REQ_LINE.LINE_NO

WHERE     (PROGRAM_ID = 'VMREQENT') AND (ID = 'UDF-0000041') AND (T.STATUS = 'P') AND (T.SUB_TYPE = 'A2') and 

 

                    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'

                    

      

EXEC msdb.dbo.sp_send_dbmail 

    @profile_name='TEST',

    @recipients='email address',

    @subject = @s,

    @body_format = 'HTML',

    @body=@tableHTML 

    


Monday, February 23, 2015 - 8:21:44 AM - samir Back To Top (36316)
Declare @strbody as varchar(MAX)
Set @strbody = 'Dear Gilbert Kasdano, QR of NB 2219 is waiting for your approval.

QR Items

Item DescriptionQuantityPriceManufacturerAffiliate POLY 205 200 23.8 BLUESTAR SILICONES SANITA PERSONA SAL
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
@body = @strbody,
@body_format ='HTML', @recipients = '[email protected]', -- replace with your email address @subject = 'E-mail in Tabular Format' ;

Friday, February 13, 2015 - 10:16:25 AM - Jason Back To Top (36233)

Thank you, this worked perfectly for me!


Tuesday, December 2, 2014 - 5:46:04 AM - Heinrich Kruger Back To Top (35459)

HI there,

I ran 2 select statements within table format.

When I do not get any results back the mail is being sent but as a BLANK email? 

Is there a way to set default values within the table values if no results was returned?


Wednesday, October 15, 2014 - 11:52:25 AM - Dave Back To Top (34969)

Really nice example, thanks. 


Tuesday, June 10, 2014 - 7:08:12 AM - seanin Back To Top (32167)

Where in the code do you place the IF exists statement, I am having bother including it


Wednesday, March 5, 2014 - 11:04:04 PM - adi_ID Back To Top (29652)

thanks for sharing. This article is really help me out!


Thursday, December 5, 2013 - 7:39:21 AM - Adnan Back To Top (27700)

Juju,

You can use 'If Exists' sql server statement to check the records in database. Based upon the same you can edit the body of the email in the else part.

e.g.,

if exists(select)

else

set(body)

Let me know if this helps

 

 

-Adnan

 


Monday, December 2, 2013 - 2:57:56 AM - Juju Back To Top (27647)

May I know how to handle blank records?  When the output is 0, the email is also blank.  I want that a note will appear like this if result is blank "Note: If above this text you don't see table of errors logged in the system, then there is no error to report. Otherwise, please coordinate with the right teams/individuals to investigate and resolve the errors."  


Friday, May 24, 2013 - 6:49:54 PM - Greg Talbot Back To Top (25134)

Just wanted to say "thanks" for your post. This helped me make a great daily report for my business! EXACTLY what I needed. Thanks again!


Wednesday, May 1, 2013 - 10:37:59 AM - Abdul Anoop Back To Top (23659)

Hello there,

 

Wonderful stuff. I appreciate. I need a small suggestion.

 

I want the output to look like the following:

Rec:1
Player Name:Rafael Nadal Ranking Points:12390 Country:Spain

Rec:2
Player Name:Roger Federer Ranking Points:7965 Country:Switzerland

Rec:3 and so on ...
This is because I am already using one EDI file of this format to push the data to outlook(designed via C#).
if I could get this format as the output then I can use Database mail concept and sent ignore Outlook and C# Apps.

I hope you understood my query.

Thank You in Advance,
Abdul Anoop

 


Tuesday, April 23, 2013 - 9:42:06 AM - Frank Back To Top (23512)

Just what  I needed awesome post!


Tuesday, April 16, 2013 - 11:35:23 AM - vidyasagar Back To Top (23378)

Thank you very much. Its really a welldone stuff.

 


Thursday, April 4, 2013 - 2:28:10 AM - kaname Back To Top (23151)

great... :) thanks


Friday, March 22, 2013 - 2:42:58 PM - Sudhir Back To Top (22973)

 

Bru - Please let me know how to handle the blank records returning from the query. I want to get a blank table with no rows but column name should exist.

Because of the blank records, i m getting a blank report/email each time. Could someone please help me in this regard?


Wednesday, February 13, 2013 - 3:41:59 PM - UltraAdventureGuy Back To Top (22106)

As for cell alignment, I used CSS which I inserted in the @body parameter between then <html><body> tags. You also can change the fontsize and table border  this way as well

Works like a champ ....


Thursday, November 22, 2012 - 11:12:55 PM - Dinesh Back To Top (20481)

I want be mail failure notiffcaition on

  

select * from msdb.dbo.sysmail_faileditems order by  last_mod_date desc
.If suggestion.Please let me know..

Friday, September 14, 2012 - 9:21:36 AM - Jacinthe Back To Top (19512)

Thank you very much it's working just fine !!!  :)


Friday, September 7, 2012 - 4:35:46 AM - Roni Vered. Back To Top (19422)

Thanks very much, an excelent post.

Very clear, presice and helpful.

I used it in order to send myself error messages of failed jobs - modified the query to msdb views and changed the XML scheme accordingly.

works like a charm.

 

Thanks again.

Roni Vered.


Sunday, July 29, 2012 - 3:02:35 AM - eRez Back To Top (18837)

thanks - that did the job!!


Thursday, July 26, 2012 - 5:45:10 PM - mobsite Back To Top (18818)

@eRez... It's ugly, but you can order by your CASE statement.  Hope I understood correctly...

 


DECLARE @xml XML

DECLARE @T_ServerData TABLE (ServerName varchar(20),TotalRequests INT,TotalSuccessfulLoads INT)

INSERT @T_ServerData
        ( ServerName ,
          TotalRequests ,
          TotalSuccessfulLoads
        )
SELECT 'ServerZero',0,0 UNION
SELECT 'Server10',10,1 UNION
SELECT 'Server50',10,5 UNION
SELECT 'Server20',10,2 UNION
SELECT 'Server20B',100,20 UNION
SELECT 'Server10C',1000,100

SET @xml = CAST((
        SELECT  ServerName AS 'td', '',
                TotalRequests AS 'td','',              
                CASE
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END AS 'td'         
        FROM @T_ServerData
        ORDER BY CASE
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END
        FOR XML PATH('tr'), ELEMENTS )
    AS NVARCHAR(MAX))

SELECT @xml


Thursday, July 26, 2012 - 3:19:51 AM - eRez Back To Top (18805)

great post - really helped me!

however, in my specific case the 'order by' is a bit more complicated, let me know if what i'm asking for is possible at all:

lets say the table i'm selecting from has 3 fields: server name, total request, and succefull loads. 

what i want to deliver is the server name, total requests and % of successfull loads- meaning (successful-loads)/(total-request), and i want the results to be ranked by this calculation.

if i try this -

SET @xml = CAST((
        SELECT  ServerName AS 'td', '',
                TotalRequests AS 'td','',               
                CASE WHEN TotalRequests = 0 THEN 0
                ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END AS 'td'          
        FROM @T_ServerData
        ORDER BY TotalSuccessfulLoads
        FOR XML PATH('tr'), ELEMENTS )
    AS NVARCHAR(MAX))

the results are orders by the absolute number of successful loads, not the relative (%).

is there any way to order the list by the calculation?

cheers,

eRez


Friday, July 20, 2012 - 7:18:07 AM - Ravinder Pal Singh Back To Top (18690)

Many-2 Thanx Buddy... I whave to work on the same assignment. It has made my work very easy


Wednesday, July 18, 2012 - 2:20:23 AM - Senturk Bilen Back To Top (18593)

Wonderfull post. Thank you.


Friday, July 6, 2012 - 8:33:11 AM - Kemi Tobais Back To Top (18373)

I am just so relieved to to find this solution, perfect for what am looking for. To handle the null value, I used ISNULL()

 

Thanks, great tip.


Tuesday, July 3, 2012 - 8:51:13 PM - mobsite Back To Top (18334)

@DonF - the previous comment was a long delayed response to your issue of how to right align.  you're right, an extra step is required.

 


Tuesday, July 3, 2012 - 8:49:52 PM - mobsite Back To Top (18333)

-- output temporary column alias for the XML type
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',[Ranking Points] AS 'tdalignright','', 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>'

-- finally, use string function REPLACE() to change final HTML output
SET @body = REPLACE(@body,'tdalignright','td align=right')


Tuesday, July 3, 2012 - 9:24:41 AM - Aleksandar Back To Top (18320)

Hi,

This is amaizing, it's exactly what i was looking for. Everything works fine when i execute my code from the query window but there is a problem when i use the code in a trigger.

What i want to do i send an e-mail message when there is a certain update in the table but i keep getting a blank e-mail body.

I've read something about NULL values and from what i understood until now is that sometime @xml variable is returning a null value.

Is there a solution for my problem? What lines should i change?

Thanks in advance,

Aleks


Tuesday, April 24, 2012 - 10:32:23 AM - Willi Back To Top (17073)

Thanks for this tip, 'twas very helpful for me!


Wednesday, April 18, 2012 - 12:10:35 PM - Scott Back To Top (16992)

Great Post! This was very helpful.


Thursday, February 23, 2012 - 8:23:17 AM - Adrian Sullivan Back To Top (16142)

Fantastic!! Thanks Bru

It's always nice to get what you are looking for in a nice complete post.


Friday, February 17, 2012 - 7:50:29 AM - Elliott Back To Top (16050)
Brilliant post love it

Thursday, July 7, 2011 - 11:39:40 AM - DonF Back To Top (14144)

Not sure what I'm missing here, when I modified the SQL to look like this

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

I get the following error.

Msg 6850, Level 16, State 1, Line 1
Column name 'td align=right' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

 


Saturday, May 21, 2011 - 10:33:15 AM - mobsite Back To Top (13886)

@8080_Diver - It would not hurt a thing, and may even be a better method if you know that all of your columns are numeric.  However, if you do have text columns in your output, you might prefer to leave those as default (left-aligned) for readability, and only right-align  your specific numeric columns.  Strictly preference.


Saturday, May 21, 2011 - 5:44:03 AM - 8080_Diver Back To Top (13884)

@mobsite,

What will it hurt if you use the <td align=right> for all of the tags?


Wednesday, May 4, 2011 - 3:38:43 PM - Krishna Back To Top (13757)

@mobsite

Yeah I know that but was trying to see if we can add them programmatically without manually changing


Wednesday, May 4, 2011 - 3:29:57 PM - mobsite Back To Top (13756)
@Donf/@Krishna - output is html, so just modify the <td> tags for your numeric colums to be <td align=right>

Wednesday, May 4, 2011 - 3:02:45 PM - Bru Medishetty Back To Top (13755)

@ Donf and Krishna.

Since I do not write a lot of code (XML atleast) I am right now trying to figure out the code and will post another reply here as soon as I get a chance to do.

Thanks

Bru Medisetty


Wednesday, May 4, 2011 - 12:47:02 AM - Krishna Back To Top (13742)

How do we right justify the numbers ?


Tuesday, April 12, 2011 - 9:55:32 AM - DonF Back To Top (13597)

How would you right justify a numeric column, in this case the Rankings Points?  Thanks for sharing!


Tuesday, April 12, 2011 - 5:52:13 AM - RichB Back To Top (13595)

@Ryan - in 2000 you don't need to - just use sp_makewebtask and email the results.  Much easier!


Tuesday, April 12, 2011 - 12:41:48 AM - Ryan Back To Top (13593)

How would you replicate this functionality in sql2000 with the for xml explicit clause? I've had a go and didn't have alot of success.


Friday, April 1, 2011 - 2:00:12 PM - Bru Medishetty Back To Top (13414)

Great !! Appreciate your feedback Christina..


Friday, April 1, 2011 - 11:55:13 AM - Christina E. Leo Back To Top (13411)

Used this today!  Great tip, thanks!


Thursday, March 31, 2011 - 1:19:26 PM - Bru Medishetty Back To Top (13401)

Yes, I agree. The tip was written and submitted before Federer dropped to #3..


Thursday, March 31, 2011 - 12:08:57 PM - mobsite Back To Top (13399)

uh oh, better update the table... fed dropped to #3 a week or two ago!  ;)

good tip, thanx much.















get free sql tips
agree to terms