Create HTML Table with SQL FOR XML
select
(select p.ProblemType as 'td' for xml path(''), type),
(select p.Onset as 'td' for xml path(''), type),
(select p.DiagnosisStatus as 'td' for xml path(''), type)
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr')
To add the header as well you can use union all.
select
(select 'Problem' as th for xml path(''), type),
(select 'Onset' as th for xml path(''), type),
(select 'Status' as th for xml path(''), type)
union all
select
(select p.ProblemType as 'td' for xml path(''), type),
(select p.Onset as 'td' for xml path(''), type),
(select p.DiagnosisStatus as 'td' for xml path(''), type)
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr')
Create HTML Table from XML in SQL Server
Try something like this:
DECLARE @Data TABLE ( Id INT, TableData XML );
INSERT INTO @Data ( Id, TableData ) VALUES
( 1, '<root><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /></root>' ),
( 2, '<root><row RowId="225936" Jobsitestate="MD" Jobsitezipcode="20762" Objective="Target" /></root>' );
SELECT
Id,
( '<table>' + TableHeaders.th + TableRows.td + '</table>' ) AS HtmlTable
FROM @Data
CROSS APPLY (
SELECT (
SELECT
'<tr><th>' + STRING_AGG( attr.val.value( 'local-name(.)', 'VARCHAR(50)' ), '</th><th>' ) + '</th></tr>'
FROM TableData.nodes( '//root/row[1]' ) AS rw( fld )
CROSS APPLY fld.nodes( '@*' ) attr( val )
) AS th
) AS TableHeaders
CROSS APPLY (
SELECT (
SELECT
STRING_AGG( '<tr>' + tds.td + '</tr>', '' ) AS rw
FROM TableData.nodes( '//root/row' ) AS rw( fld )
CROSS APPLY (
SELECT
'<td>' + STRING_AGG( attr.val.value( '.', 'VARCHAR(50)' ), '</td><td>' ) + '</td>' AS td
FROM fld.nodes( '@*' ) attr( val )
) AS tds
) AS td
) AS TableRows
ORDER BY
Id;
Returns
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | HtmlTable |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | <table><tr><th>RowId</th><th>Attendance</th><th>Jobsitecity</th></tr><tr><td>225936</td><td>250</td><td>Camp Springs</td></tr><tr><td>225936</td><td>250</td><td>Camp Springs</td></tr></table> |
| 2 | <table><tr><th>RowId</th><th>Jobsitestate</th><th>Jobsitezipcode</th><th>Objective</th></tr><tr><td>225936</td><td>MD</td><td>20762</td><td>Target</td></tr></table> |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Id 1 HTML Formatted:
<table>
<tr>
<th>RowId</th>
<th>Attendance</th>
<th>Jobsitecity</th>
</tr>
<tr>
<td>225936</td>
<td>250</td>
<td>Camp Springs</td>
</tr>
<tr>
<td>225936</td>
<td>250</td>
<td>Camp Springs</td>
</tr>
</table>
Id 2 HTML Formatted
<table>
<tr>
<th>RowId</th>
<th>Jobsitestate</th>
<th>Jobsitezipcode</th>
<th>Objective</th>
</tr>
<tr>
<td>225936</td>
<td>MD</td>
<td>20762</td>
<td>Target</td>
</tr>
</table>
Note:
I added an extra row to Id 1 to show how this handles multiple rows.
How to convert xml as a table or html table in sqlserver
You find a much enhanced version of this answer here.
old answer:
because I don't know the column name when it pass as parameters
This makes it impossible, to call something like SELECT * FROM... You might think about dynamic SQL, but there is a great alternative: FLWOR
declare @tv table
(
id int,
username varchar(50),
department varchar(50)
)
insert into @tv values(1,'tom','finance'),(2,'mark','business');
SELECT
(
SELECT *
FROM @tv
FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
return <tr>
{
for $td in $tr/*
return <td>{$td/text()}</td>
}
</tr>')
FOR XML PATH('table')
The .query() will run through your XML and re-create it as demanded.
The result:
<table>
<tr>
<td>1</td>
<td>tom</td>
<td>finance</td>
</tr>
<tr>
<td>2</td>
<td>mark</td>
<td>business</td>
</tr>
</table>
##UPDATE
##This is a solution with a FUNCTION on XML-base using FLWOR
It will transform any SELECT into a XHTML table: The call is as easy as this:
SELECT dbo.CreateHTMLTable((SELECT TOP 5 * FROM sys.objects FOR XML RAW,ELEMENTS XSINIL));
That's the code
CREATE FUNCTION dbo.CreateHTMLTable(@SelectForXmlRawElementsXsinil XML)
RETURNS XML
AS
BEGIN
RETURN
(
SELECT
@SelectForXmlRawElementsXsinil.query('let $first:=/row[1]
return
<tr>
{
for $th in $first/*
return <td>{local-name($th)}</td>
}
</tr>') AS thead
,@SelectForXmlRawElementsXsinil.query('for $tr in /row
return
<tr>
{
for $td in $tr/*
return <td>{string($td)}</td>
}
</tr>') AS tbody
FOR XML PATH('table'),TYPE
);
END
GO
--a mock-up-table with data
declare @tv table
(
id int,
username varchar(50),
department varchar(50)
)
--NULL value in row=2!!!
insert into @tv values(1,'tom','finance'),(2,NULL,'business');
--That's the way you use it
SELECT dbo.CreateHTMLTable((SELECT * FROM @tv FOR XML RAW,ELEMENTS XSINIL));
--Clean-Up
DROP FUNCTION dbo.CreateHTMLTable;
returns be aware of the NULL value in last row!
<table>
<thead>
<tr>
<td>id</td>
<td>username</td>
<td>department</td>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>tom</td>
<td>finance</td>
</tr>
<tr>
<td>2</td>
<td />
<td>business</td>
</tr>
</tbody>
</table>
##UPDATE Possible Enhancements
- You can control the layout easily via
CSS - You might pass in class names for
table,thead,tbody... for better CSS-control - One could pass in a one-row-footer with aggregated values as second parameter and append it as
<tfoot>
Create HTML Table and specify fontsize with SQL FOR XML
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;
}
Convert SQL Server table into HTML using TSQL
There is a script created by Shnugo which can be found here with his comments, and methods to call it. I specifically use to build a HTML table when I need to send results with DBMAIL.
Here's how
/************************************************************************************************
Function to build a HTML table.
Created by Shnugo on SO: https://stackoverflow.com/a/39487565/6167855
Usage:
declare @body xml =
(select AdminTools.dbo.ufn_CreateHTMLTable (
(select * from SomeTable for xml path ('row'),elements xsinil)
,null,null,null))
--CSS to make borders, which makes us change datatype
declare @body_html varchar(max)
set @body_html =
'<style type="text/css" media="screen,print">
.center
{
text-align: center;
}
table,th
{
border: 1px solid black;
}
table,tr
{
border: 1px solid black;
}
table,td
{
border: 1px solid black;
}
</style>'
+ cast(@body as varchar(max))
Then @body_html for sp_send_dbmail
************************************************************************************************/
CREATE FUNCTION [dbo].[ufn_CreateHTMLTable]
(
@SelectForXmlPathRowElementsXsinil XML
,@tblClass VARCHAR(100) --NULL to omit this class
,@thClass VARCHAR(100) --same
,@tbClass VARCHAR(100) --same
)
RETURNS XML
AS
BEGIN
RETURN
(
SELECT @tblClass AS [@class]
,@thClass AS [thead/@class]
,@SelectForXmlPathRowElementsXsinil.query(
N'let $first:=/row[2]
return
<tr>
{
for $th in $first/*
return <th>{if(not(empty($th/@caption))) then xs:string($th/@caption) else local-name($th)}</th>
}
</tr>') AS thead
,@tbClass AS [tbody/@class]
,@SelectForXmlPathRowElementsXsinil.query(
N'for $tr in /row
return
<tr>{$tr/@class}
{
for $td in $tr/*
return
if(empty($td/@link))
then <td>{$td/@class}{string($td)}</td>
else <td>{$td/@class}<a href="{$td/@link}">{string($td)}</a></td>
}
</tr>') AS tbody
FOR XML PATH('table'),TYPE
)
END
GO
How do I modify my XML codes in this SQL Query to highlight specific rows of the table?
We can add this column -
(case when [PropertyCode]='A' then 'background-color:green' when [PropertyCode]='B' then 'background-color:blue' end) as '@style'
after this column (Line 51 of SQL script) -
select 'trclass' as '@class'
I have added case only for Property code A and B. We can add same for other Property codes.
Convert a SQL query result table to an HTML table for email
Here is one way to do it from an article titled "Format query output into an HTML table - the easy way [archive]". You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.
declare @body varchar(max)
set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
from (
select dbtable = object_name( object_id ),
entities = count( distinct name ),
rows = count( * )
from sys.columns
group by object_name( object_id )
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = '<table cellpadding="2" cellspacing="2" border="1">'
+ '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '</table>'
print @body
Once you have @body, you can then use whatever email mechanism you want.
Related Topics
Alternate Table Row Color Using Css
How to Disable the Resizable Property of a Textarea
Center a Div Horizontally and Vertically
Maintain Aspect Ratio of Div But Fill Screen Width and Height in Css
Uri Starting With Two Slashes ... How Do They Behave
How to Customize the Numbers in an Ordered List
How to Prevent Column Break Within an Element
What Is Href="#" and Why Is It Used
How to Escape Hash Character in Url
Implement an Input With a Mask
Css - How to Style a Selected Radio Buttons Label
Force Flex Item to Span Full Row Width
Why Does HTML Require That Multiple Spaces Show Up as a Single Space in the Browser
Why Is a Div With "Display: Table-Cell;" Not Affected by Margin