使用 SQL FOR XML 创建 HTML 表

2021-12-02 00:00:00 xml html-table tsql html sql-server

我正在使用 SQL Server 2008 R2 中的 FOR XML 语句创建 HL7 连续性护理文档 (CCD).

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

我已经用这种方法做了很多，但这是我第一次必须在 HTML 表格中表示部分数据，这给我带来了麻烦.

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>

我正在使用这个查询:

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>

有人有什么建议吗?

推荐答案

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.

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

相关文章