使用 SQL FOR XML 创建 HTML 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7086393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create HTML Table with SQL FOR XML
提问by David Walker
I'm creating a HL7 Continuity of Care Document (CCD) using FOR XML statements in SQL Server 2008 R2.
我正在使用 SQL Server 2008 R2 中的 FOR XML 语句创建 HL7 连续性护理文档 (CCD)。
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.
我已经用这种方法做了很多，但这是我第一次必须在 HTML 表中表示部分数据，这给我带来了麻烦。
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?
有人有什么建议吗？
回答by Mikael Eriksson
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.
要添加标题，您也可以使用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')
回答by Chris Porter
Mikael's answer works but so will this:
Mikael 的回答有效，但这也有效：
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:
不要使用 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
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.
“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>')
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.
这将创建格式为 的单元格元素<td class="center">value</td>。字符串顶部的快速块，您将通过简单的调整获得居中对齐的值。
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.
我需要解决的另一种情况是在标记中包含链接。只要单元格中的值是您在 href 中需要的值，这很容易解决。我将扩展示例以包含我想要链接到详细 URL 的 ID 字段。
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.
此示例未考虑在链接文本内的单元格中使用该值，但这是一些 CHARINDEX 工作的可解决问题。
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.
我对这个系统的最终实现是基于 SQL 查询发送 HTML 电子邮件。我反复需要单元格对齐和通用链接类型，因此我将替换函数移动到 SQL 中的共享标量函数中，这样我就不必在发送电子邮件的所有存储过程中都使用它们。
I hope this adds some value.
我希望这会增加一些价值。
回答by Shnugo
This is a generic solution with a FUNCTIONon XML-base using FLWOR
这是使用FLWOR的FUNCTION基于基础的通用解决方案XML
It will transform any SELECTinto a XHTML table.
它会将 anySELECT转换为 XHTML 表。
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
它适用于（测试）2008R2+，但我很确定这适用于 2008 年，甚至可能适用于 2005 年。如果有人想验证这一点，请发表评论。谢谢
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 XMLin paranthesis!
--click run snippetto see the result!
--调用必须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>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 CamelCaseNamewith an out-written caption, you can pass this as attribute:
如果您的表格包含名称中带有空白的列，或者您想手动设置列的标题（多语言支持！），或者您想用写出的标题替换CamelCaseName，您可以将其作为属性：
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
完整的 CSS 支持和超链接
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.
您可以使用属性来传递链接或基于行甚至基于值的类来标记列甚至单元格的 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>As a possible enhancement one might pass in a one-row-footerwith aggregated values as additional parameter and append it as <tfoot>
作为一种可能的增强，可以传入一个带有聚合值的单行页脚作为附加参数，并将其附加为<tfoot>
回答by CCarter
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 = :
所有这些答案都很好，但我最近遇到了一个问题，我想在 html 上设置条件格式，即。我希望 td 的 style 属性根据数据而变化。基本格式类似，增加了设置 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:
要为此添加条件格式，您只需添加一个 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)
回答by pd1138
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')
回答by Chains
Try this:
尝试这个：
FOR XML raw, elements, root('tr')
回答by Ahmad Shli
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 someColumnis your attribute from your table
someColumn您的表中的属性在哪里
And someTableis your table name
而且someTable是你的表名
And someConditionis optional if you are using WHEREclaus
someCondition如果您使用WHEREclaus，并且是可选的
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
当然，您可以通过其他方式使用样式。事实上，使用外部 CSS 总是更好，但知道如何放置内联样式是一个很好的做法，因为您可能需要它们
回答by elle0087
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
