XSL to rearrange XML exported from Filemaker for Import into InDesign

[lanceomni]

I am working on an XSL file to reorder exported data from Filemaker. The filemaker data consists of a name, title, and list of projects through portal. Exporting to filemaker gives me the following:

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

<?xml-stylesheet href="resume.xsl" type="text/xsl"?>

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

    <ERRORCODE>0</ERRORCODE>

    <PRODUCT BUILD="01-25-2011" NAME="FileMaker" VERSION="Pro 11.0v3"/>

    <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="L30_CONTACTS_Data_Entry" NAME="DB_v0.2.03.fp7" RECORDS="29" TIMEFORMAT="h:mm:ss a"/>

    <METADATA>

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Name_Full" TYPE="TEXT"/>

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Title" TYPE="TEXT"/>

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="T05o_contacts_PROJECTS||id_contact|::Project_Name" TYPE="TEXT"/>

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="T05o_contacts_PROJECTS||id_contact|::Final Contract Value" TYPE="NUMBER"/>

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="T05o_contacts_PROJECTS||id_contact|::SF" TYPE="NUMBER"/>

    </METADATA>

    <RESULTSET FOUND="1">

        <ROW MODID="5" RECORDID="1854">

   	     <COL>

   		     <DATA>John Doe</DATA>

   	     </COL>

   	     <COL>

   		     <DATA>Project Manager</DATA>

   	     </COL>

   	     <COL>

   		     <DATA>Project Alpha</DATA>

   		     <DATA>Project Beta</DATA>

   		     <DATA>Project Gamma</DATA>

   		     <DATA>Project Delta</DATA>

   	     </COL>

   	     <COL>

   		     <DATA>$100,000</DATA>

   		     <DATA>$200,000</DATA>

   		     <DATA>$150,000</DATA>

   		     <DATA>$175,000</DATA>

 

   	     </COL>

   	     <COL>

   		     <DATA>1,000 SF</DATA>

   		     <DATA>2,000 SF</DATA>

   		     <DATA>1,800 SF</DATA>

   		     <DATA>2,500 SF</DATA>

   	     </COL>

        </ROW>

    </RESULTSET>

</FMPXMLRESULT>

 

What I would like to do is rearrange the data for import into Indesign. It would need to look something like:

 

<dataroot>

    <name>John Doe</name>

    <title>Project Manager</title>

    <project>

        <projectname>Project Alpha</projectname>

        <value>$100,000</value>

        <size>1,000 SF</size>

    </project>

    <project>

        <projectname>Project Beta</projectname>

        <value>$200,000</value>

        <size>2,000 SF</size>

    </project>

    <project>

        <projectname>Project Gamma</projectname>

        <value>$150,000</value>

        <size>1,800 SF</size>

    </project>

    <project>

        <projectname>Project Delta</projectname>

        <value>$175,000</value>

        <size>2,500 SF</size>

    </project>

<dataroot>

I am starting to understand the basics for simple queries and arranging XML but am stuck with the table like structure of what Filemaker exports. Having these items broken up as columns and rows has made it difficult. I think that I should be able to SELECT based on the attributes but all my attempts have washed up. What method should I use to convert this style XML into something like my above example?

Resume.zip

[comment]

In your example, you are exporting only one contact record - and the target format doesn't seem to have room for more. Is this going to be the rule here?

[lanceomni]

The current game plan is to select a single person. I believe exporting as mentioned above would be the easiest but I do have an existing layout which would accept the projects in one node separated by carriage breaks.

I may be interested in expanding down the road but how now I would like to take it one step at a time.

[comment]

I think you'll find it easier if you export from the Projects table - after doing GTRR [show related only] from the selected contact. Then you can create a <project> element for each <ROW>. Include the related name and title fields in the export and get them once from the first row.

[lanceomni]

I see that this is starting to get a bit more simplified but I still have some questions. I am now getting something like this.

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

    <ERRORCODE>0</ERRORCODE>

    <PRODUCT BUILD="01-25-2011" NAME="FileMaker" VERSION="Pro 11.0v3"/>

    <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="MossDB_v0.2.03.fp7" RECORDS="10" TIMEFORMAT="h:mm:ss a"/>

    <METADATA>

   	 <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Project_Name" TYPE="TEXT"/>

   	 <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Calc Current Project Value" TYPE="NUMBER"/>

   	 <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SF" TYPE="NUMBER"/>

    </METADATA>

    <RESULTSET FOUND="4">

   	 <ROW MODID="1" RECORDID="1">

   		 <COL>

   			 <DATA>Project Alpha</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>$100,000</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>1,000 SF</DATA>

   		 </COL>

   	 </ROW>

   	 <ROW MODID="2" RECORDID="2">

   		 <COL>

   			 <DATA>Project Beta</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>$200,000</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>2,000 SF</DATA>

   		 </COL>

   	 </ROW>

   	 <ROW MODID="3" RECORDID="3">

   		 <COL>

   			 <DATA>Project Gamma</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>$150,000</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>1,800 SF</DATA>

   		 </COL>

   	 </ROW>

   	 <ROW MODID="4" RECORDID="4">

   		 <COL>

   			 <DATA>Project Delta</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>$175,000</DATA>

   		 </COL>

   		 <COL>

   			 <DATA>2,500 SF</DATA>

   		 </COL>

   	 </ROW>

    </RESULTSET>

</FMPXMLRESULT>

So we could convert each ROW to a <PROJECT> but how do we handle the COL to <VALUE> and <SIZE>?

[comment]

You do this by following the export field order (there is another method that can find the value by field name, but that's much more complicated).

...

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

<project>

<projectname>

<xsl:value-of select="fmp:COL[1]/fmp:DATA"/>

</projectname>

<value>

<xsl:value-of select="fmp:COL[2]/fmp:DATA"/>

</value>

<size>

<xsl:value-of select="fmp:COL[3]/fmp:DATA"/>

</size>

</project>

</xsl:for-each>

...

[lanceomni]

I appreciate your help with this project and apologize for not responding to your last post. Your solutions worked great. I studied up on style sheets and was able to convert the information into a useable format.

Thanks again!

[samozaparola]

You do this by following the export field order (there is another method that can find the value by field name, but that's much more complicated).

	 <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

<project>

<projectname><xsl:value-of select="fmp:COL[1]/fmp:DATA"/></projectname>

<value><xsl:value-of select="fmp:COL[2]/fmp:DATA"/></value>

<size><xsl:value-of select="fmp:COL[3]/fmp:DATA"/></size>

</project>

</xsl:for-each>

Comment, or somebody else, can you please provide a complete XSL example? I tried the quoted code on an XML export of mine but I received this error:

XPath error: The prefix 'fmp' is not declared.

If I omit the "fmp:", the export returns nothing.

The "raw" xml code generated by my export looks like this:

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

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

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="06-11-2010" NAME="FileMaker" VERSION="ProAdvanced 11.0v2"/>

<DATABASE DATEFORMAT="D.m.yyyy" LAYOUT="PREGLED" NAME="hippocrates.fp7" RECORDS="28168" TIMEFORMAT="k:mm:ss "/>

<METADATA>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="START_DATE" TYPE="DATE"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="START_TIME" TYPE="TIME"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="MAIN_DIAG_MKB" TYPE="TEXT"/>

</METADATA>

<RESULTSET FOUND="5">

<ROW MODID="0" RECORDID="12226">

<COL>

<DATA>12.12.2008</DATA>

</COL>

<COL>

<DATA>10:00</DATA>

</COL>

<COL>

<DATA>J06</DATA>

</COL>

</ROW>

<ROW MODID="0" RECORDID="12227">

<COL>

<DATA>12.12.2008</DATA>

</COL>

<COL>

<DATA>10:20</DATA>

</COL>

<COL>

<DATA>J03</DATA>

</COL>

</ROW>

<ROW MODID="0" RECORDID="12228">

<COL>

<DATA>12.12.2008</DATA>

</COL>

<COL>

<DATA>10:40</DATA>

</COL>

<COL>

<DATA>J04</DATA>

</COL>

</ROW>

<ROW MODID="0" RECORDID="12229">

<COL>

<DATA>12.12.2008</DATA>

</COL>

<COL>

<DATA>11:20</DATA>

</COL>

<COL>

<DATA>J21</DATA>

</COL>

</ROW>

<ROW MODID="0" RECORDID="12230">

<COL>

<DATA>12.12.2008</DATA>

</COL>

<COL>

<DATA>11:50</DATA>

</COL>

<COL>

<DATA>J20</DATA>

</COL>

</ROW>

</RESULTSET>

</FMPXMLRESULT>

[comment]

What is the result you are after?

[samozaparola]

Comment, thank you very much for your answer! The results that are needed are pretty complex, so I just wanted some basic principles. But if you are willing to help with a particular example, you are quite welcome! Here is a simplified version of the task I am after:

My database consists of medical records. I need to make monthly reports and the XML has to look something like this:

<?xml version="1.0" encoding="windows-1251"?>

<Practice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PracticeSchema.xsd">

<PracticeCode>1234567890</PracticeCode>

<PracticeName>The name of the doctor's practice</PracticeName>

<DateFrom>01-12-2008</DateFrom>

<DateTo>2011-12-31</DateTo>

<ContrHA>0</ContrHA>

<Doctor>

<FullName>The name of the doctor</FullName>

<Visit>

<DateOfVisit>12-12-2008</DateOfVisit>

<TimeOfVisit>10:00:00</TimeOfVisit>

<CodeOfDiagnosis>J06</CodeOfDiagnosis>

</Visit>

<Visit>

<DateOfVisit>12-12-2008</DateOfVisit>

<TimeOfVisit>10:20:00</TimeOfVisit>

<CodeOfDiagnosis>J03</CodeOfDiagnosis>

</Visit>

<Visit>

<DateOfVisit>12-12-2008</DateOfVisit>

<TimeOfVisit>10:40:00</TimeOfVisit>

<CodeOfDiagnosis>J04</CodeOfDiagnosis>

</Visit>

<Visit>

<DateOfVisit>12-12-2008</DateOfVisit>

<TimeOfVisit>11:20:00</TimeOfVisit>

<CodeOfDiagnosis>J21</CodeOfDiagnosis>

</Visit>

<Visit>

<DateOfVisit>12-12-2008</DateOfVisit>

<TimeOfVisit>11:50:00</TimeOfVisit>

<CodeOfDiagnosis>J20</CodeOfDiagnosis>

</Visit>

</Doctor>

</Practice>

Even if I can transform the XML portion containing info about each visit (the "<Visit> </Visit>" element), with the help of XSLT, I don't see a way of populating the

<DateFrom>01-12-2008</DateFrom>

<DateTo>2011-12-31</DateTo>

portion, which represents the period from which the actual visits are. Maybe this can be done with scripting, but so far I have no idea how to pass the "period of time" variable used to find all the records during a particular time lag to the file with the exported results.

I found an example XSL, somewhat complex than your example:

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

<xsl:stylesheet version="1.0"

xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

exclude-result-prefixes="xsl fmp xsi">

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

<xsl:template match="/fmp:FMPXMLRESULT">

<ROOT>

<xsl:for-each select="fmp:RESULTSET/fmp:ROW">

<xsl:call-template name="getRecord">

<xsl:with-param name="subset" select="." />

</xsl:call-template>

</xsl:for-each>

</ROOT>

</xsl:template>

<xsl:template name="getRecord">

<xsl:param name="subset"/>

<RECORD>

<xsl:for-each select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD">

<xsl:variable name="pt" select="position()" />

<xsl:element name="[email="{@NAME"]{@NAME[/email]}">

<xsl:value-of select="$subset/fmp:COL[$pt]/fmp:DATA" />

</xsl:element>

</xsl:for-each>

</RECORD>

</xsl:template>

</xsl:stylesheet>

Why do I need to use the fmp prefix an then try to exlude it? Is there a particular way of forming XPath declarations in FileMaker?

[comment]

how to pass the "period of time" variable used to find all the records during a particular time lag to the file with the exported results.

There are several ways you could do this, for example: populate 2 global fields with the dates of first and last day of the reported month, and include them in your export. Alternatively, you could have the stylesheet look at the date of the first visit and calculate those two dates (assuming that the first visit is always in the reported month).

Why do I need to use the fmp prefix an then try to exlude it?

It's because Filemaker's XML output is its own namespace, and you need a prefix to address that namespace.

Is there a particular way of forming XPath declarations in FileMaker?

No. In fact, you don't do anything "in Filemaker" here. Filemaker calls a Xalan engine to do the XSL processing.

---

BTW, in your example the format of DateFrom is dd-mm-yyyy, while DateTo is yyyy-mm-dd. I mean, really..

Edited January 15, 2012 by comment

[comment]

Oh, I almost forgot: here is an example of a complete stylesheet as per your original request. I believe this should work with your export as is, though it still needs a solution for the parts that are currently hard-coded (e.g. the month boundaries).

practice#.zip

[samozaparola]

Thank you for your help, comment! Your example works great. Unfortunately I'm completely new to FileMaker and I don't know yet how to export data different from what is contained in a particular foundset.

So I was thinking of creating an XSL file on the fly in FileMaker with the info of the period containing all the foundsets:

period.xml:

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<DateFrom>dd-mm-yyyy</DateFrom>

<DateTo>yyyy-mm-dd</DateTo>

</xsl:template>

</xsl:stylesheet>

and import it into the original XSL (the one from your example). Something like this:

<?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:import href="period.xsl"/>

<xsl:output method="xml" version="1.0" encoding="windows-1251" indent="yes"/>

<xsl:template match="/">

<Practice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PracticeSchema.xsd">

<PracticeCode>1234567890</PracticeCode>

<PracticeName>The name of the doctor's practice</PracticeName>

<xsl:apply-imports/>

<ContrHA>0</ContrHA>

<Doctor>

<FullName>The name of the doctor</FullName>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

<Visit>

<DateOfVisit><xsl:value-of select="fmp:COL[1]/fmp:DATA"/></DateOfVisit>

<TimeOfVisit><xsl:value-of select="fmp:COL[2]/fmp:DATA"/></TimeOfVisit>

<CodeOfDiagnosis><xsl:value-of select="fmp:COL[3]/fmp:DATA"/></CodeOfDiagnosis>

</Visit>

</xsl:for-each>

</Doctor>

</Practice>

</xsl:template>

</xsl:stylesheet>

[comment]

I am not sure I follow. You cannot get the period data out of Filemaker unless it exists there. If it does exist there, then it can be exported along with the Visits data.

[samozaparola]

If I use a script to trigger the find, with the help of a plugin (I understand FileMaker doesn't have native support for writing text files), I can make that same sript generate a text (XSL) file containing info about the search criteria (the period.xml file above). If I have reference to it in the XSL used to transform the XML output in FileMaker export settings, I'll be able to connect the records data with the search criteria in the final export file.

Comment, can you please explain to me how can I export the period data along with the visits records using global fields as is your suggestion?

[comment]

If I use a script to trigger the find, with the help of a plugin (I understand FileMaker doesn't have native support for writing text files), I can make that same sript generate a text (XSL) file containing info about the search criteria (the period.xml file above). If I have reference to it in the XSL used to transform the XML output in FileMaker export settings, I'll be able to connect the records data with the search criteria in the final export file.

Well, yes - I suppose you could do something like that. You could also send the file for a trip around the world, while you're at it...

Comment, can you please explain to me how can I export the period data along with the visits records using global fields as is your suggestion?

See if this helps:

gexport.zip

[samozaparola]

Comment, I need lots more kisses and FileMaker practice before my files start their world travel...

Anyway, thank you very much for your help and invaluable examples! I owe you one.