XML and MySQL

[Cuchulain]

I'm a total idiot when it comes to XML and I've just been given a URL which produces a live feed of property data for estate agents, I'm wondering how easy it'd be for me to import this data into a MySQL database, once it's actually in there I can write a whole search application around it but I'm stumped on the actual import, anyone have any ideas? I was thinking some form of app to extract the XML data which might need to be run as a cron job hourly or something. help (pretty please)

 

[JIMA]

Hi,

I guess unless you're dealing with a very fixed layout of XML you'd need database tables set up to record each element, attributes for the elements and some way of recording the relationship of the elements. The way I'd do it is to either user Perl or Java to parse the XML document and form data that would then be imported into the tables. Even with the data in a database it might be difficult to query on.

Have you looked at querying the XML directly using something like XPath, or parsing it using SAX etc?

Hope that helps,
Jim

 

[Cuchulain]

Hi,

I guess unless you're dealing with a very fixed layout of XML you'd need database tables set up to record each element, attributes for the elements and some way of recording the relationship of the elements. The way I'd do it is to either user Perl or Java to parse the XML document and form data that would then be imported into the tables. Even with the data in a database it might be difficult to query on.

Have you looked at querying the XML directly using something like XPath, or parsing it using SAX etc?

Hope that helps,
Jim

The XML field names will always be constant, there just might not be data in some of them, I was hoping to use PHP or at a pinch ASP to convert it as I know even less about Perl or Java, I'll take a look at Xpath and SAX.

Cheers

 

[Augmented]

If you're on PHP5, SimpleXML makes it very easy to extract data from XML.