使用SQL FOR XML创建HTML表格

Question

我正在使用SQL Server 2008 R2中的FOR XML语句创建HL7连续护理文档（CCD）。

我已经做了很多工作，但这是我第一次需要在HTML表格中表示部分数据，这给我带来了麻烦。

因此，我有一个包含以下信息的表：

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

我正在尝试渲染以下内容

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

我正在使用以下查询：

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

我一直得到以下信息：

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

有人有什么建议吗？

- David Walker

---

嗨，这可能对你有兴趣：我刚刚发布了我的函数的版本5（请参见我的答案），支持动态CSS类和超链接。 - Shnugo

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

如果你想添加标题，你可以使用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')

- Mikael Eriksson

---

非常好的回答，谢谢！现在有一个问题；如果日期是NULL，怎么处理？我不想使用XSINIL，因为我稍后需要添加命名空间。而CASE似乎无法解决这个问题，因为它将呈现为1900-01-01T00:00:00。 - David Walker

@David Walker - 你可以使用 coalesce(convert(varchar(23), p.Onset, 126), '')。 - Mikael Eriksson

嗨，这可能对你有兴趣：我刚刚发布了我的函数的版本5（请参见我的答案），支持动态CSS类和超链接。 - Shnugo

@MikaelEriksson 有没有办法添加“table”标签？ - user793468

@user793468 尝试在查询的末尾添加 , root('table')。 - Mikael Eriksson

@MikaelEriksson 那很好用！此外，我正在尝试创建一个数据库视图，但遇到了一个错误 - 在包含集合运算符的视图、内联函数、派生表和子查询中，FOR XML子句无效。为了解决这个问题，请使用派生表语法包装包含集合运算符的SELECT，并在其上应用FOR XML。 - user793468

Answer 2

除了Mikael的答案外，还有另一种方法：

不要使用FOR XML PATH('tr')，而是使用FOR XML RAW('tr')，ELEMENTS。这将防止值被合并，并为您提供非常干净的输出。您的查询如下所示：

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

我更喜欢使用纯标记添加标题行，这样我可以对发生的情况有更好的控制。完整的代码块如下：

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

编辑

根据需要格式化输出表格的要求，我想添加一些额外的价值。

"AS td"别名将在标记中生成<td>value</td>元素，但这并不是因为它理解表格单元格是td。这种分离允许我们创建假的HTML元素，在查询执行后稍后更新它们。例如，如果我希望ProblemType值居中对齐，我可以调整元素名称以实现此目的。我不能向元素名称添加样式或类，因为这会破坏SQL中的别名命名约定，但我可以创建一个新的元素名称，如tdc。这将生成<tdc>value</tdc>元素。虽然这在任何情况下都不是有效的标记，但很容易用替换语句处理。

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

这将创建格式为 <td class="center">value</td> 的单元格元素。将字符串顶部快速块中的代码稍作修改，即可获得居中对齐的值。

另一种我需要解决的情况是在标记中包含链接。只要单元格中的值是您需要在 href 中使用的值，这就很容易解决。我将扩展示例以包括一个 ID 字段，我希望将其链接到详细信息 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>')

这个例子没有考虑将单元格中的值用作链接文本，但使用CHARINDEX可以解决这个问题。

我最终实现了基于SQL查询发送HTML电子邮件的系统。我经常需要进行单元格对齐和使用常见链接类型，因此我将替换函数移至SQL中的共享标量函数中，这样我就不必在所有发送电子邮件的存储过程中都使用它们。

希望这会增加一些价值。

- Chris Porter

---

嗨，这可能对你有兴趣：我刚刚发布了我的函数的版本5（请参见我的答案），支持动态CSS类和超链接。不需要进行文本替换... - Shnugo

Answer 3

使用基于XML和FLWOR的FUNCTION，这是一个通用解决方案，可以将任何SELECT转换为XHTML表格。

它适用于2008R2+（已测试），但我相信这在2008年可能也适用，甚至可能适用于2005年。如果有人想验证这一点，请留言。谢谢

以下函数替换了我之前提供的所有各种函数（如有需要，请参考上一个版本）

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

最简单的调用

一个带有一些值的模拟表格

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

--调用必须在括号内包含 SELECT ... FOR XML！
--点击运行片段以查看结果！

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>

如果您需要带空格的标题

如果您的表格包含一个名称中有空格的列，或者您想手动设置列的标题（多语言支持！），或者您想用描述性文字替换驼峰命名法的名称，您可以将其传递为属性：

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>

完全支持CSS和超链接

您可以使用属性传递链接、基于行或基于值的类，甚至可以标记列甚至单元格以进行CSS样式设置。

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

作为一个可能的增强功能，可以传入一个一行页脚作为额外参数，并将其附加为<tfoot>

- Shnugo

---

@nurettin，我刚刚完全重写了这个答案，并采用了更好的方法来处理列标题。 - Shnugo

添加说明属性并检查其是否存在是一个非常好的想法。做得好！ - nurettin

@Markov，看一下我的最终示例中类warning如何有条件地绑定到<tr>。你可以通过CSS将此类设置为任何可见格式。 - Shnugo

@Markov 抱歉，这个回答有点晚了... 你可以使用上面的方法，并将 class="warning" 替换为任何基于字符串的样式... - Shnugo

6岁了，仍然是一个出色的答案！我认为自己在使用FOR XML方面还算经验丰富，但是阅读这篇文章时我仍然学到了一些东西。它应该获得更多赞。 - Nick Fotopoulos

Answer 4

所有这些答案都很好，但最近我遇到了一个问题，我想在html上进行条件格式设置，即我希望基于数据使td的样式属性有所不同。基本格式类似，只需添加设置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)

要添加条件格式，您只需要添加一个case语句：

```html

要添加条件格式，您只需要添加一个case语句：

```

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)

- CCarter

Answer 5

我曾经遇到过这个问题。以下是我解决它的方法：

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

- pd1138

Answer 6

试试这个：

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

- Chains

---

这将把<td>节点作为<row>节点的子节点。 - Mikael Eriksson

Answer 7

我更喜欢这样做：

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

- elle0087

---

什么是元素？ - Vernard Sloggett

elements是SQL XML中的关键字：https://learn.microsoft.com/it-it/sql/relational-databases/xml/example-specifying-xsinil-with-the-elements-directive?view=sql-server-2017 - elle0087

Answer 8

已经有很多答案了。我只想补充一点，您也可以在查询中使用样式，这在设计方面可能会更好。

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

其中someColumn是您表中的属性

someTable是您的表名

someCondition是可选的，如果您使用WHERE子句

请注意，查询仅选择两个属性，您可以添加任意数量的属性，并且还可以更改样式。

当然，您可以以其他方式使用样式。实际上，最好始终使用外部CSS，但了解如何放置内联样式是一种良好的实践，因为您可能需要它们。

- Ahmad Shli

---

顺便说一下，运行此代码不需要使用 SET NOCOUNT ON。我之所以将其放在那里，是因为在编写查询时我正在测试其他内容。 - Ahmad Shli