$a = "<style>"$a = $a + "BODY{background-color:peachpuff;}"$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"$a = $a + "</style>"# The following formats "get-service" output correctlyGet-Service | Select-Object Status, Name, DisplayName | ConvertTo-HTML -head $a -body "<H2>Service Information</H2>" | Out-File temp.htmstart temp.htm# The following does not format SQLCMD.EXE output correctlysqlcmd -S server_name\instance_name -E -Q "select application_cde, env_cde, migration_note from metrics.dbo.application_migration_note" | select-object application_cde, env_cde, migration_note | ConvertTo-HTML -head $a -body "<H2>SQL Server Query Output</H2>" | Out-File temp.htmstart temp.htm
# See "SQL Server 2008 R2 Unleashed," page 1865 ff# See also http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/ <############################################################### # # Create a test table, populate it with data, and extract xml # ###############################################################> $HTML_File_NME = "temp.htm" $SQLCommand_STR = "if db_name() <> 'tempdb' use tempdb;set nocount ondeclare @Body_STR varchar(max) ;create table junk ( fname varchar(20), lname varchar(20) );insert into junk values ('John', 'Doe');insert into junk values ('Jane', 'Doe');insert into junk values ('Abby', 'Smith');print '<HTML><HEAD> <style>''td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ''</style></HEAD><BODY><TABLE CELLPADDING=1 CELLSPACING=1 BORDER=1> <TR BGCOLOR=#FFEFD8> <TH ALIGN=CENTER>First Name</TH> <TH ALIGN=CENTER>Last Name</TH> </TR>';:XML ONselect @Body_STR = ( select fname, lname from junk order by lname, fname for xml raw ('TR'), elements );:XML OFFselect @Body_STR = replace(@Body_STR, '<TR><fname>', ' <TR> <TD>')select @Body_STR = replace(@Body_STR, '</fname>', '</TD>')select @Body_STR = replace(@Body_STR, '<lname>', ' <TD>')select @Body_STR = replace(@Body_STR, '</lname></TR>', '</TD> </TR>')print @Body_STR;print '</TABLE></BODY></HTML>';drop table junk;" # End of $SQLCommand_STR variable sqlcmd.exe -S server_name\instance_name -E -w1000 -b -l 32 -d tempdb -Q "$SQLCommand_STR" -o $HTML_File_NME <############################################################### # # Display XML file information # ###############################################################> write-output "" write-output "Displaying the contents of $HTML_File_NME`:" write-output "" cat $HTML_File_NME start $HTML_File_NME exit
.\convert_sql_xml_to_html.ps1
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL DROP TABLE #T ; CREATE TABLE #T (ID INT IDENTITY PRIMARY KEY, ColA VARCHAR(10)) ;INSERT INTO #T (ColA)VALUES ('Hello'), ('there') ;SELECT (SELECT ID AS TD, '', ColA AS TD, '' FROM #T AS T2 WHERE T2.ID = #T.ID FOR XML PATH(''), TYPE) AS TRFROM #TFOR XML PATH('table') ;
<table> <TR> <TD>1</TD> <TD>Hello</TD> </TR></table> <== Can this tag be omitted?<table> <== Can this tag be omitted? <TR> <TD>2</TD> <TD>there</TD> </TR></table>
SELECT (SELECT (SELECT ID AS TD, '', ColA AS TD, '' FROM #T AS T2 WHERE T2.ID = #T.ID FOR XML PATH(''), TYPE) AS TRFROM #TFOR XML PATH(''), TYPE)FOR XML PATH('table');
