ado,access2xml,数据库到XML转换
2005/7/1 12:19:37
阅读全文(2397) | 回复(0) | 编辑 | 精华
译者：孤独 http://www.joysou.com/Article/Article_show.asp?Article_id=3209 你可以按照以下代码做这些转换. var rs1= new ActiveXObject("ADODB.Recordset");var adopenStatic = 3;var adLockReadOnly = 1;var adCmdText = 1;var adPersistXML = 1;rs1.Open( "SELECT * FROM products", "DSN=katiadb", adopenStatic, adLockReadOnly, adCmdText);rs1.Save ("D:\\katia\\test.xml", adPersistXML) 但是除非你有用最后版本的ADO,你不会允许用adPersistXML在save方法上. 这里有一个解决的方法.是用JS实现的. //<script>//<author>Chris@bayes.co.uk</author>//<website>http://www.bayes.co.uk</website>//<disclaimer>This software may be used as long as this//header stays intact. No responsibility is accepted//for use of this software.</disclaimer>//debugger;var rst = new ActiveXObject("ADODB.Recordset");var fso = new ActiveXObject("Scripting.FileSystemObject");var file = fso.CreateTextFile("productsdb.xml");file.WriteLine("<?xml version=\"1.0\"?>");file.WriteLine("<products>");tabletoxml("monitors", "monitor", "some monitors");tabletoxml("modems", "modem", "some modems");file.WriteLine("</products>");function tabletoxml(table, rows, descrip){file.WriteLine("<" + table + " name=\"" + descrip + "\">");//rst.Open("SELECT * FROM " + table , "Provider=MSDASQL;Data Source=katiadb;" )rst.Open("SELECT * FROM " + table , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb;")propstoxml(rst, "props", "prop");rst.MoveFirst();for (i=0; !rst.EOF; i++){recordtoxml(rst, rows);rst.MoveNext();}rst.Close();file.WriteLine("</" + table + ">");}function recordtoxml(r, n){var os = "<" + n + " ";for (i=0; i < r.fields.Count; i++){os += r.fields(i).name + "=\"" + r.fields(i).value + "\" ";}os += "/>";file.WriteLine(os);}function propstoxml(r, ns, n){file.WriteLine("<" + ns + "> ");for (i=0; i < r.properties.Count; i++){file.WriteLine("<" + n + " name=\"" + r.properties(i).name + "\" value=\"" + r.properties(i).value + "\" type=\"" + r.properties(i).type + "\" attributes=\"" + r.properties(i).Attributes + "\" />");}file.WriteLine("</" + ns + "> ");} 使用的时候引用这个文件,或者直接把这个文件COPY到代码中. 你需要改变以下代码. tabletoxml("modems", "modem", "some modems"); 从你希望用的表中选择数据元素和标题. 你将也需要改变SQL连接字符串去打开这个数据库. 大多数你也希望使用这样的删除语句.就像下面这样.(You will almost certainly want to remove the following line.) propstoxml(rst, "props", "prop"); 也许当我得到转了一圈的时间(get round to do something)并且有一个需要的化我会编写一个HTA控件来做一个转换的工具. 这是一个最通常的使用的程序所以我决定传见一个更加专业的版本来做ACCESS数据库的转换. accesstoxml.js //<script>//<author>Chris@bayes.co.uk</author>//<website>http://www.bayes.co.uk</website>//<disclaimer>This software may be used as long as this//header stays intact. No responsibility is accepted//for use of this software.</disclaimer>//debugger;var oArgs = WScript.Arguments;if (WScript.Arguments.Length < 1){WScript.Echo("Usage : dumpAccess.js fullDatabasePath [table]");WScript.Quit();}var dbpath = oArgs(0);var rst = new ActiveXObject("ADODB.Recordset");var fso = new ActiveXObject("Scripting.FileSystemObject");var dbname = dbpath.substr(dbpath.lastIndexOf("\\")+1, dbpath.lastIndexOf("."));var file = fso.CreateTextFile(dbpath.substr(0, dbpath.lastIndexOf(".")) + ".xml");file.WriteLine("<?xml version=\"1.0\"?>");if (WScript.Arguments.Length == 1){var theApp = new ActiveXObject("Access.Application");theApp.OpenCurrentDatabase(dbpath);var dbs = theApp.CurrentData;var tables = dbs.AllTables;file.WriteLine("<database name=\"" + dbname + "\" >");for (var i=0; i < tables.Count; i++){var name = tables(i).Name;if (name.substr(0, 4) != "MSys"){ // ignore system tablestabletoxml(name, "row", tables(i).Description, dbpath);}}file.WriteLine("</database>");}else{tabletoxml(oArgs(1), "row", oArgs(1), dbpath);}function tabletoxml(table, rows, descrip, dbname){file.WriteLine("<table name=\"" + table + "\" description=\"" + descrip + "\" >");//rst.Open("SELECT * FROM " + table , "Provider=MSDASQL;Data Source=katiadb;" )rst.Open("SELECT * FROM " + table , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath + ";")if (!(rst.BOF & rst.EOF)){rst.MoveFirst();for (var i=0; !rst.EOF; i++){recordtoxml(rst, rows);rst.MoveNext();}}rst.Close();file.WriteLine("</table>");}function recordtoxml(r, n){var os = "<" + n + " ";for (var i=0; i < r.fields.Count; i++){os += r.fields(i).name + "=\"" + r.fields(i).value + "\" ";}os += "/>";file.WriteLine(os);} cscript accesstoxml.js fullPathToAccess.mdb cscript accesstoxml.js fullPathToAccess.mdb tableName 你需要使用完全的路径到数据库(PS server.mapth),否则连接将会尝试打开一个在"start in"的文件夹 (folder win*/profiles/personal )(没看懂....) etc.
Posted by Qr on 2005/7/1 12:19:37
| 发表评论： |