Create HTML Table and specify fontsize with SQL FOR XML

Question

I need to write SQL statement that will return an html table and specify font size to its content.

I've found some information here. Solusion of this tipic describes how to get XML with elements but without attributes:

<tr>
    <th>Problem</th>
    <th>Onset</th>
    <th>Status</th>
</tr>
<tr>
    <td>aaa</td>
    <td>bbb</td>
    <td>ccc</td>
</tr>

But I need to write SQL statement that would return something like this:

<tr>
    <th><font size="1">Problem</font></th>
    <th><font size="1">Onset</font></th>
    <th><font size="1">Status</font></th>
</tr>
<tr>
    <td><font size="1">aaa</font></td>
    <td><font size="1">bbb</font></td>
    <td><font size="1">ccc</font></td>
</tr>

score 1 · Accepted Answer · answered Dec 15 '11 at 17:08

1

A couple thoughts.

1) Convert your SQL data to XML in your application, not in the query. .NET / PHP / Java all have ways to get SQL data as XML.

2) Use XSL to transform the XML from the database to HTML

3) Consider using CSS instead of <font> tags.

table td {
    FONT-SIZE: 12px;
}

answered Dec 15 '11 at 17:08

William Walseth

2,803
1
23
25

I was thinking about working with strings in my .NET code and inserting tags directy into resulting html. But it would be too comlicated because my stored procedure can return values in different formats depending on input values and consuming .NET applicaion could not even know what it receives, it just knows that this data can be shown in the embedded browser. Style sheets is a really good solution for me! Thank you very much! – Alexander Serdyuk Dec 16 '11 at 09:05

score 0 · Answer 2 · answered Dec 16 '11 at 13:44

declare @T table
(
  ProblemType varchar(10),
  Onset date,
  DiagnosisStatus varchar(10)
)

insert into @T values
(  'Ulcer',     '01/01/2008',  'Active'),
(  'Edema',     '02/02/2005',  'Active')

select 
  (select 1 as 'th/@size', 'Problem' as th for xml path(''), type),
  (select 1 as 'th/@size', 'Onset'   as th for xml path(''), type),
  (select 1 as 'th/@size', 'Status'  as th for xml path(''), type)
union all         
select 
  (select 1 as 'td/@size', p.ProblemType     as 'td' for xml path(''), type),
  (select 1 as 'td/@size', p.Onset           as 'td' for xml path(''), type),
  (select 1 as 'td/@size', p.DiagnosisStatus as 'td' for xml path(''), type)
from @T p
for xml path('tr')

Result:

<tr>
  <th size="1">Problem</th>
  <th size="1">Onset</th>
  <th size="1">Status</th>
</tr>
<tr>
  <td size="1">Ulcer</td>
  <td size="1">2008-01-01</td>
  <td size="1">Active</td>
</tr>
<tr>
  <td size="1">Edema</td>
  <td size="1">2005-02-02</td>
  <td size="1">Active</td>
</tr>

Hi, Mikael Eriksson! Thank you for your answer. When I first faced with need to change font size I was thinking about creating elements and attributes via SQL FOR XML. I think your answer is the direct solution of my task. But previous answer gave me even more than I expected. Thank all of you! — Alexander Serdyuk, Dec 23 '11 at 15:20

Create HTML Table and specify fontsize with SQL FOR XML

2 Answers2