add bold in for xml type

• tutupouet

  SSCommitted

  Points: 1512

  More actions

  November 15, 2011 at 9:31 am

  #250671

  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.

• drew.allen

  SSC Guru

  Points: 77011

  More actions

  November 15, 2011 at 9:40 am

  #1407996

  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

• tutupouet

  SSCommitted

  Points: 1512

  More actions

  November 16, 2011 at 1:02 am

  #1408281

  Thank Drew, it works

• Riyasat Ali

  Grasshopper

  Points: 18

  More actions

  January 19, 2013 at 2:44 am

  #1578938

  thnks Drew, it Works

• oscarooko

  SSC-Addicted

  Points: 445

  More actions

  September 19, 2013 at 12:41 pm

  #1651643

  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'