SQL Query to HTML/XML

there is nothing built-in as SQL is a QUERY language as opposed to a reporting tool.

but you can use workarounds such as:
https://www.red-gate.com/simple-talk/blogs/generating-html-sql-server-queries/
https://gallery.technet.microsoft.com/scriptcenter/Selection-result-as-HTML-a1ccff98

If you're running this from the command line then you can use the -H option to produce an HTML table.

If you're using a scripting language such as PHP, then you'd need to run the query, loop through the results and build the HTML string record by record.

If you can give us some more info, we might be able to offer some specific advice.

Just an afterthought ... the -H option on the command line is for mySQL. You didn't specify which server you're using!

You can build the HTML around your result.  Example:

DECLARE @table TABLE (Item VARCHAR(300), SizeMB Float)

INSERT INTO @table (Item,SizeMB)
VALUES ('Computer1',540.00)

INSERT INTO @table (Item,SizeMB)
VALUES ('Computer2',1120.00)


DECLARE @html VARCHAR(max)

SET @html = REPLACE(REPLACE((
	SELECT '<td>' + Item + '</td><td>' + CAST( SizeMB AS nvarchar(50)) + '</td>' FROM @table
	for xml path('tr')),'&lt;','<'),'&gt;','>')

SET @html = '<table><thead><tr><th>Computer</th><th>SizeMB</th></tr></thead><tbody>' + @html + '</tbody></table>'

SELECT @html

Select allOpen in new window

It is MSSQL, and I don't understand the Dustin Saunders answer...
My query got no computer1 or 2

>>It is MSSQL

Added  the Microsoft SQL Server Topic Area.

You really need to start adding the specific database Topic Area to the questions so you get answers specific to that database platform.

I'm not a SQL Server person but since I'm here, will the FOR XML clause work to get it in XML?

https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-2017

The problem you have with string concatenation methods is the data itself could cause invalid HTML/XML.

It's just an example with a sample table,  you'd need to adjust it to your query.  You're selecting A.* so I don't know what information is contained in the result.

DECLARE @html VARCHAR(max)

SET @html = REPLACE(REPLACE((

	--EDIT HERE
	SELECT '<td>' + Item + '</td><td>' + CAST( SizeMB AS nvarchar(50)) + '</td>' FROM @table
	
	
	for xml path('tr')),'&lt;','<'),'&gt;','>')

	--Edit the items in <th> </th> to add headers to your HTML.
SET @html = '<table><thead><tr>
					<th>Computer</th>
					<th>SizeMB</th>
			</tr></thead><tbody>' + @html + '</tbody></table>'

SELECT @html

Select allOpen in new window

With comments in specific areas, add a header for each column and wrap the items in <td> blocks like in the example.

Before you get into writing HTML into your queries, is there a particular reason you're trying to do this. If it's to present data in an HTML page, then there are probably better ways to do it. Maybe a little background info would be helpful

It is to generating a report monthly to the boss, and way to send this report in excel

or html as mail

Query is to show all databases and sizes

Easiest way to get to Excel is CSV.

I'm guessing you are looking for automation and not a manual process.

I realize you want SQL Server but what I have done in the past, with Oracle, is generate crude HTML using the suggested string concatenation then send the email from inside the database.  For this report you shouldn't need any "fancy" HTML.

My caution above still stands:  You need to make sure your data doesn't break the HTML tags.  Since this is a report you create, you have complete control over it so that likely isn't going to be a problem.

@WeTi

You do realize you can connect Excel directly to SQL server right?  You can create a view and link Excel to that, then all they have to do is open the workbook and get live data

As Dustin Saunders said above, see my answer here: https://www.experts-exchange.com/questions/29103145/Excel-Microsoft-Object-Library-Pull-Table-from-Website.html

Yes, but Im looking for solution to directly from SQL query to HTML or XML, and yes its for automatication reason,

Just a question like this: what will send your email?

I would send the email directly from the database.  I'm not a SQL Server person but it appears it can:  sp_send_dbmail
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

In the Oracle world, I did this all the time and not just for Management and User reports.  As the DBA, my databases emailed me their status and important things every morning.