Use modular XSLT to simplify importing XML data into FileMaker

A simple XSLT file encapsulates the target FileMaker grammar into easy-to-use parameterized templates; once written, the file can power any import task with no changes.

To import XML data into FileMaker you have to write:

• Elements of the source grammar;
• Elements of the target grammar, FMPXMLRESULT;
• A list of instructions to transform the former into the latter.

You can put all of this into a single file and it will work; almost all XSLT samples are written this way. But as you face more and more complex import task, you start seeing the drawbacks of this approach:

• You'll have to repeat all the verbose syntax of the target grammar for every XSLT file you write;
• A mix of source and target elements and XSLT instructions can become hardly readable, that is error-prone and difficult to manage.

The universal way to manage complexity is to break something complex into modules that hide all the complexity beneath a simple interface. Let see how it can be done with XSLT.

Here's the FileMaker.xslt file that contains all the FMPXMLRESULT structure as it must be organized. The file contains four named templates: TABLE, FIELD, ROW and COL. Each template has a number of parameters; for example, FIELD has four: NAME, TYPE, MAXREPEAT and EMPTYOK.

<xsl:template name="FIELD">
  <xsl:param name="NAME">UNKNOWN</xsl:param>
  <xsl:param name="TYPE">TEXT</xsl:param>
  <xsl:param name="MAXREPEAT">1</xsl:param>
  <xsl:param name="EMPTYOK">YES</xsl:param>
  ...

If it were a function you would be able to call it like this: FIELD( NAME, TYPE, MAXREPEAT, EMPTYOK ). XSLT syntax is more verbose:

<xsl:call-template name="FIELD">
  <xsl:with-param name="NAME"      select="'Sample field name'"/>
  <xsl:with-param name="TYPE"      select="'TEXT'"/>
  <xsl:with-param name="MAXREPEAT" select="'1'"/>
  <xsl:with-param name="EMPTYOK"   select="'1'"/>
</xsl:call-template>

but since most parameters either aren't required or have default values, you don't need to enter all of them every time. The really required parameters are:

• TABLE( METADATA-FIELDS, RESULTSET-RECORDS )
• FIELD( NAME )
• ROW( COLS )
• COL( DATA ).

What does a template do? Let's look at the COL template:

<xsl:template name="COL">
  <xsl:param name="DATA"/>
  <COL><DATA><xsl:value-of select="$DATA"/></DATA></COL>
</xsl:template>

You can see it just takes the parameter and puts it into a proper XML structure for FileMaker to import. This template is the simplest; others are more complex but the principle is same.

How does it help?

• Write this file once and then simply include in other templates;

  <xsl:include href="FileMaker.xslt"/>

  you don't need to change it.

• The file with templates has no data, only placeholders, so the code remains clear and readable. It's just difficult to make a mistake here.

• The unnecessary details are hidden. You don't need to know FileMaker requires <COL> and <DATA> tags, neither you need to type them. All you have to do is to all the <COL> template and pass a single parameter.

  <xsl:call-template name="COL">
    <xsl:with-param name="DATA" select="any-xpath-expression"/>
  </xsl:call-template>
  

Hands-on training

Let's spend 15 minutes to try the modular XSLT on importing some data from RSS feeds.

Make a FileMaker table RSS with 3 fields: Title, Link and Description or grab the Sample RSS.fp7.

Download the FileMaker.xslt file and the Simple import.xslt that uses FileMaker.xslt for a simple generic one-field import. Here's the full listing of Simple Import.xslt:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns="http://www.filemaker.com/fmpxmlresult">

<xsl:include href="FileMaker.xslt"/>

<xsl:template match="/">
  <xsl:call-template name="TABLE">
    <xsl:with-param name="FIELDS">
      <xsl:call-template name="FIELD">
        <xsl:with-param name="NAME" select="'Sample field name'"/>
      </xsl:call-template>
      <!-- more FIELDs -->
    </xsl:with-param>

    <xsl:with-param name="RECORDS">
      <xsl:for-each select=""> <!-- the elements you're importing -->
        <xsl:call-template name="ROW">
          <xsl:with-param name="COLS">
            <xsl:call-template name="COL">
              <xsl:with-param name="DATA" select=""/> <!-- element attribute -->
            </xsl:call-template>
            <!-- more COLs in the same order as FIELDs -->
          </xsl:with-param>
        </xsl:call-template>
      </xsl:for-each>
    </xsl:with-param>

</xsl:template match>

Duplicate the Simple Import.xslt to Simple RSS import.xslt. Change the list of fields:

...
<xsl:call-template name="FIELD">
  <xsl:with-param name="NAME" select="'Title'"/>
</xsl:call-template>
<xsl:call-template name="FIELD">
  <xsl:with-param name="NAME" select="'Link'"/>
</xsl:call-template>
<xsl:call-template name="FIELD">
  <xsl:with-param name="NAME" select="'Description'"/>
</xsl:call-template>
...

the select statement:

<xsl:for-each select="//item">

and the list of cols:

...
<xsl:call-template name="COL">
  <xsl:with-param name="DATA" select="title"/>
</xsl:call-template>
<xsl:call-template name="COL">
  <xsl:with-param name="DATA" select="link"/>
</xsl:call-template>
<xsl:call-template name="COL">
  <xsl:with-param name="DATA" select="description"/>
</xsl:call-template>
...

Save the file (here's the final version of Simple RSS Import.xslt) and go back to FileMaker. Let's use, say, FMPro.org news as a sample RSS feed. Copy the link above to the clipboard and select the “Import Records > XML Data Source” command. Paste the link into the HTTP request field and then select the Simple RSS import.xslt as the XSLT stylesheet.

Now import the data.

You're done :)

This method works fairly well for simple imports up to, say, 15-20 fields. Beyond this number the lists of fields become difficult to manage and things turn even worse if you have to import different parts of source XML file into different FileMaker tables. Next time I'll tell about a more powerful “mapping” method.

Technorati tags: FileMaker, FileMaker 7, FileMaker 8, XML, XSLT.

Comments

[Veselko Pesut]

Great article!
It helps me to start with xslt and FileMaker.

Thanx Mikhail!

[Todd Geist]

I am getting an error every time I try the final versions of you example files.

XSLT Error Failed to process stylesheet (, line -1, column -1)

I am using OSX FIleMaker 8

Any ideas?

Thanks

Todd

[m.edoshin]

Tried to do this and got the same error. It disappeared after I had reselected the XSLT stylesheet in the "Import" dialog (i.e. pressed the "Specify" button and selected the "Simple RSS import.xslt" again). Not sure what causes this.

[Patrik Bergvall]

Great article.

I´m never tried importing xml before but now I have an oai-pmh compliant system which from which I would like to import data into Filemaker.

I´m trying to use this as a source: http://dspace.hh.se/dspace-oai/request?verb=ListRecords&metadataPrefix=oai_dc

and applying a stylesheet but whenever I try to import, Filemaker says "empty expressions" "remaining tokens".

I don´t know what causing it. I suppose the last line in the xml output, which contains

messes everything up.

Any ideas would be greatly appreciated.

//Patrik :-)

[Dwane]

Great Article. I'm trying to copy the code you've got there and substitute my own xPath's for my particular xml file that I'm trying to import. (1 day old Xpath user)

It doesn't error for me now and brings up the import box with field mappings, but the problem is, it doesn't import any data or create any records.

I changed the:

to:

and also I changed the:

to:

I also downloaded the Filemaker.xslt file and edited the:

To have the "any-xpath-expression" in there.

I kinda fumbling around in the dark here...Any ideas what I'm doing wrong?

[Dwane]

Oops. It doesn't seem to post that code...Putting it in brackets doesn't seem to help either...Don't know what to do then....

[Dwane]

Hi Mikhail,

Just wondering whether you are interested in writing an XSLT for me. I can offer USD$250 for it. I can forward the XML file that I need 'parsed'for you to evaluate before you decide. I'd like to be able to understand how to modify it to add more data when the XML standard (MXML) is updated later with more variables. You should have my email from this comment. If not Here it is: dwane[a--t]hollands[dot--][--com--][---dot][a-u]. Simply remove all the dashes etc etc. Thanks.

[paolo di mauro]

hallo,

i'm not able to import a file including only one tag
using the two files provided

I get no errors but result of import is always 0 records

[Rick Shepherd]

I am trying to get values from multiples of a node in the same file.
This example finds the correct 'number' of nodes, but 'selects' the data from the first node for however many multiples of the node there are in the file.
Please help ...
thx
Rick

[Mikhail Edoshin]

Hello, Rick,

Please provide more information; it's best to write me an email to m.edoshin-(at)-mac.com.

[Larsschulz]

What is the prefered method, if I want to reexport to XML in the same format as the sourcefile. Is there a way to get the same structure (without the filemaker mumbojumbo).

Like this: Source XML goes in - Manipulation in Filemaker - Export to XML with same format as input file...

Cheers

Lars

[Mikhail Edoshin]

Hi Lars,

There's no automatic tool I'm aware of, although it may be possible to write one. (Also, nowadays I consider the method above to be rather awkward too, and use a much leaner one.) Here's a link to a StackOverflow question I once answered with a good example of an export stylesheet:

http://stackoverflow.com/questions/2994751/filemaker-xsl-select-column-by-name/

Feel free to drop me a note to m.edoshin-(you know what)-mac com.