Filemaker xml export via xslt

Question

I am trying to export some data from our Filemaker database and convert it through XSLT

I have most of the way, but I'm stuck in some nested data:

Current XML output structure:

<?xml version="1.0" encoding="UTF-8"?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="07-18-2011" NAME="FileMaker" VERSION="ProAdvanced 11.0v4"/> <DATABASE DATEFORMAT="D/m/yyyy" LAYOUT="" NAME="bubbles.fp7" RECORDS="34604" TIMEFORMAT="k:mm:ss "/> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="description intro" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Description Line 1" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Description Line 2" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="description short" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="description W3" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Destination URL" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="EAN Code" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="product code W1" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="product group name" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="product name W6" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="stock_for_sale_quantity" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="UPC Code" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="web link standard" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="products_ADDITIONAL_IMAGES::full url" TYPE="TEXT"/> </METADATA> <RESULTSET FOUND="6"> <ROW MODID="5678" RECORDID="5051"> <COL> <DATA>description info here</DATA> </COL> <COL> <DATA> desc line 1</DATA> </COL> <COL> <DATA>desc line 2</DATA> </COL> <COL> <DATA>short description here</DATA> </COL> <COL> <DATA> Reall long description goes in here</DATA> </COL> <COL> <DATA>url to product</DATA> </COL> <COL> <DATA/> </COL> <COL> <DATA>SKU</DATA> </COL> <COL> <DATA>Acoustic guitars</DATA> </COL> <COL> <DATA>Epiphone EJ-200 Acoustic Guitar Vintage Sunburst EJ200</DATA> </COL> <COL> <DATA>0</DATA> </COL> <COL> <DATA>711106264509</DATA> </COL> <COL> <DATA>http://www.mywebsite.co.uk/catalog/product/view/id/15186</DATA> </COL> <COL> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_01.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_02.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_03.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_04.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_05.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/additional/epiej200vsbrst_06.jpg</DATA> <DATA>http://www.mywebsite.co.uk/store/images/uploads/epiej200vsbrst.jpg</DATA> </COL> </ROW> 

My XSL works to a certain extent, but I cannot get it to select each of the images:

  <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" version="1.0" exclude-result-prefixes="fmp"> <xsl:output method="xml" version="1.0" encoding="windows-1251" indent="yes"/> <xsl:template match="/"> <product> <product_count> <xsl:value-of select="count(//fmp:ROW)"/> </product_count> <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW"> <product_info> <description_info> <xsl:value-of select="fmp:COL[1]/fmp:DATA"/> </description_info> <desc_line_1> <xsl:value-of select="fmp:COL[2]/fmp:DATA"/> </desc_line_1> <desc_line_2> <xsl:value-of select="fmp:COL[3]/fmp:DATA"/> </desc_line_2> <short_description> <xsl:value-of select="fmp:COL[4]/fmp:DATA"/> </short_description> <long_description> <xsl:value-of select="fmp:COL[5]/fmp:DATA"/> </long_description> <dest_url> <xsl:value-of select="fmp:COL[6]/fmp:DATA"/> </dest_url> <ean> <xsl:value-of select="fmp:COL[7]/fmp:DATA"/> </ean> <sku> <xsl:value-of select="fmp:COL[8]/fmp:DATA"/> </sku> <group_name> <xsl:value-of select="fmp:COL[9]/fmp:DATA"/> </group_name> <product_name> <xsl:value-of select="fmp:COL[10]/fmp:DATA"/> </product_name> <stock_level> <xsl:value-of select="fmp:COL[11]/fmp:DATA"/> </stock_level> <upc> <xsl:value-of select="fmp:COL[12]/fmp:DATA"/> </upc> <web_link> <xsl:value-of select="fmp:COL[13]/fmp:DATA"/> </web_link> <position> <xsl:value-of select="fmp:DATA"/> </position> <images> <xsl:variable name="count" select="count(fmp:COL[14]/fmp:DATA)"/> <image_count> <xsl:value-of select="$count"/> </image_count> <xsl:for-each select="fmp:COL[14]/fmp:DATA[position()&lt;=$count]"> <img> <xsl:value-of select="position()"/> </img> </xsl:for-each> </images> </product_info> </xsl:for-each> </product> </xsl:template> 

This works, except for the bit, which gives me the correct number of tags, but no data

  <images> <image_count>7</image_count> <img/> <img/> <img/> <img/> <img/> <img/> <img/> </images> 

Anyone who can help with the last little cycle?

Answer 1

You make things a lot harder than you need:

 <xsl:for-each select="fmp:COL[14]/fmp:DATA"> <img> <xsl:value-of select="."/> </img> </xsl:for-each> 

will provide you

 <img>http://www.absolutemusic.co.uk/store/images/uploads/additional/epiej200vsbrst_01.jpg</img> <img>http://www.absolutemusic.co.uk/store/images/uploads/additional/epiej200vsbrst_02.jpg</img> .... 

As an aside, I had to write XMPT files in the past and keep myself sane (and I don't need to remember the correct column indices for each field). I used a trick like this:

 <xsl:variable name="mdf" select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD" /> <xsl:variable name="descIntro" select="count($mdf[ following-sibling::fmp:FIELD/@NAME = 'description intro']) + 1" /> <xsl:variable name="descL1" select="count($mdf[ following-sibling::fmp:FIELD/@NAME = 'Description Line 1']) + 1" /> 

etc. This creates a variable for each field name containing the correct index for that field, so you can use expressions like fmp:COL[$images] instead of fmp:COL[14] . This has an additional advantage: if you later change the export definition to include additional fields, XSLT will automatically compensate, you do not need to manually change all the numbers.