Создание таблицы HTML с SQL FOR XML

Ответ 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')

Ответ 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>. Хотя это недопустимая разметка каким-либо образом, легко выполнить оператор replace.

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, это довольно легко решить. Я расширю этот пример, добавив поле идентификатора, которое я хочу связать с подробным 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.

Моя последняя реализация этой системы заключалась в отправке HTML-писем на основе SQL-запросов. У меня была повторяющаяся потребность в выравнивании ячеек и общих типах ссылок, поэтому я переместил функции замены в общую скалярную функцию в SQL, поэтому мне не нужно было иметь их во всех моих хранимых процедурах, отправляющих электронную почту.

Надеюсь, это добавит некоторую ценность.

Ответ 3

Это общее решение с FUNCTION на XML -base с помощью FLWOR

Он преобразует любой 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');

Вызов --The должен заключить в себе SELECT... FOR XML !
--click запустить фрагмент, чтобы увидеть результат!

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>

Ответ 4

Все эти ответы работают нормально, но в последнее время я столкнулся с проблемой, когда мне хотелось иметь условное форматирование на html ie. Я хотел, чтобы свойство стиля 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:

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)

Ответ 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')

Ответ 6

Попробуйте следующее:

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

Ответ 7

Уже есть огромные ответы. Я просто хотел добавить, что вы также можете использовать стили в своем запросе, которые могут быть хорошими с точки зрения дизайна.

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 claus

Обратите внимание, что в запросе вы выбираете только два атрибута, вы можете добавить столько, сколько хотите, а также можете изменять стили.

Конечно, вы можете использовать стили другими способами. На самом деле, всегда лучше использовать внешний CSS, но это хорошая практика, чтобы знать, как создавать встроенные стили, потому что они могут понадобиться им

Ответ 8

Я предпочитаю сделать это:

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