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 :\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

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 4 of 4 (Total in Forum: 4) (Refresh) FirstPrevNext

FileSystemObject Member 3707053 1:35 7 Apr '08   Works fine for me so far; there are two things i'd like to accomplish: 1.-how to handle the FileSystemObject to safe a phisical xml-file on my computer. 2.-where do i place my dsn-less connection string and table-name in the asp-page if i want the process work automatically? Help would be greatly appreciated! Cheers! Walter Sign In·View Thread·PermaLink 2.00/5 (1 vote) I don't correct output [modified] yogeshbhandare 23:56 25 Mar '07   An invalid character was found in text content. Error processing resource 'http://localhost/mysql/SourceCode/xmile.asp'. L... '<xml version='1.0'?><xml> When I run the application. It runs correct if the query returns two records. More than 2 record I records it gives this error Why does it happen that when we run the query ? select * from sysobjects for xml auto output get truncated I tried saving in a file the result is the same Thanks in advance -- modified at 5:03 Monday 26th March, 2007 yogesh is a good boy Sign In·View Thread·PermaLink Hmm... Shog9 11:30 15 Jul '02   I donno... Sorta reminds me of the article Convert any Table into XML format & view it using XSL, but without the cool "whole article is a hyperlink" feature... -------- PMGRE --Shog9 -- Sign In·View Thread·PermaLink Re: Hmm... Andrew Peace 13:15 15 Jul '02   You can just never please some people.... All fixed now. -- Andrew. Sign In·View Thread·PermaLink

Last Visit: Friday, November 21, 2008 3:08 AM     Last Update:Friday, November 21, 2008 3:08 AM