Filemaker to MySQL XSL Stylesheet.

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp" >
<xsl:output method='text' version='1.0' encoding='iso-8859-1' indent='no'/>
<!--
The following stysheet allows to insert FileMaker data into mysql. It creates the
apropriate data structure from FileMaker METADATA and the INSERT statements.
It does not, however, create the database itself, nor does it support container fields.
Of course, you have to be careful to avoid special characters in your field names
(no spaces, ".", ";", ",", "@" etc. allowed).

USAGE: on the mysql pc, execute 
mysql databasename [-u<user[@host]>] [-p<password>] < output.sql

If you want to keep your existing table, comment out the "DROP TABLE" and CREATE TABLE
statements. This XSLT converts dates in M/d/yyyy (US) and d.M.yyyy (European) formats to
mysql internal date format. It is also implied that dateformat M/d/yyyy uses
US decimal separator "." and European dateformat uses ",". You may change that according
to your local date and number formats.

Enjoy.
Christian Jaeger
c.jaeger@ajholding.com
-->

<xsl:template match="fmp:FMPXMLRESULT">
<xsl:variable name="dateformat" select="fmp:DATABASE/@DATEFORMAT" /><!--comment this out to keep your data-->DROP TABLE IF EXISTS <xsl:value-of select="substring-before(fmp:DATABASE/@NAME, '.fp5')" />;
<xsl:variable name="table_name" select="substring-before(fmp:DATABASE/@NAME, '.fp5')" />
CREATE TABLE <xsl:value-of select="$table_name" /> (
recordid int(11) NOT NULL,
<xsl:for-each select="fmp:METADATA/fmp:FIELD" ><xsl:value-of select="@NAME" /><xsl:if test="@TYPE='TEXT'"> text</xsl:if><xsl:if test="@TYPE='NUMBER'"> float(16)</xsl:if><xsl:if test="@TYPE='DATE'"> date</xsl:if><xsl:if test="@TYPE='TIME'"> time</xsl:if><xsl:if test="@EMPTYOK = not( 'YES')">NOT NULL</xsl:if>,
</xsl:for-each>primary key(recordid));
<!--INSERT commands follows -->
<xsl:for-each select="fmp:RESULTSET/fmp:ROW">INSERT INTO <xsl:value-of select="$table_name" /> VALUES(<xsl:value-of select="@RECORDID" />,<xsl:for-each select="fmp:COL"><xsl:variable name="pos" select="position()" /><xsl:choose><xsl:when test="/*/*/fmp:FIELD[$pos]/@TYPE = 'DATE'">'<xsl:if test="$dateformat = 'd.M.yyyy'"><xsl:value-of select="substring(fmp:DATA,7,4)"/>-<xsl:value-of select="substring(fmp:DATA,4,2)"/>-<xsl:value-of select="substring(fmp:DATA,1,2)"/></xsl:if><xsl:if test="$dateformat = 'M/d/yyyy'"><xsl:value-of select="substring(fmp:DATA,7,4)"/>-<xsl:value-of select="substring(fmp:DATA,1,2)"/>-<xsl:value-of select="substring(fmp:DATA,4,2)"/></xsl:if>'</xsl:when><xsl:when test="/*/*/fmp:FIELD[$pos]/@TYPE = 'NUMBER'"><xsl:if test="$dateformat = 'd.M.yyyy'"><xsl:value-of select="number(translate(fmp:DATA,'.,',',.'))" /></xsl:if><xsl:if test="$dateformat = 'M/d/yyyy'"><xsl:value-of select="number(fmp:DATA)" /></xsl:if></xsl:when><xsl:otherwise>'<xsl:call-template name="replace-string"><xsl:with-param name="text" select="fmp:DATA"/><xsl:with-param name="from" select="'
'"/><xsl:with-param name="to" select="'\n'"/></xsl:call-template>'</xsl:otherwise></xsl:choose><xsl:if test="not (position()=  last())">,</xsl:if></xsl:for-each>);
</xsl:for-each>
</xsl:template>
<!--replace returns in FileMkaer text. Taken from Steve Ball's XSLTSL. You can find out more about that
package here: http://xsltsl.sourceforge.net/ -->

<xsl:template name="replace-string">
    <xsl:param name="text"/>
    <xsl:param name="from"/>
    <xsl:param name="to"/>

    <xsl:choose>
      <xsl:when test="contains($text, $from)">

	<xsl:variable name="before" select="substring-before($text, $from)"/>
	<xsl:variable name="after" select="substring-after($text, $from)"/>
	<xsl:variable name="prefix" select="concat($before, $to)"/>

	<xsl:value-of select="$before"/>
	<xsl:value-of select="$to"/>
        <xsl:call-template name="replace-string">
	  <xsl:with-param name="text" select="$after"/>
	  <xsl:with-param name="from" select="$from"/>
	  <xsl:with-param name="to" select="$to"/>
	</xsl:call-template>
      </xsl:when> 
      <xsl:otherwise>
        <xsl:value-of select="$text"/>  
      </xsl:otherwise>
    </xsl:choose>            
 </xsl:template>


</xsl:stylesheet>