ASP Kitchen: Classic ASP Articles: Practical uses of XML and XSL with Classic ASP

Practical uses of XML and XSL with Classic ASP

Introduction

Note: As of June 2007 this article has been archived and the example deactivated.

This article shows some of the uses of XML and XSL, particularly in the display of data obtained from remote servers using HTTP. The article retrieves sample data obtained from my personal website and displays it on the ASPAlliance.com website in the table and chart below.

The table and chart below shows the rough guide to the health of the web development job market. The figures are compiled from June 2001 and show the average daily number of permanent jobs listed on JobServe.co.uk that are based in London (UK) requiring the specific development skill listed in the table. Clicking on the skill name will display a chart of that skill's data.

How does this page work?

A script within the global.asa on my personal website executes once a day and using the process of "screen scraping", does a search of JobServe.co.uk and extracts the number of advertised jobs for a specified skill (e.g. ASP). The screen scraping is carried out by making use of the AspHTTP server component from ServerObjects Inc., although other server components can be used (an article in the ASP Kitchen describes how to extract HTML using HTTP with PerlScript).

The number of advertised jobs for a particular skill on a particular day are stored in an Access 95 database.

A monthly summary of the average number of jobs advertised requiring each skill is generated using the GetJobs query below:

SELECT Format([DateTime],"mmmm")+" "+CStr(DatePart("yyyy",[DateTime])) AS [Date], Avg(JobServe.ASP) AS ASP, Avg(JobServe.PHP) AS PHP, Avg(JobServe.HTML) AS HTML, Avg(JobServe.JavaScript) AS JavaScript, Avg(JobServe.JSP) AS JSP, Avg(JobServe.Java) AS Java, Avg(JobServe.Perl) AS Perl, DatePart("m",[DateTime]) AS Month, DatePart("yyyy",[DateTime]) AS Year
FROM JobServe
GROUP BY Format([DateTime],"mmmm")+" "+CStr(DatePart("yyyy",[DateTime])), DatePart("m",[DateTime]), DatePart("yyyy",[DateTime])
ORDER BY DatePart("yyyy",[DateTime]), DatePart("m",[DateTime]);

A Classic ASP page on the site is then used to convert the data to XML. This XML is accessible from the URL http://www.brettb.com/ASPAlliance/WebJobMarketXML.asp. Converting data to XML is straightforward. Later versions of ADO contain methods for automatically converting a RecordSet to XML, but in this example I've constructed the XML from a series of Response.Write statements. The important thing to remember is to change the Content-Type of the output to text/xml. The ASP is shown below:

<%
Dim RS 'ADO RecordSet object
Dim sSQL

Const adOpenStatic = 3
Const adLockOptimistic = 3

sSQL = "GetJobs"

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sSQL,"DSN=" & Application("DSN"), adOpenStatic, adLockOptimistic

Response.ContentType = "text/xml" 
Response.Write("<?xml version=""1.0""?>" & vbCRLF)
Response.Write("<JobSurvey>" & vbCRLF)

Do While Not RS.EOF

Response.Write(" <Survey Date=""" & RS("Date") & """>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>ASP</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("ASP"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>PHP</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("PHP"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>HTML</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("HTML"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>JavaScript</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("JavaScript"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>JSP</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("JSP"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>Java</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("Java"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" <Skill>" & vbCRLF)
Response.Write(" <Name>Perl</Name>" & vbCRLF)
Response.Write(" <Average>" & Round(RS("Perl"), 0) & "</Average>" & vbCRLF)
Response.Write(" </Skill>" & vbCRLF)

Response.Write(" </Survey>" & vbCRLF)

RS.MoveNext
Loop

Response.Write("</JobSurvey>" & vbCRLF)

Response.End
%>

Retrieving XML from a remote URL

The FileSystemObject object can obviously be used to load XML from disk if it is located on the same machine/network as the web server. If the XML is on a remote server, then it is possible to load the XML via HTTP. There are various methods of retrieving XML from remote servers using HTTP. One solution is to use the AspHTTP server component from ServerObjects Inc.. Alternatively, PerlScript has built in support for HTTP. In this particular example, the Microsoft XMLHTTP component will be used. This component is supplied with Microsoft's XML Parser, and is installed with a variety of Microsoft products, including recent versions of Internet Explorer.

The sample code below uses the component to save the XML to a variable named oResponseXML. The code checks to ensure that the XML has been retrieved by looking for a 200 HTTP status code. The bXMLLoadError variable is assigned a boolean value of True if the XML was not retrieved correctly.

<%
'Initialise variables
Dim oXMLHTTP
Dim oResponseXML
Dim bXMLLoadError

Set oXMLHTTP = Server.CreateObject("Microsoft.XMLHTTP") 'Create instance of the Microsoft XMLHTTP object

'Use the Microsoft XMLHTTP object to retrieve the XML from the remote server
oXMLHTTP.Open "GET", "http://www.brettb.com/ASPAlliance/WebJobMarketXML.asp", false
oXMLHTTP.Send("")

'Check that the XML was retrieved successfully by checking to ensure that a HTTP status code of 200 was received (200 = OK)
If oXMLHTTP.Status = 200 Then
    Set oResponseXML = oXMLHTTP.ResponseXML 'Save XML to an object
    bXMLLoadError = False
Else
    Response.Write("<font color=""red"">Page Error: Could not load XML from remote server</font><br>") 'Show error message
    bXMLLoadError = True
End If

Set oXMLHTTP = nothing 'Release Microsoft XMLHTTP object
%>

Formatting the XML into a table

As can be seen at the top of the page, the job market data  is displayed in two formats: as a table and as a chart. There are two methods of extracting the required information from the XML and formatting it as a table. The first method would be to write a script to parse the XML and extract the relevant data for each row in the table. Alternatively, use can be made of Extensible Stylesheet Language (XSL). This allows XML to be converted into HTML by making use of an appropriate stylesheet. The stylesheet is basically a template of HTML into which parts of the XML are substituted. XSL is described in more detail in some of the links in the further reading section. The stylesheet used in this example may be seen here.

The Microsoft XML components make it straightforward to display XML as HTML using an XSL stylesheet. The sample code below will apply a stylesheet to the previously retrieved XML:

<%

If Not bXMLLoadError Then

    'Load the XML from the oResponseXML object
    set oXML = Server.CreateObject("Microsoft.XMLDOM")
    oXML.Async = false
    oXML.Load(oResponseXML)

    'Load the XSL from disk
    set oXSL = Server.CreateObject("Microsoft.XMLDOM")
    oXSL.Async = False
    oXSL.Load(Server.MapPath("WebJobMarketTable.xsl"))

    Response.Write(oXML.transformNode(oXSL)) 'Transform the XML using the XSL stylesheet

End If

%>

XSL is a very detailed subject; additional resources are shown in the further reading section below.

Formatting the XML as a chart

The XML is also formatted as a chart which shows the monthly average number of jobs advertised for a specific skill. The actual chart is produced using the AspImage server component from ServerObjects Inc. . The server component is called from an ASP file called JobChart.asp. The data to be displayed in the chart is sent in the querystring, using a format such as that shown below:

http://www.brettb.com/JobChart.asp?Visit=June+2001%2C793&Visit=July+2001%2C763&Visit=August+2001%2C792&Visit=September+2001%2C785&Visit=October+2001%2C564&Visit=November+2001%2C491&Visit=December+2001%2C285

The link to the chart, therefore, requires data for the X (average number of visits) and Y (date) axes of the chart, which involves parsing the XML. For each skill, the date is extracted, together with the monthly average number of jobs advertised for the specific skill. The code for extracting these details is shown below.

<%
If Not bXMLLoadError Then

Dim Row_Date
Dim Skill_Details
Dim ChartLinkText
Dim Skill

Skill = Request.QueryString("Skill") 'Retrieve the skill name from the QueryString
If Skill = "" Then Skill = "ASP"

'Initial part of the QueryString required to call the ASP
'that generates the PNG format chart for this data
ChartLinkText = "http://www.brettb.com/JobChart.asp?"

For Each x In oXML.documentElement.childNodes 'Parse the XML nodes

    Row_Date = x.getAttribute("Date") 'Extract the date for this survey from the Survey XML node

    For Each y In x.childNodes 'Loop through each node in this particular Survey node

        If y.childNodes.Item(0).Text = Skill Then 'Extract the monthly average number of jobs advertised for the specific skill

            Skill_Details = Skill_Details & "Visit=" & Server.URLEncode(Row_Date & "," & y.childNodes.Item(1).Text ) & "&"

        End If

    Next

Next

ChartLinkText = ChartLinkText & Skill_Details 'Update the QueryString required to generate the chart

Set oResponseXML = nothing
Set oXSL = nothing
Set oXML = nothing
%>

The chart is then included on the page using the following:

<%If Not bXMLLoadError Then%>
<imgsrc="<%=VisitChartLinkText%>" align="absMiddle" alt= "Average number of<%= Skill%>jobs"></p>
<%End If%>

The use of the ASPImage server component to dynamically generate images is beyond the scope of this article, but may be covered in a future article. Incidentally, ASP.NET contains support for dynamic image generation using the System.Drawing functions.

Further reading

• XML features heavily in .NET, so why not start learning ASP.NET with Sams' Teach Yourself ASP.NET in 24 Hours.

• Two useful articles on XSL are available on ASPAlliance.com: Basic XML3: XSL and ASP.NET XML/XSL Transforms.

• Microsoft have a section of their website dedicated to XML. The Windows Platform SDK and MSDN contain a complete reference to the various components used to parse XML. There is also a useful guide to XSL.

• Learn how to use the dynamic image creation facilities of the System.Drawing functions in ASP.NET. Other System.Drawing articles are available on ASPAlliance.com.

• Help improve your knowledge of XML and XSL with the following from Amazon.com:

Useful Development Tools

ASP Documentation Tool™

Automatically creates technical documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework applications written in C# or VB.NET (including ASP.NET). Documentation for SQL Server 7/2000/2005 databases and C#/VB.NET components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats. Additional support for ASP.NET web applications. A useful alternative to NDoc!

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0, 2000 and 2005 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships, Jobs and DTS Packages.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript search engines and server-side ASP search engines and ASP.NET search engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

Text Workbench

Text Workbench is a file search and replacement utility for text files and Microsoft Office documents. Make rapid file replacements on multiple files and folders full of files. Advanced replacement options include regular expressions support. It even works on remote file systems via FTP. A Regular Expression Laboratory allows advanced pattern matching and replacement expressions to be built and tested. This great utility will make your everyday development tasks much easier!

Download Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation).

Indexing Service Companion

The Indexing Service Companion is a utility that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from any remote website and also from ODBC compliant databases. As such it can be used as a low cost alternative to Sharepoint's Search Services.

View Product Documentation (119K ZIP file).     Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

ASP Spell Check

ASPSpellCheck is the easy way to add spell checking capabilities to your ASP or ASP.NET websites, Intranets and web applications. The utility allows you to add spell checking capabilities to any HTML text field or rich content editing text box. It works with all common web browsers, and there are no components or databases to install on the server.

Read ASPSpellCheck Review.     View ASPSpellCheck Examples.     Download Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation).

Author details

Brett Burridge has worked as a web developer since 1997 and has developed web applications for a range of corporations, start up busiensses and educational establishments.

Brett is presently employed as an Internet developer and technical writer through his own company, Winnersh Triangle Web Solutions Limited. The company produces a number of innovative products, including a range of software documentation tools, which include the ASP Documentation Tool™, the .NET Documentation Tool for VB.NET and C#, and the SQL Server Documentation Tool. Other products include The Website Utility, which functions as a website error checker, search engine optimizer and ASP/ASP.NET search engine builder application.

As well as the ASPAlliance, Brett has written articles for Ariadne.ac.uk, ASPToday, the software documentation portal www.softwaredocumentation.info, and has contributed recipes to the ASP.NET Developer's Cookbook.    links

Outside web development, Brett is interested in travelling (here are my travel logs from New York, Hong Kong and Tokyo), digital photography (here's my photo gallery), tropical fishkeeping and collecting contemporary works of art by artists such as Doug Hyde.

Contact Brett by emailing

Article history

"Practical uses of XML and XSL with Classic ASP" published on ASPAlliance.com on 31 December 2001.

ASP Kitchen: Classic ASP Articles: Practical uses of XML and XSL with Classic ASP