Creating an XSL stylesheet to manage FileMaker Pro XML output

Question

I use XML to feed data to an InDesign template, and am in the process of switching from an older, simpler setup (mapped cells in an Excel spreadsheet) to FileMaker Pro. FileMaker Pro exports the XML in a slightly different format than the already-established XML structure in the InDesign document.

When exporting XML from FileMaker Pro, I have the option of using an XSL stylesheet, but I don't know how to create one (and tutorials I've found seem vague or assume I already know things that I don't).

FileMaker Pro export looks like this:

<?xml version="1.0" encoding="UTF-8" ?><!-- This grammar has been deprecated - use FMPXMLRESULT instead --><FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult"><ERRORCODE>0</ERRORCODE>
<DATABASE>Projects.fmp12</DATABASE>
<LAYOUT></LAYOUT>
<ROW MODID="1" RECORDID="2">
<Project_name></Project_name>
<City_1></City_1>
<Size></Size>
<Building></Building>
<City_2></City_2>
<Completion></Completion>
<Scope_of_work></Scope_of_work>
<Description></Description>
</ROW>

I need to write a stylesheet so the exported XML matches this structure, for each record:

<project>
    <project_title>
        <name></name>
        <city_1></city_1>
    </project_title>
    <project_information>
        <size></size>
        <building></building>
        <city_2></city_2>
        <completion></completion>
        <scope_of_work></scope_of_work>
    </project_information>
    <description></description>
</project>

Any advice would be greatly appreciated.

Answer 1

You didn't specify what the outer nodes of the result XML would look like or if it uses a namespace, but how's this:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:fm="http://www.filemaker.com/fmpdsoresult" exclude-result-prefixes="fm">
  <xsl:output method="xml" indent="yes"/>

  <xsl:variable name="rename">
    <item from="Project_name" to="name" />
    <item from="City_1" to="city_1" />
    <item from="Size" to="size" />
    <item from="Building" to="building" />
    <item from="City_2" to="city_2" />
    <item from="Completion" to="completion" />
    <item from="Scope_of_work" to="scope_of_work" />
    <item from="Description" to="description" />
  </xsl:variable>

  <xsl:template match="/*">
    <root>
      <xsl:apply-templates select="fm:ROW"/>
    </root>
  </xsl:template>

  <xsl:template match="fm:ROW">
    <project>
      <project_title>
        <xsl:apply-templates select="fm:Project_name | fm:City_1" mode="rename" />
      </project_title>
      <project_information>
        <xsl:apply-templates 
         select="fm:size | fm:Building | fm:City_2 | fm:Completion | fm:Scope_of_work"
         mode="rename" />
      </project_information>
      <xsl:apply-templates select="fm:Description" mode="rename" />
    </project>
  </xsl:template>

  <xsl:template match="*" mode="rename">
    <xsl:element name="{document('')//xsl:variable[@name = 'rename']/item[@from = local-name(current())]/@to}">
      <xsl:value-of select="."/>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>

When run on this input:

<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">
  <ERRORCODE>0</ERRORCODE>
  <DATABASE>Projects.fmp12</DATABASE>
  <LAYOUT></LAYOUT>
  <ROW MODID="1" RECORDID="2">
    <Project_name>The best project</Project_name>
    <City_1>New York</City_1>
    <Size>3</Size>
    <Building>Chrysler</Building>
    <City_2>Los Angeles</City_2>
    <Completion>2012-10-12</Completion>
    <Scope_of_work>Big</Scope_of_work>
    <Description>A fun project</Description>
  </ROW>
  <ROW MODID="1" RECORDID="2">
    <Project_name>A pretty good project</Project_name>
    <City_1>Chicago</City_1>
    <Size>4</Size>
    <Building>30 Fake St.</Building>
    <City_2>Charlotte</City_2>
    <Completion>2013-02-03</Completion>
    <Scope_of_work>Medium</Scope_of_work>
    <Description>A serious project</Description>
  </ROW>
</FMPDSORESULT>

Produces this:

<root>
  <project>
    <project_title>
      <name>The best project</name>
      <city_1>New York</city_1>
    </project_title>
    <project_information>
      <building>Chrysler</building>
      <city_2>Los Angeles</city_2>
      <completion>2012-10-12</completion>
      <scope_of_work>Big</scope_of_work>
    </project_information>
    <description>A fun project</description>
  </project>
  <project>
    <project_title>
      <name>A pretty good project</name>
      <city_1>Chicago</city_1>
    </project_title>
    <project_information>
      <building>30 Fake St.</building>
      <city_2>Charlotte</city_2>
      <completion>2013-02-03</completion>
      <scope_of_work>Medium</scope_of_work>
    </project_information>
    <description>A serious project</description>
  </project>
</root>