Generate HTML output for given SQL Query
Hi, After creating the SP in server, under a database, when the sp is executed it is giving an error msg: Invalid object name ‘tempdb.sys.objects’. I removed tempdb and made sys.objects as sysobjects, then when executed, it gives error: (9 row(s) affected) Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ‘ ‘. Server: Msg 208, Level 16, State 1, Procedure SP_Get_Query_HTML, Line 37 Invalid object name ‘##tempHTML2’. I am executing this statement: exec SP_GET_QUERY_HTML ‘select top 10 * from Sample ‘ What is wrong in this? Why is it not working? Also, where is the output file generated? -Anukul
Hi. Thanks for the code., I got this working. but i have a trouble if the records gets more. bcoz in your code you are using varchar to hold the HTML string , The varchar has max of 8000 char. If the HTML grows more than that how to show the content. Pls help me to solve this ? Thanks, Dosth
Hi Dosth, Can u post the complete code on this thread after you corrected it and made it workable? Tks, Anukul
Hi Anukul, This is the code i got worked. /* EXECUTE [SPGET_QUERY_HTML] ‘select * from yourtablename’ SELECT * FROM ##TEMPhtml1 SELECT * FROM ##TEMPhtml2 */ Create procedure [dbo].[SPGET_QUERY_HTML] ( @p_sqlstmt varchar(8000)) as declare @columns varchar(8000) declare @finalhtmlout varchar(8000) declare @colHeader varchar(8000) declare @Final varchar(8000) Declare @sqlstmt varchar(8000) — drop temporary tables used. IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ‘##TEMPhtml1’) DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ‘##TEMPhtml2’) DROP TABLE ##TEMPhtml2 — prepare query set @sqlstmt = ‘select * into ##tempHTML1 from (‘ + @p_sqlstmt + ‘) as T1’ execute (@sqlstmt) —Prepare columns details SELECT @columns = COALESCE(@columns + ‘ + » » + ‘, ») + ‘convert(varchar(100),isnull(‘ + column_name +’,» »))’ FROM tempdb.information_schema.columns where table_name=’##tempHTML1′ —Prepare column Header set @colHeader = ‘ ‘ SELECT @colHeader = @colHeader + ‘ ‘ FROM tempdb.information_schema.columns where table_name=’##tempHTML1’ set @colHeader=@colHeader + ‘ ‘ —prepare final output set @Final= ‘Select » » into ##tempHTML2 from ##tempHTML1 ‘ execute( @Final) set @finalhtmlout= ‘ ‘ + @colHeader select @finalhtmlout= @finalhtmlout + [ ] from ##tempHTML2 set @finalhtmlout= @finalhtmlout + ‘ ‘ — drop temporary tables used. IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ‘##TEMPhtml1’) DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ‘##TEMPhtml2’) DROP TABLE ##TEMPhtml2 —return final output select @finalhtmlout as HTMLoutput Thanks, Dosth
Hello Dosth, This Code also doesn’t work . What changes ankul has suggested needs to be done to work on SQL 2000 or SQL 2005. Even after modification i get error message as Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named ‘##tempHTML1’ in the database. Do we need particular setting to execute/run this code. If yes please let me know. The Code i execueted is as below SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO —exec [SPGET_QUERY_HTML] ‘select top 11 * from EMP_MASTER ‘ CREATE procedure [SPGET_QUERY_HTML]( @p_sqlstmt varchar(8000)) as declare @columns varchar(8000) declare @finalhtmlout varchar(8000) declare @colHeader varchar(8000) declare @Final varchar(8000) Declare @sqlstmt varchar(8000) begin — drop temporary tables used. IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = ‘##TEMPhtml1’) DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = ‘##TEMPhtml2’) DROP TABLE ##TEMPhtml2 — prepare query set @sqlstmt = ‘select * into ##tempHTML1 from (‘ + @p_sqlstmt + ‘) as T1’ execute (@sqlstmt) —Prepare columns details SELECT @columns = COALESCE(@columns + ‘ + » » + ‘, ») + ‘convert(varchar(100),isnull(‘ + column_name +’,» »))’ FROM TEST.information_schema.columns where table_name=’##tempHTML1′ —Prepare column Header set @colHeader = ‘ ‘ SELECT @colHeader = @colHeader + ‘ ‘ FROM TEST.information_schema.columns where table_name=’##tempHTML1’ set @colHeader=@colHeader + ‘ ‘ —prepare final output set @Final= ‘Select » » into ##tempHTML2 from ##tempHTML1 ‘ execute( @Final) set @finalhtmlout= ‘ ‘ + @colHeader select @finalhtmlout= @finalhtmlout + [ ] from ##tempHTML2 set @finalhtmlout= @finalhtmlout + ‘ ‘ — drop temporary tables used. IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = ‘##TEMPhtml1’) DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = ‘##TEMPhtml2’) DROP TABLE ##TEMPhtml2 —return final output select @finalhtmlout as HTMLoutput END SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO If I could run this code then this will help us on our project a lot. Regards, Tej
Thanks, Raja — this works great! A few mods: 1. Changed the VARCHAR(8000) variables to VARCHAR(MAX) to avoid problem noted by another respondent. 2. To handle ORDER BY clauses, I added code to split out that clause, then appended the variable containing it to the following statment: set @Final= ‘Select » » into ##tempHTML2 from ##tempHTML1 ‘
I am getting this error: (3 row(s) affected) Msg 1038, Level 15, State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as «» or [] are not allowed. Add a name or single space as the alias name. Msg 208, Level 16, State 1, Procedure SPGET_QUERY_HTML, Line 43 Invalid object name ‘##tempHTML2’. Line 43 is from part on this select: —Prepare column Header set @colHeader = ‘ ‘ SELECT @colHeader = @colHeader + ‘ ‘ FROM tempdb.information_schema.columns where table_name=’##tempHTML1’ set @colHeader=@colHeader + ‘ ‘ Table I am selecting from: CREATE TABLE [dbo].[Jobs]( [ServerName] [varchar](50) NULL, [JobName] [varchar](50) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [Status] [varchar](50) NULL, [Log] [nvarchar](max) NULL, [PreviousStart] [datetime] NULL, [PreviousEnd] [datetime] NULL Lastly, when I just do a simple select * from jobs as HTMLOutput, I don’t get the html, just the raw text. I am SQL 2005.
Hi Can someone please helpout me for the following error: 🙂 Msg 1038, Level 15, State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as «» or [] are not allowed. Add a name or single space as the alias name. Msg 208, Level 16, State 1, Procedure SPGET_QUERY_HTML1, Line 38 Invalid object name ‘##tempHTML2’. Please let me know where exactely it is going wrong & what action shall i take on this.
I’d like to suggest that this solution strikes me as a bad idea. Why are you asking your database to do so much string manipulation? If you’re using ADODB, recordset.getString() is a much better way to transform data into the basic table this example produces. If you’re using PHP, the data connector is probably returning your results as an array. With implode() you could get a similarly basic markup. If you have more complex html to generate you’ll be spending even more time with procedural code (probably cursors) and doing odd things to work around varchar limitations, etc. You might also be tempted to use SQL Server’s for XML. I would still suggest that the webserver should be be requesting data, transforming it, and (probably) caching it. Also a webservice could be acting as an additional tier between client-side scripts and the database. The programming environment is a better place to transform data for presentation. The .GetString() method has been around since 1998! http://www.4guysfromrolla.com/webtech/121598-1.shtml
I would tend to agree with Mike Dougherty-384281. This approach seems like a bad idea for a number of reasons. Why not use a «FOR XML» query and transform the XML on a web page?
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
Parsing HTML in SQL Server
Recently I was asked how to parse text out of an HTML fragment stored in SQL Server.
Over the next few seconds my brain processed the following ideas:
- SQL Server is not meant for parsing HTML. Parse the data with something else.
- T-SQL does have functions like REPLACE, CHARINDEX, and SUBSTRING though, perfect for searching for tags and returning just the values between them.
- CLRs could do it, probably using some kind of HTML shredding library. You also might be able to use XMLReader to do something with it.
- Wait a minute, SQL Server has XML parsing functions built in!
Maybe you see where this is going.
WARNING — this is a terrible idea
Parsing HTML with T-SQL is not a great idea. It’s dirty, it’s prone to breaking, and it will make your server’s CPUs cry that they aren’t being used for some nobler cause. If you can parse your HTML somewhere outside of SQL Server, then DO IT THERE.
With that said, if you absolutely need to parse HTML on SQL Server, the best solution is probably to write a CLR.
However, if you are stuck in a bind and plain old T-SQL is the only option available to you, then you might be able to use SQL Server’s XML datatype and functions to get this done. I’ve been there before and can sympathize.
So anyway, here goes nothing:
Using XML to parse HTML
Let’s say we have the following fragment of HTML (copied from a bootstrap example template):
If we wanted to say extract all of the text from this HTML (to allow text mining without all of the tags getting in the way) we could easily do this using the XML nodes() and value() methods:
-- Get all text values from elements SELECT T.C.value('.','varchar(max)') AS AllText FROM @html.nodes('/') T(C); 
If we want to only extract the items from the list elements, we can write some XQuery to select only those elements:
-- Get a fragment of HTML SELECT T.C.value('.','varchar(100)') AS ListValues FROM @html.nodes('//*[local-name()=("li")]') T(C); 
Finally, we can also do things like select HTML fragments based on an attribute to parse further in subsequent steps. If I want to select the div with a class of «card-body», I can write:
-- Get the text from within certain elements SELECT T.C.query('.') AS CardBody FROM @html.nodes('//div[@class="card-body"]') T(C); 
Yuck
To reiterate — you don’t want to do any of the above unless you have no other choice.
The XML parsing functions will not parse all HTML, so you may need to do some pre-processing on your HTML data first (removing invalid HTML, closing tags, etc. ).
It works beautifully in the above example but your results may very. Good luck!