From XML to MySQL

[wilco]

I have access to my local MLS's listings via an XML feed and need some advice for how to insert this data into a MySQL database. The first hurdle is taking the zipped xml file containing everything to initally populate my tables. I need to make sense of this xml file and determine how to convert it into a query that will insert all the relevant data correctly. Any advice or info on where to start looking would be much appreciated!

[Aaron]

Importing XML into MySql Can Be a interesting challenge.This software will allow you to do it manually, might help? http://www.navicat.com/

What you really need is a PHP script of some sort that will parse the XML. This page might help with that http://ca.php.net/xml


Aaron

[wilco]

You know, I actually have Navicat and just yesterday finally got it to import correctly.

You're right though, what I really need is a PHP script that I can run as a CRON job periodically to update the DB (manually doing that is of course the worst idea ever ;-) ).

I'll take a look at that link - thanks for the help! If anyone else knows of some good resources, feel free to point me towards them.

[PeterDillon]

you can use xslt to transform your xml into sql.

<?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xslutput method="text" />
<xsl:template match="text()" />

<xsl:template match="IDX_LISTING_DATA_FEED">
INSERT INTO
<xsl:value-of select="@tablename"/> (
<xsl:for-each select="LISTING/*">
<xsl:if test="position()=last()"><xsl:if test="starts-with(name(),'FIELD')"><xsl:value-of select="@fieldname"/></xsl:if></xsl:if>
<xsl:if test="position()!=last()"><xsl:if test="starts-with(name(),'FIELD')"><xsl:value-of select="@fieldname"/>,</xsl:if></xsl:if>
</xsl:for-each> ) VALUES (
<xsl:for-each select="LISTING/*">
<xsl:if test="position()!=last()"><xsl:if test="starts-with(name(),'FIELD')">'<xsl:value-of select="."/>',</xsl:if></xsl:if>
<xsl:if test="position()=last()"><xsl:if test="starts-with(name(),'FIELD')">'<xsl:value-of select="."/>'</xsl:if></xsl:if>
</xsl:for-each>);
</xsl:template>
</xsl:stylesheet>

is a style sheet im working on to convert csv into sql. You then just transform your source xml into sql using php or asp etc.

peter