Good Morning, I am relatively new to leveraging the benefits of XML and I’m hoping you can help me take it to the next level. I frequently use SQL selects that utilize the ‘for xml path’ option in conjunction with sp_send_dbmail where I specify @body_format=’HTML’. The result is a very esthetically pleasing email with the information that my end users have requested. Here is my question. How else, within the realm of SQL Server 2008, can I render and transform my SQL/XML data into a pleasing end user format without using dbmail or Reporting Services? Thanks in advance for any advice you can provide. Regards, D. Fortier
I expect you have discovered the trick whereby a a Query result can be turned into an HTML structural 'fragment' such as a table, or list. By using CSS3, this can be rendered in a number of ways that make the results very easy to read. This obviously has value when displayed in Reporting Services or sent as an HTML email. The results can also be returned by a stored procedure as an output variable, so can be used by any application, web or otherwise, to display results. I use Powershell to get such results from SQL Server and display them on the client machine in IE, using automation. However, any .NET language can be used. As far as turning SQL into a 'pleasing and end-user format', I'm assuming that you require something that delivers reports to end-users outside of requiring a conventional application. There are a number of techniques that can be used for this, mostly using Office applications such as Word (tables) and Excel. I've used scripting as well as third-party tools for doing this. The former is more versatile, but the latter is a lot easier! If you can refine your question a bit, then we can give a clearer answer.
> *Hello Phil, thank you for your response. Unfortunately I haven’t discovered the trick whereby* > *a Query result can be turned in an HTML structure. Could you explain further please?* Here is a demo of the trick to render XML as a table. It works equally well with lists and directory structures. OK: This has little to do with relational practice, but neither does XML. We're just using this stuff to prepare the output report. For formatting this sort of table which has no CSS assigned to particular colums, use CSS3. I describes some techniques here [Making HTML tables easier on the eye- CSS Structural Pseudo-classes] USE AdventureWorks DECLARE @query NVARCHAR(MAX) SET @query = ' AdventureWorks Employees Employee NamePhoneEmail' + REPLACE( CAST( ( SELECT TOP 20 --purely for demonstration purposes td = Coalesce( Title + ' ', '' ) + Coalesce( FirstName + ' ', '' ) + Coalesce( MiddleName + ' ', '' ) + LastName,'', td = Phone,'', td = eMailAddress FROM Person.Contact FOR XML PATH('tr'), TYPE ) AS NVARCHAR( MAX )), ' ', ' ' ) + ' ' SELECT @Query :