Import XML how to

[Toni]

Excuse for the rookie question, but I can't figure out how to import an XML file. I understand that it needs a xsl stylesheet. But how can it be built? Funny thing is that Excel can import the file, it detects that it does not have a scheme for it, but creates it on the fly. From excel I can import the data, but I don't like to go that route.

So how can one built a xsl style sheet for filemaker? The xml file I want to import is in fact a tcx file of bicycle rides, and they are xml actually.

 

Any help? I understand the question is a bit general, but any pointing to the right direction would be helpful.

[Wim Decorte]

XSLT files are just text files with the proper extension, so you could code it in Notepad / TextEdit,...

The trick is getting the syntax right of course and for that you have to learn the "language".  There are some tools out there that offer drag and drop functionality to write the xslt but they are sort of expensive for a one-off use.

 

If you don't "speak" XSLT, you are probably better off sticking to the Excel route, or hiring someone to write the XSLT.

[Kris M]

XML imports is described here and other places

http://www.filemaker.com/help/11/fmp/html/import_export.16.19.html

Line 11 is the important one because not all XML is formatted for FileMaker to be able to ingest it without indigestion.

So in a nutshell you have to transform the XML using XSLT into a format that FM can swallow.

Heres a good place to start:

http://www.w3schools.com/xsl/

[comment]

So how can one built a xsl style sheet for filemaker? The xml file I want to import is in fact a tcx file of bicycle rides, and they are xml actually.

 

Why don't you attach an example?

 

XSLT is a language by itself, but in most cases you only need to know the most rudimentary basics (and be at least roughly familiar with Filemaker's FMPXMLRESULT grammar) in order to build a stylesheet for importing into Filemaker.

 

There are also numerous examples posted here that you could adapt.

[Toni]

Why don't you attach an example?

 

XSLT is a language by itself, but in most cases you only need to know the most rudimentary basics (and be at least roughly familiar with Filemaker's FMPXMLRESULT grammar) in order to build a stylesheet for importing into Filemaker.

 

There are also numerous examples posted here that you could adapt.

I didn't wanted to bother you guys with an attachment. It wood look like I am looking for you to do my work

Since you asked, I am going to attach it. I deleted a great part of the file, because it is really big. If you open it in Excel you would have a better understanding of what the fields are.

Thank you all for your replies. As I understand, first I need to learn the XSLT language, but I should understand the FMPXRESULT as well, right? To have a better idea, the XSLT is a file whihc tells FileMaker where to pick the fields from the XML, or it does transform the XML into another XML understandable for FileMaker? Is one XSLT file enough for many XML files, or would I need an XSLT for each of the XML files?

2015-01-11_Arbane1.zip

[Wim Decorte]

To have a better idea, the XSLT is a file whihc tells FileMaker where to pick the fields from the XML, or it does transform the XML into another XML understandable for FileMaker? 

 

The latter.  The XSLT will transform your XML into a grammar FM understands and can import from.  It does that on the fly though during the import.  That makes the import sometimes slow with big files.

 

You only need one XSLT for all XML files that are in the same format.

[comment]

Is one XSLT file enough for many XML files, or would I need an XSLT for each of the XML files?

 

You need a stylesheet for each specific transformation. Often a single XML file will contain data that needs to be imported into two or more tables of a relational database. In such case you will need a separate stylesheet for each table, that will extract the data that needs to be imported and organize it in the grammar that Filemaker knows how to import.

 

In your example, you would very likely want to import the Course data into a parent Courses table, then import the Trackpoint data (along with a Course id)  into a child Trackpoints table (not sure where the Lap entity figures in this). Here's an example of a stylesheet which you could use when importing the Trackpoints:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:tcx="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2"
exclude-result-prefixes="tcx">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE>
  <PRODUCT BUILD="" NAME="" VERSION=""/>
  <DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>
 
  <!-- DEFINE FIELDS -->
  <METADATA>
   <FIELD NAME="Course" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Date" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Time" TYPE="TIME" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Latitude" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Longitude" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Altitude" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>
   <FIELD NAME="Distance" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>
  </METADATA>
 
  <!-- IMPORT DATA -->
  <RESULTSET FOUND="">
   <xsl:for-each select="tcx:TrainingCenterDatabase/tcx:Courses/tcx:Course/tcx:Track/tcx:Trackpoint">
    <ROW MODID="" RECORDID="">
     <COL><DATA><xsl:value-of select="../../tcx:Name"/></DATA></COL>
     <COL><DATA><xsl:value-of select="substring(tcx:Time, 1, 10)"/></DATA></COL>
     <COL><DATA><xsl:value-of select="substring(tcx:Time, 12, 8)"/></DATA></COL>
     <COL><DATA><xsl:value-of select="tcx:Position/tcx:LatitudeDegrees"/></DATA></COL>
     <COL><DATA><xsl:value-of select="tcx:Position/tcx:LongitudeDegrees"/></DATA></COL>
     <COL><DATA><xsl:value-of select="tcx:AltitudeMeters"/></DATA></COL>
     <COL><DATA><xsl:value-of select="tcx:DistanceMeters"/></DATA></COL>
    </ROW>
   </xsl:for-each>
  </RESULTSET>
</FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Note the use of a prefix for addressing the elements in your source XML's "private" namespace.

[Toni]

Thank you @comment for the sample. I will play a little bit and come back with some results if I can