selectfor XML Path('tr') - conditional formatting

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

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.

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

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?

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

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

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>' ;

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,'<','<'),'>','>')

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')

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources