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?

Frayne

Hi, might be interesting for you: I just posted a VERSION 5 of my function (see my answer) supporting CSS classes and even hyper links dynamically. – Reshape

Answer 1

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

Sliver

Great answer, thanks! Have one issue now; what to do with a NULL date? I don't want to use XSINIL because I have a namespace to add later, and I can't seem to CASE it away because it renders as 1900-01-01T00:00:00 – Frayne

@David Walker - You can use coalesce(convert(varchar(23), p.Onset, 126), '') – Sliver

Hi, might be interesting for you: I just posted a VERSION 5 of my function (see my answer) supporting CSS classes and even hyper links dynamically. – Reshape

@MikaelEriksson anyway to append 'table' tag? – Packston

@Packston try to add , root('table') to the end of the query. – Sliver

@MikaelEriksson That worked! Additionally I am trying to create a database view this but getting an error - The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it. – Packston

Answer 2

Mikael's answer works but so will this:

Rather than using FOR XML PATH('tr'), use FOR XML RAW('tr'), ELEMENTS. This will prevent the values from being concatenated and give you very clean output. Your query would look like this:

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

I prefer to append the header row using pure markup so I can have a little better control over what is happening. The full code block would look something like this:

DECLARE @body NVARCHAR(MAX)
SET     @body = N'<table>'
    + N'<tr><th>Problem</th><th>Onset</th><th>Status</th></tr>'
    + CAST((
        SELECT  p.ProblemType AS td,
                p.Onset AS td,
                p.DiagnosisStatus AS td
        FROM    tblProblemList p
        WHERE   p.PatientUnitNumber = @PatientUnitNumber
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

EDIT

I wanted to add some extra value that I came up based on the need to format the output table.

The "AS td" alias will produce <td>value</td> elements in the markup but not because it understands that a table cell is a td. This disconnect allows us to create fake HTML elements that can be later updated after the query has been executed. For instance, if I wanted to the ProblemType value to be center aligned I can tweak the element name to allow for this. I can't add a style or class to the element name because it breaks alias naming conventions in SQL, but I can create a new element name such as tdc. This will produce <tdc>value</tdc> elements. While this is not valid markup in any way, it is easy for a replace statement to handle.

DECLARE @body NVARCHAR(MAX)
SET     @body = N'<table>'
    + N'<tr><th>Problem</th><th>Onset</th><th>Status</th></tr>'
    + CAST((
        SELECT  p.ProblemType AS tdc,
                p.Onset AS td,
                p.DiagnosisStatus AS td
        FROM    tblProblemList p
        WHERE   p.PatientUnitNumber = @PatientUnitNumber
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

SET @body = REPLACE(@body, '<tdc>', '<td class="center">')
SET @body = REPLACE(@body, '</tdc>', '</td>')

This will create cell elements with the format <td class="center">value</td>. A quick block at the top of the string and you'll have center aligned values with a simple tweak.

Another situation I needed to solve was inclusion of links in the markup. As long as the value in the cell is the value you need in the href this is pretty easy to solve. I'll expand the example to include an ID field that I want linked to a detail URL.

DECLARE @body NVARCHAR(MAX)
SET     @body = N'<table>'
    + N'<tr><th>Problem</th><th>Onset</th><th>Status</th></tr>'
    + CAST((
        SELECT  p.ID as tda
                p.ProblemType AS td,
                p.Onset AS td,
                p.DiagnosisStatus AS td
        FROM    tblProblemList p
        WHERE   p.PatientUnitNumber = @PatientUnitNumber
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

SET @body = REPLACE(@body, '<tda>', '<td><a href="http://mylinkgoeshere.com/id/')
SET @body = REPLACE(@body, '</tda>', '">click-me</a></td>')

This example doesn't account for using the value in the cell inside of the link text but that is a solvable problem with some CHARINDEX work.

My final implementation of this system was for sending HTML emails based on SQL queries. I had a repeated need for cell alignment and common link types so I moved the replace functions into a shared scalar function in SQL so I didn't have to have them in all my stored procedures that sent email.

I hope this adds some value.

Theatricalize

Hi, might be interesting for you: I just posted a VERSION 5 of my function (see my answer) supporting CSS classes and even hyper links dynamically. No textual replacements needed... – Reshape

Answer 3

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>

Reshape

no text replacements needed. Unless you have spaces in the column names. Which you should. For readability. Also I'd prefer adding a separate class to every tag using a counter in order to reach elements with the kind of limited css outlook supports. – Pursley

@Pursley See what you mean... If your table has got a column with a blank you'll get a column head like <th>user_x0020_name</th>. The easy solution was to replace all _x0020_ with a blank on string level. Don't think, that this can be done by FLWOR internally. This should be done externally on unicode base. – Reshape

yes, after converting the result to a character string and applying replace, it looks very good. – Pursley

@nurettin, I've just rewritten this answer completely with a much better approach to deal with a column's caption. – Reshape

Adding a caption attribute and checking if it exists is an excellent idea. Well done! – Pursley

This is awesome! It would only work for 2012+ correct? – Prescribe

@scsimon thx! The linked article tells us so (2012+). But I'm pretty sure this would work on 2008 too. Might be even on 2005. If you can check it out please let us know. – Reshape

You were right, at least it did work on 2008R2. If someone is on 2005 they should just upgrade haha. – Prescribe

How can I add in if condition into TR and change the row color ? – Heyerdahl

@Markov, just examine how the class warning is conditionally bound to <tr> in my final example. You can set this class to any visible format by CSS. – Reshape

Thank you @Shnugo , but I am trying to build an email HTML table through SqlMailAgent where I cannot use CSS, is there a way I could add a <tr bgcolor="#FF0000"> based on the case statement similar the way classes are done for CSS. Thank you. – Heyerdahl

@Markov Sorry, this answer is rather late... You might use the approach above and replace class="warning" with any style on string base... – Reshape

<3 <3 <3 <3 <3 <3 – Annikaanniken

6 years old and still an excellent answer! I like to think of myself as fairly experienced at working with FOR XML and I still learned a few things reading this. Deserves way more upvotes than it has. – Kilovoltampere

Answer 4

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)

Vilmavim

Answer 5

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

Encomiastic

Answer 6

Try this:

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

Cattery

This will put the <td> nodes as children to a <row> node. – Sliver

Answer 7

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

Jd

By the way, SET NOCOUNT ON is not necessary to run this code. I put it there because I was testing something else while I was writing the query – Jd

Answer 8

i prefer do this:

select 
convert(xml,
(
    select 'column1' as th,
           'column2' as th
    for xml raw('tr'),elements
)),     
convert(xml,
(
    select t1.column1 as td,
           t1.column2 as td
    from #t t1
    for xml raw('tr'),elements
))
for xml raw('table'),elements

Haircloth

what are elelments? – Bernardo

elements is a key word in sql xml: learn.microsoft.com/it-it/sql/relational-databases/xml/… – Haircloth