x

Rendering XML

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
more ▼

asked Dec 29, 2011 at 06:55 AM in Default

don 2 gravatar image

don 2
106 9 9 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Dec 29, 2011 at 10:12 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

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?
Dec 29, 2011 at 12:18 PM don 2
Ok, did some research, now I’m good for turning Query results to HTML fragments. Here’s my dilemma; boss calls ‘I need (asap) a name and address listing of our 3000 employees. It needs to look good and saved on a network drive. In Mgmt Studio I write my SELECT FOR XML PATH('tr'), ELEMENTS statement sending the result to a varchar(max) variable where I include HTML formatting information. I retrieve the final result with SELECT @MyVariable. I then run my SQL with Result to Grid, copy/paste the result in Notepad where I save it to a file called MyEEs.html. I open the file with IE and all looks good except I don’t have the bottom half of my ‘report’. Results to Text and Results to File are even worse. In summary, what is the best way to create an .html file from a SQL FOR XML statement? Thanks again for any comments, guidance, or solutions.
Dec 30, 2011 at 10:31 AM don 2
In SSMS under Tools->Options - Query Results - SQL Server - Results to Grid what value do you have for Maximum Characters Retrieved? Your results may be truncated so you do not see the bottom half of your report.
Dec 30, 2011 at 04:10 PM Scot Hauder
The Maximum Characters Retrieved is set to 65535 which is the max, thanks for pointing out why I'm having a problem. Given this limitation what do you feel is the best way to create an .html file of the results from a SQL FOR XML statement?
Jan 04, 2012 at 06:09 AM don 2
Write the results to file (Query ->Results to..-> Results to file) rather than copy/paste from the results pane and all should be well.
Jan 04, 2012 at 06:35 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

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][1]

  USE AdventureWorks
  DECLARE @query NVARCHAR(MAX)

  SET @query = '<table>
      <caption>AdventureWorks Employees</caption>
      <tr><th>Employee Name</th><th>Phone</th><th>Email</th></tr>' +
              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 )), '</tr><tr>',
              '</tr>
          <tr>'
               ) + '</table>
      '

  SELECT
    @Query 
[1]: http://www.simple-talk.com/dotnet/.net-framework/making-html-tables-easier-on-the-eye--css-structural-pseudo-classes/
more ▼

answered Dec 31, 2011 at 01:39 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

I see you are embedding a carriage return between the and , could you explain why you are doing this? I tested this and all it seems to do is lengthen the character length of the final result which just further exacerbates my problem. Obviously I'm missing something. Just to restate my question, what is the best way to create an .html file from a SQL FOR XML statement? Thank you.
Jan 04, 2012 at 06:19 AM don 2
I'm not quite sure what the problem is. SSMS only displays a certain number of characters of the string in the output pane, but this does not cause problems unless you need to inspect the entire string in SSMS. If this was part of a process. then the next stage would be to maybe save the results to a file or return it to the application. If you need to scoop up the results on the client end using SSMS you'd save the result to file rather than display it in the output pane.
Jan 04, 2012 at 06:33 AM Phil Factor

When I run the SQL you provided and I send the results to a file I only get the caption and the first data row:

AdventureWorks Employees Employee Name Phone Email Mr. Gustavo Achong 398-555-0132 gustavo0@adventure-works.com Ms. Catheri

(1 row(s) affected)
Jan 04, 2012 at 07:06 AM don 2
I double checked here, and it works fine for me both to file and on-screen. it looks very like you haven't set the 'Maximum number of characters to display in each column' to 8192 (The Maximum). You can set this from the current query (Query->Query options->) or for your default settings for subsequent queries from Tools->Options->Results To Text. You can take out the option to include column headers in the result set too, and display messages in a separate tab
Jan 04, 2012 at 07:42 AM Phil Factor
Hum, that's peculiar! I have my defaults set to the max for text (8192) and grid (65535). My problem appears to be that the query result is going to a single column in a single row. That's why I get better results when I send the results to grid.
Jan 04, 2012 at 08:25 AM don 2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150

asked: Dec 29, 2011 at 06:55 AM

Seen: 1384 times

Last Updated: Dec 29, 2011 at 06:55 AM