• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

T-SQL output to html

I would like to create a string from a simple select clause building an html table with the database rows...

I tried something like this but it didn't add multiple rows:

DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT '<tr><td>'+NavPass+'</td><td>'+email+'</td></tr>' as strH FROM tblUser

SET @strHTML = @strHTML + '</table></body></html>'

can someone figure out how to have the return dataset encapsulated in an HTML table?
0
conrad2010
Asked:
conrad2010
1 Solution
 
Jon500Commented:
Hi,

You were close. Use this in your SELECT statement:

SELECT @strHTML = @strHTML + ...

Regards,
Jon500
0
 
Anthony PerkinsCommented:
You should be able to do this with a simple FOR XML clause, rather then resorting to concatenating strings and hoping that there are no special characters.
0
 
conrad2010Author Commented:
@Jon500

implemented yopur suggestion and got a NULL string back...

complete solution:
DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT @strHTML = @strHTML + '<tr><td>'+field1+'</td><td>'+field2+'</td></tr>' FROM mytable

SET @strHTML = @strHTML + '</table></body></html>'

select @strHTML
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @strHTML varchar(5000)
SET @strHTML = ''

SELECT @strHTML + CAST ( ( SELECT      
                                                      td = NavPass, '',
                                                      td = email, ''
                                           FROM tblUser
                                           FOR XML PATH('tr'), TYPE
                                           ) AS Varchar(MAX) )+
                              N''            
0
 
conrad2010Author Commented:
super!
0
 
Jon500Commented:
Might you have a typo? I ran this code with no issue in my T-SQL SQL Server 2005...

Thanks,
Jon500
0
 
Jon500Commented:
Oops, forgot attachment.
DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT @strHTML = @strHTML + '<tr><td></td><td>'+countryname+'</td></tr>' FROM dbo.BP_Accounts_CountryCodes

SET @strHTML = @strHTML + '</table></body></html>'

SELECT @strHTML

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now