Jump to content

Import XML how to


Toni
 Share

This topic is 2866 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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/

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2866 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.