Solved

adding color format into FOR XML PATH query

Posted on 2011-03-09
2
2,316 Views
Last Modified: 2012-06-21
how can you insert colors into the XML path query where HTML may have the format like

<table border="1">
  <tr style="color:green;background-color:purple;">
    <th>Column1</th>
    <th>Column2</th>
    <th>Column3</th>
    <th>Column4</th>
  </tr>
  <tr>
    <td style="color:white;background-color:black;">1</td>    
    <td style="color:white;background-color:black;">text2</td>
    <td style="color:white;background-color:black;">text3</td>    
    <td style="color:white;background-color:black;">text4</td>
  </tr>
  <tr>
    <td><span style="color:red;background-color:yellow;">2</span></td>
    <td><span style="color:red;background-color:yellow;">code2</span></td>
    <td><span style="color:red;background-color:yellow;">code3</span></td>
    <td><span style="color:red;background-color:yellow;">code4</span></td>
  </tr>
</table>'
create table #test (column1 int, column2 text, column3 text, column4 text)
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'


DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)

SET @title = 'Summary Report - FunctionCode'
SET @tableHTML =
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr style="color:green;background-color:purple;">' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>' + 
     CAST(
            (SELECT
                  td = Column1 ,'',
                  td = Column2,'',
                  td = Column3,'',
                  td = Column4,''
            FROM  #test WHERE column1 in (1,2)
            FOR XML PATH('tr'), TYPE) 
	 AS NVARCHAR(MAX) ) 
	 + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'mail_profile',
            @recipients='user@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

drop table #test
--(Code courtesy: lcohan)

Open in new window

0
Comment
Question by:anushahanna
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35097004
Sorry took me a while to get you that.. here is a sample script to include font/color:

create table #test (column1 int, column2 text, column3 text, column4 text)
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'


DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:12px; border-collapse:collapse;}
td{background-color:#F1F1F1; border:1px solid black; padding:4px;} th{background-color:#99CCFF; padding:4px;}
h4{font-family: "Courier New", Courier, monospace; font-size: 11px;} </style>'


SET @title = 'Summary Report - FunctionCode'
SET @tableHTML = @tableHTML +
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr style="color:green;background-color:purple;">' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>' +
     CAST(
            (SELECT
                  td = Column1 ,'',
                  td = Column2,'',
                  td = Column3,'',
                  td = Column4,''
            FROM  #test WHERE column1 in (1,2)
            FOR XML PATH('tr'), TYPE)
       AS NVARCHAR(MAX) )
       + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'mail profile',
            @recipients='user@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

drop table #test
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35098536
thanks very much, lcohan.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now