Skip to content

Instantly share code, notes, and snippets.

@karoltheguy
Created May 24, 2017 22:03
Show Gist options
  • Save karoltheguy/daaab621652ed21233e635aa5cae093b to your computer and use it in GitHub Desktop.
Save karoltheguy/daaab621652ed21233e635aa5cae093b to your computer and use it in GitHub Desktop.
Make HTML table with SQL result
CREATE FUNCTION dbo.CreateHTMLTable(@SelectForXmlRawElementsXsinil XML)
RETURNS XML
AS
BEGIN
RETURN
(
SELECT
@SelectForXmlRawElementsXsinil.query('let $first:=/row[1]
return
<tr>
{
for $th in $first/*
return <th>{local-name($th)}</th>
}
</tr>') AS thead
,@SelectForXmlRawElementsXsinil.query('for $tr in /row
return
<tr>
{
for $td in $tr/*
return <td>{string($td)}</td>
}
</tr>') AS tbody
FOR XML PATH('table'),TYPE
);
END
GO
@karoltheguy
Copy link
Author

To be used this way:
SELECT dbo.CreateHTMLTable((SELECT * FROM table FOR XML RAW,ELEMENTS XSINIL));

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment