Merging 1600 XMLs Together to Query

I have 1200 XML files with an identical format but no schema. What's the best way to get that into an ACCESS files so I can start quering them.
FrylockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
If you have a list of all the XML files OR if the files have a "specific pattern" (ex:
file1.xml, file2.xml, file3.xml, etc), then you can iterate over all the files. Example;
Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

Dim file
For i = 1 To 20
  file = "c:\scripts\file" & i & ".xml"
  objAccess.ImportXML file, acAppendData
Next

Refer to the following for more details.
http://www.microsoft.com/technet/scriptcenter/resources/officetips/oct05/tips1020.mspx
0
jerryb30Commented:
Can you post a sample?
0
FrylockAuthor Commented:
Hielo - what is that - vbscript? Can I create a 'macro' and write that into the script?

Hielo - it's all the files in a directory. Is it possible to code it so that it appens all the files in a directory?

Jerryb30 - I can't. Proprietary data. But it's a simple XMLfile system.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hieloCommented:
Here is a complete working example:

/*
1. save this script as hielo.js under C:\Data
2. save the following two XML files under C:\Data
You can name them whatever you want. It makes no difference. The script will search
only for files ending in .xml
 
<?xml version="1.0"?>
<test>
 <username>Alpha</username>
 <password>a</password>
 <username>Beta</username>
 <password>b</password>
</test>
 
 
<?xml version="1.0"?>
<test>
 <username>Gamma</username>
 <password>g</password>
 <username>Delta</username>
 <password>d</password>
</test>
 
3. create a new database named Test.mdb under C:\Data
You do not need any tables.
 
4. Start > Run > cmd
 
5. At the command prompt type:
cscript c:\Data\hielo.js
 
6. When you see the access prompt window click "Open"
 
7. Done
 
*/
function GetFolderFileList(folderspec)
{
 
   var fso, f, f1, fc;
   var fileList = new Array();
   fso = new ActiveXObject("Scripting.FileSystemObject");
 
   f = fso.GetFolder(folderspec);
 
   fc = new Enumerator(f.files);
 
 
   while( !fc.atEnd() )
   {
   	if( /\.xml$/i.test(fc.item()) )
	{
		fileList[fileList.length] = fc.item();
	}
	fc.moveNext();
   }
 
return(fileList);
}
 
 
var XMLFilesFolder = "C:\\Data";
var DB_PATH="C:\\Data\\Test.mdb";
var objAccess = null;
var acAppendData = 2;
 
var files = GetFolderFileList(XMLFilesFolder);
if( files.length > 0 )
{
	var objAccess = new ActiveXObject("Access.Application");
	objAccess.OpenCurrentDatabase(DB_PATH)
	for( var i=0, limit=files.length; i < limit; ++i)
	{
		WScript.echo("\nimporting " + files[i]+" ...");
		objAccess.ImportXML(files[i], acAppendData);
		WScript.echo("Done");
	}
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jerryb30Commented:
If you have spec that imports one xml, you can loop through all xml's in a folder, and run the import.  1600 times sounds like a lot, but if these are small xmls, it will not take long.

How do you currently import manually?
0
FrylockAuthor Commented:
Heilo - Compilation error:  told me 'expected hexadecimal digit'. Code with a minor edit below.

Jerry - I've never done it before. I don't have a spec.


/*
1. save this script as hielo.js under C:\Data
2. save the following two XML files under C:\Data
You can name them whatever you want. It makes no difference. The script will search
only for files ending in .xml
 
<?xml version="1.0"?>
<test>
 <username>Alpha</username>
 <password>a</password>
 <username>Beta</username>
 <password>b</password>
</test>
 
 
<?xml version="1.0"?>
<test>
 <username>Gamma</username>
 <password>g</password>
 <username>Delta</username>
 <password>d</password>
</test>
 
3. create a new database named Test.mdb under C:\Data
You do not need any tables.
 
4. Start > Run > cmd
 
5. At the command prompt type:
cscript c:\Data\hielo.js
 
6. When you see the access prompt window click "Open"
 
7. Done
 
*/
function GetFolderFileList(folderspec)
{
 
   var fso, f, f1, fc;
   var fileList = new Array();
   fso = new ActiveXObject("Scripting.FileSystemObject");
 
   f = fso.GetFolder(folderspec);
 
   fc = new Enumerator(f.files);
 
 
   while( !fc.atEnd() )
   {
   	if( /\.xml$/i.test(fc.item()) )
	{
		fileList[fileList.length] = fc.item();
	}
	fc.moveNext();
   }
 
return(fileList);
}
 
 
var XMLFilesFolder = "C:\temp\xml";
var DB_PATH="C:\\temp\\xml\\Test.mdb";
var objAccess = null;
var acAppendData = 2;
 
var files = GetFolderFileList(XMLFilesFolder);
if( files.length > 0 )
{
	var objAccess = new ActiveXObject("Access.Application");
	objAccess.OpenCurrentDatabase(DB_PATH)
	for( var i=0, limit=files.length; i < limit; ++i)
	{
		WScript.echo("\nimporting " + files[i]+" ...");
		objAccess.ImportXML(files[i], acAppendData);
		WScript.echo("Done");
	}
}

Open in new window

0
hieloCommented:
I tested what I gave you. If it is not working, then YOU did something wrong.
This:
var XMLFilesFolder = "C:\temp\xml";
should be:
var XMLFilesFolder = "C:\\temp\\xml";

Two slashes to separate the folders.
0
FrylockAuthor Commented:
Yeah, I kind figured that, which is why I posted my code back to you to see where I messed it up. Works great now, though a little slower than I'd prefer. No worries though. Thanks!
0
FrylockAuthor Commented:
Great guys. sorry I can only give you points. Keep up the good work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.