query - Crear tabla HTML con SQL FOR XML
sql server query to html table (8)
Esta es una solución genérica con FUNCTION en base de XML utilizando FLWOR
Transformará cualquier SELECT en una tabla XHTML.
Funciona (probado) con 2008R2 +, pero estoy bastante seguro de que esto funcionaría en 2008, incluso en 2005. Si alguien quiere verificar esto, por favor deje un comentario. Gracias
La siguiente función reemplaza todas las funciones que proporcioné anteriormente (consulte la versión anterior si es necesario)
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
La llamada más fácil
Una maqueta con algunos valores
DECLARE @tbl TABLE(ID INT, [Message] VARCHAR(100));
INSERT INTO @tbl VALUES
(1,''Value 1'')
,(2,''Value 2'');
--La llamada debe incluir SELECT ... FOR XML en paranthesis!
--haga clic en ejecutar fragmento para ver el resultado!
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>
Si necesita encabezados con espacios en blanco
Si su tabla contiene una columna con un espacio en blanco en su nombre , o si desea establecer el título de una columna de forma manual ( soporte de varios idiomas ), o si desea reemplazar un CamelCaseName con un subtítulo sobrescrito, puede pasar esto como atributo:
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>
Soporte completo de CSS e hipervínculos
Puede usar atributos para pasar un enlace o una clase basada en filas e incluso una basada en valores para marcar columnas e incluso celdas para el estilo de 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>
Como una posible mejora, se puede pasar en una fila de pie de página con valores agregados como parámetro adicional y anexarlo como <tfoot>
Estoy creando un documento de continuidad de atención (CCD) HL7 utilizando instrucciones FOR XML en SQL Server 2008 R2.
He hecho MUCHO con este método, pero esta es la primera vez que tengo que representar parte de los datos en una tabla HTML, lo que me está causando problemas.
Entonces, tengo la siguiente información en una tabla:
Problem | Onset | Status
---------------------------------
Ulcer | 01/01/2008 | Active
Edema | 02/02/2005 | Active
y estoy tratando de renderizar lo siguiente
<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>
Estoy usando esta consulta:
SELECT p.ProblemType AS "td"
, p.Onset AS "td"
, p.DiagnosisStatus AS "td"
FROM tblProblemList p
WHERE p.PatientUnitNumber = @PatientUnitNumber
FOR XML PATH(''tr'')
Y sigo recibiendo lo siguiente:
<tr>
<td>Ulcer2008-01-01Active</td>
</tr>
<tr>
<td>Edema2005-02-02Active</td>
</tr>
¿Alguien tiene algún consejo?
La respuesta de Mikael funciona, pero también lo hará esto:
En lugar de usar FOR XML PATH (''tr''), use FOR XML RAW (''tr''), ELEMENTS. Esto evitará que los valores se concatenen y le dará un resultado muy limpio. Su consulta se vería así:
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
Prefiero agregar la fila de encabezado con marcado puro para que pueda tener un poco mejor control sobre lo que está sucediendo. El bloque de código completo se vería así:
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>''
EDITAR
Quería agregar un valor extra que surgió en función de la necesidad de formatear la tabla de salida.
El alias "AS td" producirá elementos <td>value</td> en el marcado pero no porque entienda que una celda de tabla es un td. Esta desconexión nos permite crear elementos HTML falsos que se pueden actualizar más tarde después de que se haya ejecutado la consulta. Por ejemplo, si quería que el valor ProblemType estuviera centrado en el centro, puedo ajustar el nombre del elemento para permitir esto. No puedo agregar un estilo o clase al nombre del elemento porque rompe las convenciones de nombres de alias en SQL, pero puedo crear un nuevo nombre de elemento como tdc. Esto producirá elementos <tdc>value</tdc> . Si bien esto no es un marcado válido de ninguna manera, es fácil de manejar para una instrucción de reemplazo.
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>'')
Esto creará elementos de celda con el formato <td class="center">value</td> . Un bloque rápido en la parte superior de la cadena y tendrá valores alineados en el centro con un simple ajuste.
Otra situación que necesitaba resolver era la inclusión de enlaces en el marcado. Siempre que el valor en la celda sea el valor que necesita en el href, esto es bastante fácil de resolver. Ampliaré el ejemplo para incluir un campo de ID que quiero vincular a una URL de detalle.
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>'')
Este ejemplo no explica el uso del valor en la celda dentro del texto del enlace, pero ese es un problema que se puede solucionar con algún trabajo CHARINDEX.
Mi implementación final de este sistema fue para enviar correos electrónicos HTML basados en consultas SQL. Tenía una necesidad repetida de alineación celular y tipos de enlaces comunes, así que moví las funciones de reemplazo a una función escalar compartida en SQL, así que no tuve que tenerlas en todos mis procedimientos almacenados que enviaron correos electrónicos.
Espero que esto agregue algo de valor.
Me encontré con este problema hace un rato. Así es como lo resolví:
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'')
Prefiero hacer esto:
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
Prueba esto:
FOR XML raw, elements, root(''tr'')
Todas estas respuestas funcionan bien, pero recientemente me encontré con un problema en el que quería tener un formato condicional en html, es decir. Quería que la propiedad de estilo de la td variara en función de los datos. El formato básico es similar con la adición de la configuración 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)
Para agregar un formato condicional a esto, simplemente necesita agregar una declaración de caso:
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)
Ya hay muchas respuestas. Solo quería agregar que también puede usar estilos dentro de su consulta que podrían ser buenos en términos de diseño.
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
Donde someColumn es su atributo de su tabla
Y someTable es tu nombre de tabla
Y someCondition es opcional si está usando WHERE claus
Tenga en cuenta que la consulta solo selecciona dos atributos, puede agregar tantos como desee y también puede cambiar los estilos.
Por supuesto, puedes usar estilos de otras maneras. De hecho, siempre es mejor usar CSS externo, pero es una buena práctica saber cómo poner estilos en línea porque puede que los necesites.
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'')
Para agregar el encabezado también puedes usar 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'')