Convert any Table into XML format & view it using XSL

• Download source files - 2.29 Kb

Introduction

This application allows you to convert a Recordset derived from a Table of any ADO and OLE-DB complaint Database into XML format and you can additionally view it using XSL. I have tested it out with Access and SQL Server but it should work fine with other Database Management Systems. It is more generic than an existing Microsoft KB article - Q281099: HOWTO: Use Active Server Pages to Dynamically Generate XML and also differs code-wise.

The user has to specify a DSN-less Connection string and a Table name pertaining to that database. The corresponding Recordset is converted to XML and this is displayed by a generic Extensible Stylesheet Language file. You can save this page as a .xml file. To keep the code snappy it is assumed that the DSN-less Connection string and the Table name are valid and no checking is done. Moreover special data types like Image, Binary are not supported.

A XML file generated by this app may look like this -

<?xml version='1.0'?>
<?xml-stylesheet type='text/xsl' href='generic.xsl'
version='1.0'?>
<xml>
<row CallID='CallID' ContactID='ContactID'
CallDate='CallDate' CallTime='CallTime'
Subject='Subject' Notes='Notes' />
<row CallID='1' ContactID='1' CallDate='11/21/1994'
CallTime='12:05:00 PM' Subject='Suite of coffees.'
Notes='Spoke to Janet about NWIND carrying a coffee
collection designed by us.' />
<row CallID='2' ContactID='1' CallDate='12/13/1994'
CallTime='12:45:00 PM' Subject='Pricing for proposed
suite.' Notes='Too high - should wait and see if Janet
comes around.' />
</xml>

You may remove lines 2 and 3 if you are interested only in the xml file or tweak the code to prevent them from appearing at all. You can use the FileSystemObject to create and store XML and their equivalent XSL files automatically on your machine.

XML has become the lingua-franca of the Internet. RDBMS's like Oracle and SQL Server have built-in XML features. To retrieve rows in XML format using SQL Server 2000,you can use the SELECT query with the FOR clause.

Steps to run the app -

1. The zipped file contains 3 files xmily.html, xmile.asp and generic.xsl. Create a new folder named xmile within the folder that contains the content for your default Web site. This is the <drive>:\InetPub\WWWRoot folder by default. Unzip xmile.zip to this folder.
2. You'll need IIS or PWS to first call xmile.html using http like this-
   http://localhost/xmile/xmily.html
3. Type in a DSN-less Connection string to connect to a DBMS and a Table name. The XML content opens in a new window as a HTML table with the help of the XSL file, generic.xsl.

Source Code

• xmily.html allows user to submit Connection String and Table name
• generic.xsl displays the recordset converted to XMLformat as a HTML table.
• xmile.asp handles the actual conversion of recordset info into XML.

Useful Links

• W3Schools.com contains simple and useful tutorials on XML and XSL.
• This Charles Caroll article talks about the advantages of using the GetRows Method

You can find a nice compilation of Connection Strings here