Import nested XML file to mysql database

need help

[dillonstars]

I am working with a large XML file (20mb) and getting it to display on a website by using XSLT transformations takes a very long time for the server to process, so I have decided to put the content of the feed into a mysql database.

I use Dbtools pro to manage my databases.

The trouble I am having is that the XML file contains multiple nested elements and the import wizard in dbtools won't import the sub elements.

What is the easiest way to get the xml data into a mysql database?

[cmarshall]

Probably XQuery [w3schools.com], but I don't know enough about it to give a definitive answer.

[dillonstars]

I can see how XQuery could achieve my original aim of parsing the XML file for use on my website, but not how it would get the xml file data into a mysql database.

I will have a look at XQuery to see if i don't actually need to go down the mySQL route at all, but i'm worried that working with a 20mb xml file on a live website will simply be too slow.

[cmarshall]

You are probably right about it being too slow.

My experience is that modern XSLT processors are pretty fast, but 20MB is a lot of data. You may want to consider "tweening" with PHP or something.

Which reminds me, PHP (and most server-based languages) tends to have some pretty robust XML parsers. You may not need to use XSLT at all.

My [mis]understanding of XQuery is that it is meant to REPLACE SQL. But maybe it is just a layer on top of SQL.

[dillonstars]

My understanding of XQuery is that it is used to query XML files, much the way that SQL queries databases. So yes it replaces SQL, but only as long as the XML file replaces the database (which isnt what i want to do).

I will certainly be looking at XQuery for some of my smaller XML duties though, it looks pretty simple.

I have had some other advice in the last hour that using PEAR to unserialze the XML file will help get it in a format to import into a database but I'm not very proficient with php, and the site is pretty unfriendly to noobs :)

[pear.php.net...]

[dillonstars]

You may want to consider "tweening" with PHP or something.

I had thought of that too, but i need to update the xml data weekly, and so was hoping to set up a cron job to import the data to my database automatically once a week.

Looks like some kind of php script will be the way to go. better get my learning cap on i think :)

[dillonstars]

I've found a technique that uses an XSLT transformation to restructure the XML data into a new XML file. This might get the data into a more streamlined format for me to import into a mySQL database. I will post my results when I try tomorrow.