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

Format XML data for inclusion in web page

I'm currently querying my temperature database view with a tabular result as follows:

 2011-10-15-1158.png

If I add the "for XML Auto" clause, I get a result that looks like this:

<currtemp Location="Server Room" Temperature="73.58" Time="11:59AM"/><currtemp Location="Family Room" Temperature="83.3" Time="11:59AM"/><currtemp Location="General Environment" Temperature="70.52" Time="12:00PM"/>

or, if I click the link, it looks like this:

<currtemp Location="Server Room" Temperature="73.58" Time="11:59AM" />
<currtemp Location="Family Room" Temperature="83.3" Time="11:59AM" />
<currtemp Location="General Environment" Temperature="70.52" Time="12:00PM" />


What's the simplest way to incorporate this data into an html doc such that the result looks like this:

 2011-10-15-1213.png
0
stevengraff
Asked:
stevengraff
  • 6
  • 4
1 Solution
 
regevhaCommented:
Use XSL transformation (XML Style Sheet) to convert the XML to HTML.

Refer to the following article for more detailed technical information - http://www.xmlfiles.com/articles/cynthia/xslt/default.asp
For tutorial on defining XSL tranformations,refer to http://www.w3schools.com/xsl/
0
 
stevengraffAuthor Commented:
@regevha, in this example from w3schools.com...

http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog

I get it... except for the tryxslt.asp file itself. I assume I'm not seeing that when I right-click and choose view page source.
0
 
Kevin CrossChief Technology OfficerCommented:
I agree, you can use XSLT. If you need this to come out from T-SQL, you can use something similar to this example. I was writing an article about subnets and wanted to quickly create HTML <table> and this is what I came up with.

SELECT th1 AS "td", th2 AS "td"
FROM (VALUES('Network', 'NodesPer')) tr(th1, th2)
UNION ALL
SELECT '/' + CONVERT(VARCHAR(2), [number]) AS "td"
     , CONVERT(VARCHAR(20), POWER(CONVERT(BIGINT, 2), 32 - [number])) AS "td"
FROM master..spt_values AS tr
WHERE [type] = 'P'
AND [number] BETWEEN 24 AND 32
FOR XML AUTO, ELEMENTS, ROOT('table')
;

I have highlighted the important bits. Note, the use of UNION ALL to include the headings. You can always add those separate and avoid having to CONVERT() each data element to VARCHAR. Also, because it is a UNION, the column names had to match (i.e., td versus th on headings). Another approach for that is to bring back your data in a subquery like this:

SELECT CONVERT(XML, '<tr><th>Network</th><th>NodesPer</th></tr>'),
(
SELECT '/' + CONVERT(VARCHAR(2), [number]) AS "td"
     , POWER(CONVERT(BIGINT, 2), 32 - [number]) AS "td"
FROM master..spt_values tr
WHERE [type] = 'P'
AND [number] BETWEEN 24 AND 32
FOR XML AUTO, ELEMENTS, TYPE
)
FOR XML PATH(''), TYPE, ROOT('table')
;

Again, I tried to highlight the important changes.

Hope that helps!

kevin
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Kevin CrossChief Technology OfficerCommented:
To answer http:#36973995 and support XSLT suggestion: no, you won't see the XSL template when you right-click and view source of the web page as at that point, the transformation to HTML has already occurred. So, you would provide XML from your database that your site translates via XSL dynamically through .NET code shown or even in a .HTML page using JavaScript or VBScript to process the XML through the XSL template.
0
 
stevengraffAuthor Commented:
@mwvisa1, thanks, it appears that the FOR XML AUTO, ELEMENTS, ROOT('table') part produces a good format.

@mwvisa1, I basically understand the concept, just not how to actually do it. Do you know of any simple examples I could take a look at to get me started? I suspect the JavaScript route would work best for me. Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
How are you retrieving the original XML? In other words, is the XML being saved first to the web server or is it in the HTML body somewhere? Please confirm and when I get back to my PC, I will look for examples I have seen or mock something up.
0
 
stevengraffAuthor Commented:
@mwvisa1, at this point it's nowhere. I have the option to put it anywhere I want, though. My preference is to have it in it's own file, so I can run a query periodically to refresh it, using a SQL "job."

I'm trying to avoid "active" web pages that require a program to run upon accessing the web page. I want to be able to view the page using a blackberry browser. Blackberry browsers are fine with static content, but fussy with active content.
0
 
Kevin CrossChief Technology OfficerCommented:
Sounds good. The load from file technique is what I used many years ago with normal .HTML files. I often found the w3schools.com tutorials very good examples, so I double checked and they have an exact example: http://www.w3schools.com/xsl/xsl_client.asp

It shows loading the XML, loading the XSL template (StyleSheet), performing the transform and then adding the result to a <div> (HTML element) dynamically. Here is the .innerHTML() method tutorial:
http://www.w3schools.com/jsref/prop_html_innerhtml.asp

Walkthrough:
SQL Server outputs XML like:
<temperatures>
   <currtemp Location="Server Room" Temperature="73.58" Time="11:59AM" />
   <currtemp Location="Family Room" Temperature="83.3" Time="11:59AM" />
   <currtemp Location="General Environment" Temperature="70.52" Time="12:00PM" />
</temperatures>

Open in new window


XML StyleSheet could look like this:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
   <table border="1">
      <tr>
         <th>Location</th>
         <th>Temperature</th>
         <th>Time</th>
      </tr>
   <xsl:for-each select="temperatures/currtemp">
      <tr>
         <td><xsl:value-of select="@Location" /></td>
         <td><xsl:value-of select="@Temperature" /></td>
         <td><xsl:value-of select="@Time" /></td>
      </tr>
   </xsl:for-each>
   </table>
</xsl:template>

</xsl:stylesheet>

Open in new window


Note: if you output the XML for the temperatures already as a table, i.e., the root element is <table> and each row is <tr> with each column as <td>, then you can skip the transformation portion. You would just load the XML file and use .innerHTML to place it on your HTML page at some placeholder. If you go that route, then you may find this interesting - http://daytabase.org/2011/10/16/quick-xhtml-using-sql-server/. I made a quick blog post about this same topic and showed adding in attributes to top level table like border=1. If you use CSS, you can add id and class attributes also. Hopefully, in combination with other examples above it gives you tools to do the SQL side of things yourself.

Use JavaScript shown in the w3 schools tutorial. Here is a live example:
http://www.crossedlogic.com/ee/xml-xslt/temperatures.html

In case I cannot persist this live url, please find the files in the attached .zip file.
temperatures.zip
0
 
Kevin CrossChief Technology OfficerCommented:
And this may interest you:
http://www.crossedlogic.com/ee/xml-xslt/temperatures-styled.xml

It is similar to what sites like BrainBench does. Or at least on the surface. It serves the .XML file directly. In the .XML file, you specify the StyleSheet. You can do this during the process of generating the FOR XML output from SQL.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="temperatures-styled.xsl"?>
<temperatures>
   <currtemp Location="Server Room" Temperature="73.58" Time="11:59AM" />
   <currtemp Location="Family Room" Temperature="83.3" Time="11:59AM" />
   <currtemp Location="General Environment" Temperature="70.52" Time="12:00PM" />
</temperatures>

Open in new window


This is the important line:
<?xml-stylesheet type="text/xsl" href="temperatures-styled.xsl"?>

Then in the XSL, instead of just outputting a table -- a whole HTML structure is done as shown in the w3schools.com tutorial.
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<html><head><title>XML Example</title></head>
<body>
   <h1>Current Temperatures</h1>
   <table border="1">
      <tr>
         <th>Location</th>
         <th>Temperature</th>
         <th>Time</th>
      </tr>
   <xsl:for-each select="temperatures/currtemp">
      <tr>
         <td><xsl:value-of select="@Location" /></td>
         <td><xsl:value-of select="@Temperature" /></td>
         <td><xsl:value-of select="@Time" /></td>
      </tr>
   </xsl:for-each>
   </table>
</body>
</html>
</xsl:template>

</xsl:stylesheet>

Open in new window


The XML file is transformed by the XSL and the HTML is displayed on screen without JavaScript or an additional HTML file to embed the output into.

Kevin
0
 
stevengraffAuthor Commented:
Thank you for your thorough and comprehensive help!!!
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome! Good luck with your project(s).
Best regards and happy coding,

Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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