Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 selectfor XML Path('tr') - conditional formatting

Author  Topic 

Mathias
Posting Yak Master

119 Posts

Posted - 2008-01-31 : 09:34:30
select case when '1'='1' then '<font color="green">1<font/>' else '2' end for XML PATH('tr')

doesn't give me : <tr><font color="green">1<font/></tr>

but something else that I cannot paste here properly.

Any idea on how to encode the < character in the result?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-31 : 09:40:57
for xml path will encode < to < and > to >
an also any other special char that needs encoding.
that's the way it's supposed to work.

and why are you creating html in sql?


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2008-01-31 : 09:47:22
I need to send an email with msdb.dbo.sp_send_dbmail the example is coming from SQL 2005 book on line.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-31 : 09:50:06
and your problem is what exactly?


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2008-01-31 : 09:56:20
I would like to be able to add extra HTML formating in the query but I cannot because the < tags are automatically transformed. Is there a way to add them with a specific encoding method in order to get <tr><font color="green">1<font/></tr> as result?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-31 : 10:08:21
put the xml in a variable and do a replace on it.

but i think that you don't have to do any replacing to have a properly formatted mail sent.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2008-01-31 : 10:40:23
In fact it is pretty easy, I have just done two replace to change the characters back to what it should be.
Thanks
Go to Top of Page

jasmimi
Starting Member

1 Post

Posted - 2008-02-14 : 13:07:21
I have a similar problem I'm working on however it's slightly different from yours. First I need to check the data return from the select statement, and depending on the data, I need to change certain row background color. But I'm not exactly sure where and what I should do the checking data and changing row color. PLEASE HELP ME!!~

This is what I have so far:
SET @tableHTML =
N'<H1>Status Brief Report</H1>' +
N'<b>This is what I have so far. I will start working on the formatting.<b>' +
N'<table border="1">' +
N'<tr><th>Customer</th>' +
N'<th>Bank</th>' +
N'<th>Safe</th>' +
N'<th>Enabled</th>' +
N'<th>Poll Time</th>' +
N'<th>Data In CPR</th>' +
N'<th>Edge Matches CPR</th></tr>' +
CAST ( ( SELECT td = Customer, '',
td = Bank, '',
td = Safe, '',
td = Enabled, '',
td = [Poll Time], '',
td = [Data In CPR], '',
td = [Edge Matches CPR], ''
FROM #tempBrief
ORDER BY Customer, Bank, Safe
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2008-10-14 : 04:38:58
Here is what I have done

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML = ...myHTML (done with FOR XML PATH('tr'), TYPE)
Set @tableHTML=replace(replace(@tableHTML,'<','<'),'>','>')

Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-14 : 08:12:21
Wouldn't it be easier to cast the XHTML fragment as XML rather than try to treat it as text and patch it up afterwards? Then you'd find out sooner that you meant to type "</font>" instead of "<font/>"!

select cast(case when '1'='1' then '<font color="green">1</font>' else '2' end as xml) for XML PATH('tr')
Go to Top of Page

kishore_pen
Starting Member

49 Posts

Posted - 2009-06-10 : 09:14:58
see the below link for your query:

http://grounding.co.za/blogs/romiko/archive/2008/09/21/generating-professional-email-notifications-with-sql.aspx
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 2011-04-25 : 01:47:30
quote:
Originally posted by jasmimi

I have a similar problem I'm working on however it's slightly different from yours. First I need to check the data return from the select statement, and depending on the data, I need to change certain row background color. But I'm not exactly sure where and what I should do the checking data and changing row color. PLEASE HELP ME!!~

This is what I have so far:
SET @tableHTML =
N'<H1>Status Brief Report</H1>' +
N'<b>This is what I have so far. I will start working on the formatting.<b>' +
N'<table border="1">' +
N'<tr><th>Customer</th>' +
N'<th>Bank</th>' +
N'<th>Safe</th>' +
N'<th>Enabled</th>' +
N'<th>Poll Time</th>' +
N'<th>Data In CPR</th>' +
N'<th>Edge Matches CPR</th></tr>' +
CAST ( ( SELECT td = Customer, '',
td = Bank, '',
td = Safe, '',
td = Enabled, '',
td = [Poll Time], '',
td = [Data In CPR], '',
td = [Edge Matches CPR], ''
FROM #tempBrief
ORDER BY Customer, Bank, Safe
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;



on this example how i can set that few columns will have red font?
THX
Go to Top of Page
   

- Advertisement -