November 15, 2011 at 9:31 am
Hello,
I have a query that returns in xml format the contents of a table and I want to bold lines where run_status = 0
the initial query is :
set @tableHTML =
N'<table border="1">' +
N'<tr><th></th>' +
N'<th>srvname</th>' +
N'<th>jobname</th>' +
N'<th>date</th>' +
N'<th>duration</th>' +
N'<th>status</th></tr>' +
CAST((select
td = t.num, '',
td = t.srvname, '',
td = t.jobname, '',
td = t.run_datetime, '',
td = t.run_duration, '',
td = t.run_status
from @t t
where t.run_datetime > dateadd(dd, -1, GETDATE())
order by t.run_status, t.srvname
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>';
so I wrote
select
td = '<B>' + convert(varchar(3), t.num) + '</B>', '',
td = '<B>' + t.srvname + '</B>', '',
td = '<B>' + t.jobname + '</B>', '',
td = '<B>' + t.run_datetime + '</B>', '',
td = '<B>' + t.run_duration + '</B>', '',
td = '<B>' + convert(varchar(3), t.run_status) + '</B>'
from @t t
where t.run_datetime > dateadd(dd, -1, GETDATE())
and t.run_status = 0
order by t.run_status, t.srvname
but <B> is replaced by "& l t ; B & g t ;" (without spaces : same here I cant write it!!! :-D)
So how can I do
Thank you for your help.
November 15, 2011 at 9:40 am
It's because your '<B>' is text, not xml. The easiest way is to use the following:
select
= convert(varchar(3), t.num),'',
= t.srvname,'',
= t.jobname,'',
= t.run_datetime,'',
= t.run_duration,'',
= convert(varchar(3), t.run_status)
from @t t
where t.run_datetime > dateadd(dd, -1, GETDATE())
and t.run_status = 0
order by t.run_status, t.srvname
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2011 at 1:02 am
Thank Drew, it works
January 19, 2013 at 2:44 am
thnks Drew, it Works 🙂
September 19, 2013 at 12:41 pm
Anyone with an idea on how to format a part of a string in a table to bold.
Consider this...
CREATE TABLE [dbo].[Test_XML](
[XMLID] [int] IDENTITY(1,1) NOT NULL,
[FName] [nchar](30) NULL,
[LName] [nchar](30) NULL,
[TextBody] [xml] NULL
)
INSERT INTO [dbo].[Test_XML](FName, LName,TextBody)
VALUES ('John','Doe','This is to test Conditional Formatting of this text')
Goal is to have the string ' Conditional Formatting ' in bold in a report so that the report is like
This is to test Conditional Formatting of this string'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply