I have an XML file like this which I need to import into a Filemaker database.
<propertys>
<property>
<ref>12345</ref>
<photos>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
</photos>
</property>
<property>
<ref>54321</ref>
<photos>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
</photos>
</property>
<property>
<ref>67890</ref>
<photos>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
<photo>http://www.url.com/image.jpg</photo>
</photos>
</property>
</propertys>
As you can see, there is an inconsistent number of photos per property.
I need to fit this into the following Filemaker XSLT import structure, which has to give an output of 36 photo rows per property whether there are 36 photos or not.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl">
<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="FileMaker" VERSION="ProAdvanced 12.0v4"/>
<DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>
<METADATA>
<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="Ref" TYPE="TEXT" />
<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="Image1" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image2" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image3" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image4" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image5" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image6" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image7" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image8" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image9" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image10" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image11" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image12" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image13" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image14" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image15" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image16" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image17" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image18" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image19" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image20" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image21" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image22" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image23" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image24" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image25" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image26" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image27" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image28" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image29" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image30" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image31" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image32" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image33" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image34" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image35" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Image36" TYPE="TEXT" />
</METADATA>
<RESULTSET FOUND="">
<xsl:for-each select="propertys/property">
<ROW RECORDID="" MODID="">
<!-- REF -->
<COL><DATA><xsl:value-of select="ref"/></DATA></COL>
<!-- here I need to output 36 rows whether the property has 36 photos or not to match the Filemaker definition -->
<COL><DATA></DATA></ROW> <!-- IMAGE 1 -->
<COL><DATA></DATA></ROW> <!-- IMAGE 2 -->
.... etc
</ROW>
</xsl:for-each>
</RESULTSET>
</FMPXMLRESULT>
If anyone could help me understand this, it would be much appreciated.
Many thanks.
You do not need to create 36 COLs for each ROW. Having declared 36 FIELDs in the METADATA section is enough. Try this stylesheet:
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<METADATA>
<FIELD NAME="Ref" TYPE="TEXT" />
<FIELD NAME="Image1" TYPE="TEXT" />
<FIELD NAME="Image2" TYPE="TEXT" />
<FIELD NAME="Image3" TYPE="TEXT" />
<FIELD NAME="Image4" TYPE="TEXT" />
<FIELD NAME="Image5" TYPE="TEXT" />
<FIELD NAME="Image6" TYPE="TEXT" />
<FIELD NAME="Image7" TYPE="TEXT" />
<FIELD NAME="Image8" TYPE="TEXT" />
<FIELD NAME="Image9" TYPE="TEXT" />
<FIELD NAME="Image10" TYPE="TEXT" />
<FIELD NAME="Image11" TYPE="TEXT" />
<FIELD NAME="Image12" TYPE="TEXT" />
<FIELD NAME="Image13" TYPE="TEXT" />
<FIELD NAME="Image14" TYPE="TEXT" />
<FIELD NAME="Image15" TYPE="TEXT" />
<FIELD NAME="Image16" TYPE="TEXT" />
<FIELD NAME="Image17" TYPE="TEXT" />
<FIELD NAME="Image18" TYPE="TEXT" />
<FIELD NAME="Image19" TYPE="TEXT" />
<FIELD NAME="Image20" TYPE="TEXT" />
<FIELD NAME="Image21" TYPE="TEXT" />
<FIELD NAME="Image22" TYPE="TEXT" />
<FIELD NAME="Image23" TYPE="TEXT" />
<FIELD NAME="Image24" TYPE="TEXT" />
<FIELD NAME="Image25" TYPE="TEXT" />
<FIELD NAME="Image26" TYPE="TEXT" />
<FIELD NAME="Image27" TYPE="TEXT" />
<FIELD NAME="Image28" TYPE="TEXT" />
<FIELD NAME="Image29" TYPE="TEXT" />
<FIELD NAME="Image30" TYPE="TEXT" />
<FIELD NAME="Image31" TYPE="TEXT" />
<FIELD NAME="Image32" TYPE="TEXT" />
<FIELD NAME="Image33" TYPE="TEXT" />
<FIELD NAME="Image34" TYPE="TEXT" />
<FIELD NAME="Image35" TYPE="TEXT" />
<FIELD NAME="Image36" TYPE="TEXT" />
</METADATA>
<RESULTSET FOUND="">
<xsl:for-each select="propertys/property">
<ROW>
<COL><DATA><xsl:value-of select="ref"/></DATA></COL>
<xsl:for-each select="photos/photo">
<COL><DATA><xsl:value-of select="."/></DATA></COL>
</xsl:for-each>
</ROW>
</xsl:for-each>
</RESULTSET>
</FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>
Having 36 identical fields is bad database structure. You should have a related table for the photos, where each photo would be a separate record. Then each property can have any number of photos.
Say I have two tables in Filemaker: Properties and Images. They are linked on a unique number ID field, PropID, which is created in Filemaker as records are added. How would I import using Filemaker's XML import step using the above XML and XSL and get the photos and data separately and into the correct tables?
This is by no means the only way to do this, but that's what I would do:
Images table, routing ref to a Images::TempPropertyID field.Properties table, routing Images::TempPropertyID to Properties::TempPropertyID; the Properties::TempPropertyID field needs to be validated as Unique, Validate always. TempPropertyID, to populate the (real) Images::PropertyID field with the value of the matching Properties::PropertyID (auto-entered during the import to Properties).TempPropertyID fields in both imported sets, to prevent collision with subsequent imports that might use the same ref number to refer to another property.