Create HTML Table with SQL FOR XML

Question

I'm creating a HL7 Continuity of Care Document (CCD) using FOR XML statements in SQL Server 2008 R2.

I've done A LOT with this method, but this is the first time I have to represent part of the data in a HTML table, which is giving me trouble.

So, I have the following information in a table:

  Problem  |   Onset    | Status
---------------------------------
  Ulcer    | 01/01/2008 | Active
  Edema    | 02/02/2005 | Active

and I'm trying to render the following

<tr>
    <th>Problem</th>
    <th>Onset</th>
    <th>Status</th>
</tr>
<tr>
    <td>Ulcer</td>
    <td>01/01/2008</td>
    <td>Active</td>
</tr>
<tr>
    <td>Edema</td>
    <td>02/02/2005</td>
    <td>Active</td>
</tr>

I'm using this query:

SELECT    p.ProblemType AS "td"
    , p.Onset AS "td"
    , p.DiagnosisStatus AS "td"
FROM tblProblemList p
WHERE p.PatientUnitNumber = @PatientUnitNumber
FOR XML PATH('tr')

And I keep getting the following:

<tr>
  <td>Ulcer2008-01-01Active</td>
</tr>
<tr>
  <td>Edema2005-02-02Active</td>
</tr>

Anyone got any advice?

Answer 1

All these answers work fine but I ran into a problem recently where I wanted to have conditional formatting on the html ie. I wanted the style property of the td to vary based on data. The basic format is similar with the addition of setting td = :

declare @body nvarchar(max)
set @body = 
cast
(select 
'color:red' as 'td/@style', td = p.ProblemType, '',
td = p.Onset, '',
td = p.DiagnosisStatus, ''
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr'), type)
as nvarchar(max)

To add in conditional formatting to this you simply need to add a case statement:

declare @body nvarchar(max)
set @body = 
cast
select 
cast (case 
when p.ProblemType = 1 then 'color:#ff0000;'
else 'color:#000;'
end as nvarchar(30)) as 'td/@style',
td = p.ProblemType, '',
td = p.Onset, '',
td = p.DiagnosisStatus, ''
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr'), type)
as nvarchar(max)

Answer 2

There are a tremendous answers already. I just wanted to add that you can also use styles within your query which might be a good in terms of design.

BEGIN
  SET NOCOUNT ON;
  DECLARE @htmlOpenTable VARCHAR(200) = 
     '<table style="border-collapse: collapse; border: 1px solid #2c3e50; background-color: #f9fbfc;">'
  DECLARE @htmlCloseTable VARCHAR(200) = 
     '</table>'
  DECLARE @htmlTdTr VARCHAR(max) = (        
    SELECT 
       'border-top: 1px solid #2c3e50' as [td/@style], someColumn as td, '',
       'border-top: 1px solid #2c3e50' as [td/@style], someColumn as td, ''
    FROM someTable
    WHERE someCondition
    FOR XML PATH('tr')
  )
  SELECT @htmlOpenTable + @htmlTdTr + @htmlCloseTable
END

Where someColumn is your attribute from your table

And someTable is your table name

And someCondition is optional if you are using WHERE claus

Please note that the query is only selecting two attributes, you can add as many as you want and also you can change on the styles.

Of course you can use styles in other ways. In fact, it is always better to use external CSS, but it is a good practice to know how to put inline styles because you might need them

Answer 3

I ran into this problem awhile ago. Here is how I solved it:

SELECT
p.ProblemType AS "td"
, '' AS "text()"
, p.Onset AS "td"
, '' AS "text()"
, p.DiagnosisStatus AS "td"

FROM tblProblemList p
WHERE p.PatientUnitNumber = @PatientUnitNumber
FOR XML PATH('tr')

Answer 4

This is a generic solution with a FUNCTION on XML-base using FLWOR

It will transform any SELECT into a XHTML table.

It works (tested) with 2008R2+, but I'm pretty sure this would work on 2008, might be even on 2005, too. If someone wants to verify this, please leave a comment. Thx

The following function replaces all the various functions I provided before (see the previous version if needed)

CREATE FUNCTION dbo.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[1]
                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

The easiest call

A mock-up table with some values

DECLARE @tbl TABLE(ID INT, [Message] VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'Value 1')
,(2,'Value 2');

--The call must enclose the SELECT ... FOR XML in paranthesis!
--click run snippet to see the result!

SELECT dbo.CreateHTMLTable
(
     (SELECT * FROM @tbl FOR XML PATH('row'),ELEMENTS XSINIL)
     ,NULL,NULL,NULL
);

    <table>
	  <thead>
		<tr>
		  <th>ID</th>
		  <th>Message</th>
		</tr>
	  </thead>
	  <tbody>
		<tr>
		  <td>1</td>
		  <td>Value 1</td>
		</tr>
		<tr>
		  <td>2</td>
		  <td>Value 2</td>
		</tr>
	  </tbody>
	</table>

If you need headers with blanks

If your table contains a column with a blank in its name, or if you want to set a column's caption manually (multi langugage support!), or if you want to replace a CamelCaseName with an out-written caption, you can pass this as attribute:

DECLARE @tbl2 TABLE(ID INT, [With Blank] VARCHAR(100));
INSERT INTO @tbl2 VALUES
 (1,'Value 1')
,(2,'Value 2');

SELECT dbo.CreateHTMLTable
(
     (
     SELECT ID
           ,'The new name' AS [SomeOtherName/@caption] --set a caption 
           ,[With Blank] AS [SomeOtherName] 
     FROM @tbl2 FOR XML PATH('row'),ELEMENTS XSINIL
     )
     ,NULL,NULL,NULL
);

	<table>
	  <thead>
		<tr>
		  <th>ID</th>
		  <th>The new name</th>
		</tr>
	  </thead>
	  <tbody>
		<tr>
		  <td>1</td>
		  <td>Value 1</td>
		</tr>
		<tr>
		  <td>2</td>
		  <td>Value 2</td>
		</tr>
	  </tbody>
	</table>

Full CSS-support and hyper-links

You can use attributes to pass over a link or a row-based and even a value-based class to mark columns and even cells for CSS styling.

--a mock-up table with a row based condition and hyper-links

DECLARE @tbl3 TABLE(ID INT, [With blank] VARCHAR(100),Link VARCHAR(MAX),ShouldNotBeNull INT);
INSERT INTO @tbl3 VALUES
 (1,'NoWarning',NULL,1)
,(2,'No Warning too','http://www.Link2.com',2)
,(3,'Warning','http://www.Link3.com',3)
,(4,NULL,NULL,NULL)
,(5,'Warning',NULL,5)
,(6,'One more warning','http://www.Link6.com',6);
--The query adds an attribute Link to an element (NULL if not defined)
SELECT dbo.CreateHTMLTable
(
     (
     SELECT 
       CASE WHEN LEFT([With blank],2) != 'No' THEN 'warning' ELSE NULL END AS [@class]      --The first @class is the <tr>-class
      ,ID
      ,'center' AS [Dummy/@class]                                                    --a class within TestText (appeary always)
      ,Link AS [Dummy/@link]                                                         --a mark to pop up as link
      ,'New caption' AS [Dummy/@caption]                                             --a different caption
      ,[With blank] AS [Dummy]                                                       --blanks in the column's name must be tricked away...
      ,CASE WHEN ShouldNotBeNull IS NULL THEN 'MarkRed' END AS [ShouldNotBeNull/@class] --a class within ShouldNotBeNull (appears only if needed)
      ,'Should not be null' AS [ShouldNotBeNull/@caption]                             --a caption for a CamelCase-ColumnName
      ,ShouldNotBeNull
     FROM @tbl3 FOR XML PATH('row'),ELEMENTS XSINIL),'testTbl','testTh','testTb'
);

<style type="text/css" media="screen,print">
.center
{
    text-align: center;
}
.warning
{
    color: red;
}
.MarkRed
{
    background-color: red;
}
table,th
{
	border: 1px solid black;
}
</style>
<table class="testTbl">
  <thead class="testTh">
    <tr>
      <th>ID</th>
      <th>New caption</th>
      <th>Should not be null</th>
    </tr>
  </thead>
  <tbody class="testTb">
    <tr>
      <td>1</td>
      <td class="center">NoWarning</td>
      <td>1</td>
    </tr>
    <tr>
      <td>2</td>
      <td class="center">
        <a href="http://www.Link2.com">No Warning too</a>
      </td>
      <td>2</td>
    </tr>
    <tr class="warning">
      <td>3</td>
      <td class="center">
        <a href="http://www.Link3.com">Warning</a>
      </td>
      <td>3</td>
    </tr>
    <tr>
      <td>4</td>
      <td class="center" />
      <td class="MarkRed" />
    </tr>
    <tr class="warning">
      <td>5</td>
      <td class="center">Warning</td>
      <td>5</td>
    </tr>
    <tr class="warning">
      <td>6</td>
      <td class="center">
        <a href="http://www.Link6.com">One more warning</a>
      </td>
      <td>6</td>
    </tr>
  </tbody>
</table>

As a possible enhancement one might pass in a one-row-footer with aggregated values as additional parameter and append it as <tfoot>

Answer 5

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

Answer 6

Try this:

FOR XML raw, elements, root('tr')