Large XML Files

[BeeDev]

Not sure where to post this, apologies if this post doesn't belong here.

 

I have a client who has his own database which he cannot give access to. The only viable way for him to export the data is into an XML file and FTP it to the web server.

 

However, he has about 15.000 products in his database, and the resulting XML file is around 14MB in file size. Now I understand that this XML file is too big for a normal web server to efficiently process without exceeding the execution time (30sec) or running out of memory.

 

I just wanted to know if there's anyone out there who had a similar problem, and if you have any tips/solutions to import this big XML file to a SQL database, or even just work with it normally to search/pull out data directly from.

 

Any links to articles, tips, even a push in right direction will be appreciated.

 

Thank you.

 

P.S. The hosting is on a Windows machine, with ASP/.NET/PHP installed, and it's on a "Shared" hosting server, with very limited access to server configuration (if any at all).

Edited January 15, 2010 by BeeDev

[administrator]

Hi,

 

I would transform the XML data (offline) into an SQL dump or a simple comma delimited file. You want to strip out that extra XML because it's heavy. Once you have your more lighweight version of the data, then create a standard SQL table with it.

 

In both PHP and ASP.net, there are several functions/classes that will do this for you. I'm not sure which one will do it for you but in PHP, you may want to look at:

 

- Expat

- simpleXML

- XMLReader

 

.... And there are others.

 

Stefan

[BeeDev]

Thank you,

 

That's what I decided in the end, import it straight to Access (Access handles XML so gracefully on imports) and upsize it to SQL server.

[administrator]

Thank you,

 

That's what I decided in the end, import it straight to Access (Access handles XML so gracefully on imports) and upsize it to SQL server.

 

That works too!

 

It is important as a web designer/programmer to think outside the box (I hate using that expression!) where if say you are a PHP person, you may find using say ... Access, can solve the problem more easily for you. That kind of thinking found me using 8-9 languages over years as an active web developer - and I got the job done better and faster as a result.