Importing Multiple Random XML Files

[Graham Cookson]

Hi

Please help me - I've lost my hair trying to work this thing out.

Basically, I've been a seasoned personal user of FMP for years on my Macs and I finally persuaded my work to shift over to FMP. So they bought all the kit and they're running it on Windows 2000.

The problem: Our website has an online enquiry form that the designers have chosen to output as XML files which are stored on our webserver with both HTTP and ftp access. But I've had to use XML/XSL before. I need to find a way of getting this simple data into our database so we can print off packs for the enquirers and do the usual marketing malarky.

The XML files look like this:

<?xml version="1.0" ?>

- <enquirydata>

- <enquiry>

<title>Mr</title>

<firstname>Tony</firstname>

<lastname>Blair</lastname>

<emailaddress>[email protected]</emailaddress>

<address1>Number 10</address1>

<street>Downing Street</street>

<area>London Village</area>

<city />

<county>Greater London</county>

<postcode>W1C C1R</postcode>

<homephone>01222 222222</homephone>

<workphone>#</workphone>

<fax>#</fax>

<mobile>#</mobile>

</enquiry>

</enquirydata>

Which match exactly with the fields in our database akthough the field names are slightly different.

I think I need some sort of XSLT file to transform this XML into the kind of XML can use. How would I do this?

In addition, I want to automate the importing of the files as I work in a different department but when you go to the folder with the XML files they're all labelled using the date they were produced and are hence random. How the hell do I get FMP to handle this.

Oh, and finally what does the # sign mean and how do I deal with it?

Please, please help. I'm going crazy here.

Cheers

Graham

[LwoodPDowd]

DataMorph can convert your xml file to a tab delimited file which you could easily import into filemaker. It says the demo has a 500 record limit, but, I just used it to extract nearly 20,000 records from an xml file with a hierarchical structure into a flat file. Your conversion should be a piece of cake. I was using the Mac version, but there is a windows version as well.

http://www.qoppa.com/dmindex.html

There are a variety of other programs out there that should be able to do the job, but I had problems getting them to flatten my data. As yours it already flat you should have no problem.

http://www.xmlsoftware.com/convert.html

[Graham Cookson]

Thanks for this. I managed to work out how to write an XSLT file to allow the XML to be imported into Filemaker from some online tutorials on the web.

My only problem now is that I'm using a script made in scriptmaker that uses a HTTP get command to grab the file and then the import XML function with my new XSLT file to get that file in nicely but when this goes live there will be many XML files in the folder all with filenames generated by the online form that correspond to the date and time of creation. Not very helpful.

Any suggestions on this?

An additional hassle is that some of them will be old files already imported but I guess I can just add a find duplicates and delete step to my import script.

Thanks again for your input.

Graham

[new2fm]

Graham:

Can you please, share a reference to the XSLT file you created to import the XML file into FM.

I thinks it would be very useful for beginners in XML as myself.

Thanks in advance.

[QuinTech]

Hey new2fm, check out the FileMaker XSLT library. Great resource for learning.

Jerry

[Graham Cookson]

Sure thing new2fm. I can't warrant it as I hashed it together from bits of other files until I understood what the syntax all meant. However, it may be inelegant but it does work.

This transforms the original XML file from my original post into Filemaker using the Import command. Only problem for me now is finding a way to automate the import as the XML files produced by the website have random names.

Any help on this part appreciated folk.

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<!--

File: graham_transform.xslt

-->

<xsl:template match="/*">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="" NAME="" VERSION=""/>

<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="{count(/*/*)}" TIMEFORMAT="h:mm:ss a"/>

<METADATA>

<xsl:for-each select="/*/*[position()=1]/*">

<FIELD>

<xsl:attribute name="EMPTYOK">YES</xsl:attribute>

<xsl:attribute name="MAXREPEAT">1</xsl:attribute>

<xsl:attribute name="NAME"><xsl:value-of select="name()"/></xsl:attribute>

<xsl:attribute name="TYPE">TEXT</xsl:attribute>

</FIELD>

</xsl:for-each>

</METADATA>

<RESULTSET>

<xsl:attribute name="FOUND"><xsl:value-of select="count(child:*)"/></xsl:attribute>

<xsl:for-each select="child::*">

<ROW>

<xsl:attribute name="MODID">0</xsl:attribute>

<xsl:attribute name="RECORDID">0</xsl:attribute>

<xsl:for-each select="child::*">

<COL>

<DATA>

<xsl:value-of select="."/>

</DATA>

</COL>

</xsl:for-each>

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>