FileMaker is capable of importing data from many different sources. One of the most useful is importing from XML data sources, which can be a great way to get data from non-FileMaker resources into your database. XML is used by all sorts of programs and applications, and so you may encounter it when working with many non-filemaker services or data sources.

About XML and XSLT

XML is a standard way of making writing annotations about data, but each XML document can be formatted in completely different ways. This is similar to how how filing cabinets usually store files using hanging folders which may contain folders, which may contain paper documents, but the way a particular person or company organizes files and folders in a cabinet can be very different from company to company and person to person.

An XSL style sheet and XSL Transformations (XSLT) provide instructions for applications about how to rewrite and XML file into a format the application can understand.

The FileMaker XML Data Format

FileMaker has a defined XML style of what “record data” looks like. It only knows how to import records as XML in this one particular style of XML formatting, called a Document Type Definition (DTD). This specific XML DTD which FileMaker understands is named FMPXMLRESULT. Any data that you want FileMaker to import must match the FMPXMLRESULT DTD, or else FileMaker can’t read it and won’t know what to do with the information. We can use XSL Transformations (XSLT) to convert (transform) non-FileMaker XML data into a FileMaker-friendly FMPXMLRESULT format.

FileMaker provides a description of the FMPXMLRESULT format at http://www.filemaker.com/help/html/import_export.16.30.html. This page describes which tags and attributes are required for FileMaker to successfully read an XML document in the FMPXMLRESULT format.

Let’s take a look at an example of FMPXMLRESULT-styled XML data. For this tutorial I’ve created a simple FileMaker weather database with one table with _kp_ID, City, Temperature, Humidity, Wind Speed, Wind Direction, and Clouds fields. I’ve exported these fields (other than the _kp_ID field) using FileMaker’s Export Records function to export the data as XML to get a sample for us to reference.

Download this sample file and we’ll use it to test our XML transformation and see the exported XML.

Download

<?xml version="1.0" encoding="UTF-8" ?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
	<ERRORCODE>0</ERRORCODE>
	<PRODUCT BUILD="04-17-2014" NAME="FileMaker" VERSION="ProAdvanced 13.0v3"/>
	<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="Weather.fmp12" RECORDS="1" TIMEFORMAT="h:mm:ss a"/>
	<METADATA>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="City" TYPE="TEXT"/>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Temperature" TYPE="NUMBER"/>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Humidity" TYPE="NUMBER"/>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Speed" TYPE="NUMBER"/>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Direction" TYPE="NUMBER"/>
		<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Clouds" TYPE="TEXT"/>
	</METADATA>
	<RESULTSET FOUND="1">
		<ROW MODID="1" RECORDID="1">
			<COL>
				<DATA>Alpharetta</DATA>
			</COL>
			<COL>
				<DATA>303.69</DATA>
			</COL>
			<COL>
				<DATA>40.2</DATA>
			</COL>
			<COL>
				<DATA>6.7</DATA>
			</COL>
			<COL>
				<DATA>170</DATA>
			</COL>
			<COL>
				<DATA>few clouds</DATA>
			</COL>
		</ROW>
	</RESULTSET>
</FMPXMLRESULT>

This is data that was exported from a FileMaker database. It shows the format that we’re going to need to match for FileMaker to be able to read our XML. Conveniently, Filemaker does not require all of the XML attributes that it export to be present when importing data, which makes our job easier.

In the FMPXMLRESULT format there are two areas we need to pay particular attention to. The first part is inside the tags. The <FIELD /> tags inside this section define the fields that each record contains. The attributes of each <FIELD /> show the name of the field and the type of data (text, number, etc). The other attribute data of each <FIELD /> is useful when bring data from FileMaker to other systems, but not necessary for us to use when importing data, so we’ll ignore it for now.

The order of the <FIELD /> tags is important, as the data values further down are read and matched with the <FIELD /> tags to determine what the data is. The <FIELD /> tags are essentially the column headers or column titles. The first <FIELD /> is matched with the first piece of column data, the second <FIELD /> is matched with the second piece of column data, etc.

Lower down we have the <RESULTSET> </RESULTSET> tags which contain the records and the field data for each record. Records (rows) are indicated by <ROW></ROW> and each field (column) in the record is indicated with <COL></COL>. The data contained within each field for each record is contained in <DATA></DATA> tags. We’ll have one set of <ROW></ROW> tags for each record we’re importing, and each one of those should have the same number of <COL></COL> and <DATA></DATA> tags as we have <FIELD /> tags in the <METADATA></METADATA> section at the top. This means we’ll have one column for each field that we’re importing.

Let’s get some XML data to import! Openweathermap.org provides XML weather data which we can use as a source for our import. XML weather data for Alpharetta is available at http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml which we can use for this example.

<?xml version="1.0" encoding="utf-8"?>
<current>
	<city id="4179574" name="Alpharetta">
		<coord lon="-84.29" lat="34.08"/>
		<country>US</country>
		<sun rise="2014-09-02T11:11:50" set="2014-09-03T00:01:23"/>
	</city>
	<temperature value="303.69" min="301.48" max="305.93" unit="kelvin"/>
	<humidity value="40" unit="%"/>
	<pressure value="1020" unit="hPa"/>
	<wind>
		<speed value="1.62" name="Light breeze"/>
		<direction value="279.503" code="W" name="West"/>
	</wind>
	<clouds value="40" name="scattered clouds"/>
	<visibility/>
	<precipitation mode="no"/>
	<weather number="211" value="proximity thunderstorm" icon="11d"/>
	<lastupdate value="2014-09-02T18:11:15"/>
</current>

This XML source provides all of the values we want to bring into the weather database, but the XML is formatted very differently than the FMPXMLRESULT format Filemaker requires. An XML transformation though the use of XSLT will solve that problem!

Transform the XML

Create a new text file on your computer and name it with a weather-transform.xslt. This file will provide FileMaker with the instructions on how to transform the XML data to get it into the FMPXMLRESULT format before trying to import it. Paste the code below into this document. This will be the base framework for building our XML transformation upon, and is required in an xslt document.

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/">

	</xsl:template>
</xsl:stylesheet>

Our transform instructions are going to go inside the <xsl:template></xsl:template> tags.

<xsl:template></xsl:template> tells FileMaker to start re-writing this xml document as soon as it hits the value set in the “match” attribute. In this case it’s set to match “/” which means “root” similar to a folder structure on Mac or Linux. Since we’re matching root the original XML is essentially going to be completely replaced with whatever we write. The hierarchy of tags within other tags are indicated using XPath, a notation format which is similar to how you would write out paths for files and folder in a computer filesystem.

A lot of this document is going to be exactly the same as the data that we exported from FileMaker before, so copy and paste that XML inside the <xsl:template></xsl:template> tags. delete the <?xml version=’1.0′ encoding=’UTF-8’?>, <ERRORCODE></ERRORCODE>, <PRODUCT />, and <DATABASE /> tags, as those are not required tags and will be ignored by FileMaker.  Also remove EMPTYOK, MAXREPEAT attributes from the <FIELD /> tags, FOUND=”1″ from <RESULTSET>, MODID=”1″ RECORDID=”1″ from <ROW>.  None of these attributes are required, so we can remove them to keep our document simpler and not lead to any confusion if the values don’t match our data.  You document should now look like this:

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/">
		<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
			<METADATA>
				<FIELD NAME="City" TYPE="TEXT"/>
				<FIELD NAME="Temperature" TYPE="NUMBER"/>
				<FIELD NAME="Humidity" TYPE="NUMBER"/>
				<FIELD NAME="Wind Speed" TYPE="NUMBER"/>
				<FIELD NAME="Wind Direction" TYPE="NUMBER"/>
				<FIELD NAME="Clouds" TYPE="TEXT"/>
			</METADATA>
			<RESULTSET>
				<ROW>
					<COL>
						<DATA>Alpharetta</DATA>
					</COL>
					<COL>
						<DATA>303.69</DATA>
					</COL>
					<COL>
						<DATA>40.2</DATA>
					</COL>
					<COL>
						<DATA>6.7</DATA>
					</COL>
					<COL>
						<DATA>170</DATA>
					</COL>
					<COL>
						<DATA>few clouds</DATA>
					</COL>
				</ROW>
			</RESULTSET>
		</FMPXMLRESULT>
	</xsl:template>
</xsl:stylesheet>

 This would technically work, but it would be bringing in the same data each time, and wouldn’t actually be reading the data in from the XML source.  We’d be re-writing the XML data source with static data, so we need to replace the values with the actual data values from our source.

The <xsl:value-of /> tag will tell FileMaker to read a specific value from the original XML to replace when writing out the transformed XML data.  This is the key part that lets us transform the data into the new format we’ve written into the XML transformation.

Starting with the city “Alpharetta” we want to replace the text with the actual city name from the XML we’re importing.  This value is located in the “name” attribute of the <city> tag, which is inside the <current> tag.  The XPath expression “current/city/@name” will get us this value.  Putting this value in the <xsl:value-of /> tag will tell FileMaker to replace the tag with the actual city name.  The tag would be <xsl:value-of select=”current/city/@name”/>.  Replace the text “Alpharetta” with that tag in the xslt document.

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/">
		<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
			<METADATA>
				<FIELD NAME="City" TYPE="TEXT"/>
				<FIELD NAME="Temperature" TYPE="NUMBER"/>
				<FIELD NAME="Humidity" TYPE="NUMBER"/>
				<FIELD NAME="Wind Speed" TYPE="NUMBER"/>
				<FIELD NAME="Wind Direction" TYPE="NUMBER"/>
				<FIELD NAME="Clouds" TYPE="TEXT"/>
			</METADATA>
			<RESULTSET>
				<ROW>
					<COL>
						<DATA><xsl:value-of select="current/city/@name"/></DATA>
					</COL>
					<COL>
						<DATA>303.69</DATA>
					</COL>
					<COL>
						<DATA>40.2</DATA>
					</COL>
					<COL>
						<DATA>6.7</DATA>
					</COL>
					<COL>
						<DATA>170</DATA>
					</COL>
					<COL>
						<DATA>few clouds</DATA>
					</COL>
				</ROW>
			</RESULTSET>
		</FMPXMLRESULT>
	</xsl:template>
</xsl:stylesheet>

In this state FileMaker would use the .xslt to read the original XML and then re-write it in the new format, replacing the city name with the actual data from the source XML. The rest of the data would still be the static numbers and text inside the <DATA></DATA> tags. Let’s do the same thing for the other values that we did for the city name, replacing each value with a <xsl:value-of select=”” /> tag with select attribute matching the source of the data we want for the field.

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/">
		<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
			<METADATA>
				<FIELD NAME="City" TYPE="TEXT"/>
				<FIELD NAME="Temperature" TYPE="NUMBER"/>
				<FIELD NAME="Humidity" TYPE="NUMBER"/>
				<FIELD NAME="Wind Speed" TYPE="NUMBER"/>
				<FIELD NAME="Wind Direction" TYPE="NUMBER"/>
				<FIELD NAME="Clouds" TYPE="TEXT"/>
			</METADATA>
			<RESULTSET>
				<ROW>
					<COL>
						<DATA><xsl:value-of select="current/city/@name"/></DATA>
					</COL>
					<COL>
						<DATA><xsl:value-of select="current/temperature/@value"/></DATA>
					</COL>
					<COL>
						<DATA><xsl:value-of select="current/humidity/@value"/></DATA>
					</COL>
					<COL>
						<DATA><xsl:value-of select="current/wind/speed/@value"/></DATA>
					</COL>
					<COL>
						<DATA><xsl:value-of select="current/wind/direction/@value"/></DATA>
					</COL>
					<COL>
						<DATA><xsl:value-of select="current/clouds/@name"/></DATA>
					</COL>
				</ROW>
			</RESULTSET>
		</FMPXMLRESULT>
	</xsl:template>
</xsl:stylesheet>

 This is a finished XSLT document!  We can now use this xslt file to transform our data from the http data source into a format FileMaker can understand for importing.

Run the Import

Open Weather.fmp12 and select File->Import Records->XML Data Source… Specify http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml as the data source and weather-transform.xslt as the XSL Style Sheet.

Press continue and you should be taken through the field-matching screen and be able to directly import your data from this XML data source.

You can read more about XSLT at W3 Schools, which has lots more information about the different XSL transformation functions, such as filtering, for-each loops, ifs, and embedded templates.

Source