homepage Welcome to WebmasterWorld Guest from 54.211.7.174
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / XML Development
Forum Library, Charter, Moderators: httpwebwitch

XML Development Forum

    
Import nested XML file to mysql database
need help
dillonstars




msg:3361523
 9:25 pm on Jun 7, 2007 (gmt 0)

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




msg:3361532
 9:38 pm on Jun 7, 2007 (gmt 0)

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

dillonstars




msg:3361546
 10:04 pm on Jun 7, 2007 (gmt 0)

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




msg:3361553
 10:12 pm on Jun 7, 2007 (gmt 0)

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




msg:3361599
 10:59 pm on Jun 7, 2007 (gmt 0)

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




msg:3361608
 11:21 pm on Jun 7, 2007 (gmt 0)

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




msg:3361661
 12:23 am on Jun 8, 2007 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / XML Development
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved