SQL Server Performance

How to change the Table Format for FOR XML PATH('tr'), TYPE

Discussion in 'SQL Server 2005 General Developer Questions' started by yhchan2005, May 17, 2009.

  1. yhchan2005 Member

    Declare @tableHTML nVarchar(max)
    Declare @iTotal int
    Select @iTotal = Sum(Balance) from CustomerProfile
    Set @tableHTML =
    N'<h2><u>Summary For Testing</u></h2>' +
    N'<table border="1" bgcolor="white">' +
    N'<tr bgcolor="#EDFEDF">' +
    N' <th><div align=left>Code</div></th>' +
    N' <th><div align=left>Name</div></th>' +
    N' <th><div align=right>Balance</div></th>' +
    N'</tr>' +
    CAST ( ( Select td = cust.Code, '',
    td = cust.Name, '',
    td = Case when cust.Balance = 0 then '' else Cast(cust.Balance as varchar(10)) end, ''
    from CustomerProfile cust
    Order By Code, NameCode

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +

    -- footer row to display the total of Balance
    N'<tr><td/>' +
    N'<td><div align=right>Total</div></td>' +
    N'<td><div align=right>' + Cast(@iTotal as varchar(10)) + '</div></td></tr>' +
    N'</table>' +
    N'<p><I>This is computer generated email, no signature is required ! </I></p></font>';
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile',
    @recipients = 'yanhonpc@yahoo.com.hk',
    @subject = 'Test HTML',
    @body = @tableHTML,
    @body_format = 'HTML';
    /////////////////////////////////////////////////////
    in the email, above code only manage to set the header color and alignment.
    Question 1 - How can i make the table row alternate color. eg Odd row = yellow, even row = red
    Question 2 - Since the colum Balance is a float value, how can i set it to Right alignment
    Question 3 - How can i set the cell background color for the footer row with font color = white
  2. MohammedU New Member

    I don't think TSQL is the right choice for this type of report.
    Check for reporting services or crystal reports etc....
  3. yhchan2005 Member

    thanks for your comment.
    If this is the case, i prefer to use my own cursor to loop through the record 1 by 1 and generate the xml script instead of using XML path. Previously i though SQL got an easy solution for this so that is why posting message here.

Share This Page