Generating XSL Transforms for FileMaker

Writing XSL which generates FileMaker XML is somewhat painful, because you first generate a FileMaker “METADATA” section which lists the fields, and then generate the columns for each row, so it’s not always obvious that your columns are matching the field definitions.  Maintaining these fields later is also difficult.

While working on a project for a client, I thought of a solution that would allow us to generate the XSL from a simpler format file.  For this client, the input XML and  FileMaker fields were in a state of flux, and I wanted to be able to easily alter my XSL, and, perhaps, even to make it easy for the client to make changes in the future, if new fields were added or if the XML file schema changed.

For example, suppose you have an input XML file like:

<?xml version='1.0' encoding='us-ascii'?>
<company>
  <name>Soliant Consulting</name>
  <person id="10111">
    <name>
      <first>James</first>
      <last>Madison</last>
    </name>
    <address>
      <street1>555 Easy Lane</street1>
      <street2>Apt 1</street2>
      <city>Cambridge</city>
      <state>MA</state>
      <zip>02138</zip>
    </address>
    <email>fakeemail@soliantconsulting.com</email>
    <phone>617-555-5580</phone>
  </person>
  <person id="10112">
    <name>
      <first>John</first>
      <last>Adams</last>
    </name>
    <address>
      <street1>222 Sycamore Lane</street1>
      <city>Bedford Falls</city>
      <state>NY</state>
      <zip>00112</zip>
    </address>
    <phone>617-555-5555x202</phone>
  </person>
</company>

Rather than writing an awkward XSL file to extract people for FileMaker import, you’d write a simpler XML file:

<?xml version='1.0' encoding='us-ascii'?>
<mapping records="/company/person"
       
       >
  <field name='ID' type='NUMBER' select="@id"/>
  <field name="NameFirst"        select="name/first"/>
  <field name="NameLast"         select="name/last"/>
  <field name="AddrStreet">
    <xsl:apply-templates select="address"/>
  </field>
  <field name="AddrCity"         select="address/city"/>
  <field name="AddrState"        select="address/state"/>
  <field name="AddrZip"          select="address/zip"/>
  <field name="Phone"            select="phone"/>
  <field name="Email"            select="email"/>
  <field name="Company"          select="../name"/>

  <xsl:template match="address">
    <xsl:value-of select="street1"/>
    <xsl:if test="street2">
      <xsl:text>
</xsl:text> <!-- Newline between street1 and street2 -->
      <xsl:value-of select="street2"/>
    </xsl:if>
  </xsl:template>
</mapping>

Now you’ve got your fields right next to the XSL code which generates the field values.

From this, you could generate (or compile) the more complicated XSL file that you’ll want, but if you need to update anything in the output – say a field name, or the computation of a field, you can do it in the mapping file, rather than try to edit the XSL, and recompile.

There is a similar need for a way to generate external XML from a FileMaker XML file. The XSL for this can be nasty because FileMaker exports only allow the FMPXMLRESULT format, which makes it hard to find columns based on their fieldname, and thus adds a level of complexity to the code.You would write an input file like this:

<?xml version="1.0" encoding="utf-8"?>
<sc:Transform 
             >

<xsl:output method="xml" encoding="utf-8" indent="yes"/> 

<sc:document>
<people>
<sc:rows/>
</people>
</sc:document>

<sc:row>
  <person>
    <first><sc:field name="FirstName"/></first>
    <last><sc:field name="LastName"/></last>
    <title><sc:field name="Title"/></title>
    <fullname>
       <sc:field name="FirstName"/>
       <xsl:text> </xsl:text>
       <sc:field name="LastName"/>
    </fullname>
    <projects>
       <sc:for-each-field-value name="emp_PRO_Project::ProjectName">
          <project><xsl:value-of select="."/></project>
       </sc:for-each-field-value>
    </projects>

  </person>
</sc:row>
</sc:Transform>

and compiles it to an XSL file which can read a FileMaker export with fields named FirstName, LastName, Title, and emp_PRO_Project::ProjectName, and outputs the XML:

<?xml version="1.0" encoding="utf-8"?>
<people>
  <person>
    <first>James</first>
    <last>Madison</last>
    <title>Developer</title>
    <fullname>James Madison</fullname>
    <projects>
      <project>Payroll Rewrite</project>
      <project>Executive Briefing Program</project>
    </projects>
  </person>
  <person>
    <first>John</first>
    <last>Adams</last>
    <title>Project Manager</title>
    <fullname>John Adams</fullname>
    <projects>
      <project>Executive Briefing Program</project>
      <project>Acme Cake Company</project>
      <project>Surveys: Development Phase</project>
    </projects>
  </person>
</people>

Unfortunately, these examples are best built on MacOS or Linux (using xsltproc) to generate the XSL.

There are also some namespace issues if there are namespaces other than the FileMaker and empty namespaces in use.