Sql results to html

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Take a SQL query and turn it into HTML in PL/SQL.

License

eaolson/sql_to_html

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Take a SQL query and turn it into HTML using PL/SQL.

There is currenly one function, create table , that takes a SQL query as a string and returns an HTML table.

FUNCTION sql_to_html.create_table( p_sql IN CLOB, p_add_header IN BOOLEAN DEFAULT TRUE, p_headers IN VARCHAR2 DEFAULT NULL, p_table_attribute IN VARCHAR2 DEFAULT NULL, p_header_attributes IN VARCHAR2 DEFAULT NULL, p_cell_attributes IN VARCHAR2 DEFAULT NULL, p_separator IN VARCHAR2 DEFAULT '|', p_escape_chars IN BOOLEAN DEFAULT TRUE ) RETURN CLOB

Parameter	Description
p_sql	A SELECT query in string form. All values should be VARCHAR2 s up to 4000 bytes long. Any other data type will be implicitly converted to a character string, possibly with unexpected results. Check your NLS settings or use TO_CHAR if you’re going to be selecting dates!
p_add_header	If true, the first row will be column headers, as . If false, the table will start with s.
p_headers	If headers are enabled and this is null, the column headers will be the field names, as determined from the query. If this is not null, it should be a delimited string of header values.
p_table_attribute	This string of HTML attributes will be added to the element. For example, if ‘id=»foo» ‘ , the resulting table will be .
p_header_attributes	A delimited string of HTML attributes to add to the elements.
p_cell_attributes	A delimeted string of HTML attributes to add to the elements.
p_separator	The default separator to use for p_headers , p_header_attributes , and p_cell_attributes . This must be a single character. The default is the pipe character (|), since that is not likely to be used in an HTML attribute.
p_escape_chars	If true, HTML characters in the output will be escaped. This affects only the contents of the header and data cells; attributes are never escaped.

sql_to_html.create_table( p_sql => 'SELECT ''One fish'', 2, SYSDATE, ''I '' FROM dual' , p_headers => 'Fish|Other Fish|Right Now|Heart', p_header_attributes => 'class="col1"|class="col2"', p_table_attribute => 'id="mytable" ', p_cell_attributes => 'class="col1"|class="col2"|class="col3"' )

will output this HTML table (indentation added for clarity):

TABLE id pl-s">mytable" class pl-s">myclass"> TR> TH class pl-s">col1">FishTH> TH class pl-s">col2">Other FishTH> TH>Right NowTH> TH>HeartTH> TR> TR> TD class pl-s">col1">One fishTD> TD class pl-s">col2">2TD> TD class pl-s">col3">2017-01-09 19:11:34TD> TD>I <3 youTD> TR> TABLE>

There is currently no installation script. Run the *.pks and *.pkb files in SQL*Plus, SQL Developer, or your IDE of choice. I suggest installing it into its own schema and granting execute privileges to any schema that needs to use it.

This package uses DBMS_SQL to execute the SQL command and runs with AUTHID CURRENT_USER permissions. No checking of the SQL command is done, so you should make sure to protect it from SQL injection attacks.

Since the output is likely to be used on a web page, care should also be taken that the output is escaped to protect against HTML injection attacks.

About

Take a SQL query and turn it into HTML in PL/SQL.

Источник

Generating HTML from SQL Server Queries

You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques.

Can you produce HTML from SQL? Yes, very easily. Would you ever want to? I certainly have had to. The principle is very simple. HTML is really just a slightly odd dialect of XML that imparts meaning to predefined tags. SQL Server has built-in ways of outputting a wide variety of XML. Although I’ve had in the past to output entire websites from SQL, the most natural thing is to produce HTML structures such as tables, lists and directories.

HTML5 can generally be worked on in SQL as if it were an XML fragment. XML, of course, has no predefined tags and is extensible, whereas HTML is designed to facilitate the rendering and display of data. By custom, it has become more forgiving than XML, but in general, HTML5 is based on XML.

Generating Tables from SQL expressions.

In HTML5, tables are best done simply, but using the child elements and structures so that the web designer has full control over the appearance of the table. CSS3 allows you to specify sets of cells within a list of child elements. Individual TD tags, for example, within a table row (TR) can delimit table cells that can have individual styling, but the rendering of the table structure is quite separate from the data itself.

The table starts with an optional caption element, followed by zero or more colgroup elements, followed optionally by a thead element. This header is then followed optionally by a tfoot element, followed by either zero or more tbody elements or one or more tr elements, followed optionally by a tfoot element, but there can be only one tfoot element.

The HTML5 ‘template’ for tables

Источник

Email Query Results as an HTML Table in SQL Server (T-SQL)

SQL Server’s Database Mail has a stored procedure called sp_send_dbmail that you can use to send emails from SQL Server.

You can use this stored procedure to send query results formatted into an HTML table.

Example

Here’s an example to demonstrate.

DECLARE @body_content nvarchar(max); SET @body_content = N' table.GeneratedTable < width: 100%; background-color: #ffffff; border-collapse: collapse; border-width: 2px; border-color: #ffcc00; border-style: solid; color: #000000; >table.GeneratedTable td, table.GeneratedTable th < border-width: 2px; border-color: #ffcc00; border-style: solid; padding: 3px; >table.GeneratedTable thead ' + CAST( (SELECT td = ArtistId, '', td = ArtistName, '', td = ActiveFrom, '' FROM Music.dbo.Artists FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) + N'
 
 
ArtistId
 
ArtistName
 
ActiveFrom
 
 
 
 
'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB Admin Profile', @recipients = '[email protected]', @body = @body_content, @body_format = 'HTML', @subject = 'As discussed';

In this case, I declare a variable called @body_content and put all the HTML code into it. I also add some styles using CSS, so that the table has visible borders, headers, etc. This variable is used as the value of the @body argument when sending the email with the sp_send_dbmail procedure.

If you don’t need any styles to be applied, simply remove the tags and everything in between. In this case, you can also remove the class=»GeneratedTable» bit from the actual table.

If you want to change the styles, check out my table generator over at Quackit. You can use it to generate your table styles, then you can copy/paste those styles into the code that goes into your emails.

The T-SQL code passed to the CAST() function is what returns the query results, each in their own table row ( element), and each column within its own element.

Be sure to include @body_format = ‘HTML’ . This is what sends the email in HTML format. If you omit this (or if you set it to TEXT ), the recipient will receive the raw HTML code, instead of the rendered code.

Источник