How to create XML from an Access (mdb) database with ASP
OK, so I recently created this code with VBS, but it would work with ASP as well. Basically, you need to place the script in a folder where it can get write permissions to create the XML file. Then you'll connect to the Access database like you would normally. Although to be perfectly honest, you could use this script to create an XML from data in just about any type of database that you can connect to!
Dim objConn, strConnect, strSQL, rs, tb, mdbFile, objFSO, xmlFile, objWrite
'=== filename variables
xmlFile = Server.MapPath(""inventory.xml")
mdbFile = Server.MapPath("database.mdb")
'=== tab character for xml file
tb = chr(9)
'=== instantiate objects
set objFSO = Server.CreateObject( "Scripting.FileSystemObject" )
Set objConn = Server.CreateObject( "ADODB.Connection" )
'=== connect to database
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile
'=== open/create xml file
If Not objFSO.FileExists( xmlFile ) Then objFSO.CreateTextFile( xmlFile )
set objWrite = objFSO.OpenTextFile( xmlFile, 2 )
'=== open the xml file
objWrite.WriteLine("<?xml version=""1.0"" encoding=""ISO-8859-1""?>")
objWrite.WriteLine("<data>")
strSQL = "SELECT * FROM table WHERE 1=1"
Set rs = objConn.Execute(StrSQL)
'=== loop through results
Do While not rs.EOF
objWrite.WriteLine(tb & "<item>")
objWrite.WriteLine(tb & tb & "<id>" & rs("id") & "</id>")
objWrite.WriteLine(tb & tb & "<product>" & replace(rs("product"),"&","&") & "</product>")
objWrite.WriteLine(tb & tb & "<color>" & rs("color") & "</color>")
objWrite.WriteLine(tb & tb & "<size>" & rs("size") & "</size>")
objWrite.WriteLine(tb & "</item>")
rs.MoveNext
Loop
'=== finish xml file
objWrite.WriteLine("</data>")
objWrite.Close()
