CODEWITHSUNDEEP
xmlxsltfilemaker
wdpixx
wdpixx

Reputation: 13

XSLT field names from Filemaker Pro

I'm new to XSLT and I'm trying to convert an XML file generated from Filemaker Pro using the fmpxmlresult grammar. Filemaker outputs a RAW XML in this way:

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
   <ERRORCODE>0</ERRORCODE>
   <PRODUCT BUILD="02-13-2018" NAME="FileMaker" VERSION="ProAdvanced 16.0.5" />
   <DATABASE DATEFORMAT="D/m/yyyy" LAYOUT="" NAME="fatture elettronica.fmp12" RECORDS="1" TIMEFORMAT="k:mm:ss " />
   <METADATA>
      <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="data" TYPE="DATE" />
      <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ID" TYPE="TEXT" />
      <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="righe::descrizione" TYPE="TEXT" />
      <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="righe::prezzo" TYPE="TEXT" />
   </METADATA>
   <RESULTSET FOUND="1">
      <ROW MODID="1" RECORDID="1">
         <COL>
            <DATA>31/08/2018</DATA>
         </COL>
         <COL>
            <DATA>1</DATA>
         </COL>
         <COL>
            <DATA>patate</DATA>
            <DATA>pomodori</DATA>
            <DATA>uva</DATA>
         </COL>
         <COL>
            <DATA>100</DATA>
            <DATA>50</DATA>
            <DATA>70</DATA>
         </COL>
      </ROW>
   </RESULTSET>
</FMPXMLRESULT>

In my XSLT I select the fields using 

<xsl:value-of select="fmp:COL[1]/fmp:DATA"/>

It works but it's a little time confusing because I have a XML file with a lot of fields and it's easy to adress the wrong field by number. Is there a way to select a field using the name of field that is listed in the metadata section? I tried to search but I cannot even imagine the correct keywords to look for. Thanks

Upvotes: 1

Views: 656

Answers (3)

wdpixx
wdpixx

Reputation: 13

I solved in this way:

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

then I call the variable:

<xsl:value-of select="fmp:COL[$ID]/fmp:DATA"/>

Upvotes: 0

AndreasT
AndreasT

Reputation: 2337

You can define a key to hold the field names and fetch the name from it by using the count function.

Something like this.

<!-- Define a key to get the first field and all fields that follow it by the field name -->
<xsl:key name="K" match="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD" use="@NAME" />
<xsl:key name="K" match="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD" use="following-sibling::fmp:FIELD/@NAME" />


<!-- Shortform to get a value for an attribute -->
<MyField  SomeAttr="{fmp:COL[count(key('K', 'SomeAttr'))]}"/>        

<!-- Format for getting a value for an element -->
<MyField>
    <xsl:value-of select="fmp:COL[count(key('K', 'MyField'))]/fmp:DATA" />
</MyField>

This method also has the advantage that if you change your field export order in FileMaker, your XSLT will keep up. If you change the field name in FileMaker you just need to change the one statement in your XSLT where you fetch that field.

Upvotes: 1

Martin Honnen
Martin Honnen

Reputation: 167696

In pure XSLT/XPath 1 it is difficult to find a compact expression, for instance to select the column where the corresponding FIELD has the NAME attribute value ID you would need a complex select like <xsl:value-of select="fmp:COL[count($fields[@NAME = 'ID']/preceding-sibling::fmp:FIELD) + 1]/fmp:DATA"/>:

<?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"
    exclude-result-prefixes="fmp"
    version="1.0">

  <xsl:output method="html" indent="yes"/>

  <xsl:template match="/">
      <xsl:apply-templates select="//fmp:ROW"/>
  </xsl:template>

  <xsl:variable name="fields" select="//fmp:FIELD"/>

  <xsl:template match="fmp:ROW">
    <xsl:value-of select="fmp:COL[count($fields[@NAME = 'ID']/preceding-sibling::fmp:FIELD) + 1]/fmp:DATA"/>
  </xsl:template>

</xsl:stylesheet>

https://xsltfiddle.liberty-development.net/94hvTzA/2

In XSLT 2 or 3 you could define a function returning the index of the FIELD you want based on the NAME attribute value and have a compact <xsl:value-of select="COL[mf:col-pos('ID')]/DATA"/> expression:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xpath-default-namespace="http://www.filemaker.com/fmpxmlresult"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="#all"
    version="3.0">

  <xsl:output method="html" indent="yes" html-version="5"/>

  <xsl:variable name="main-doc" select="/"/>

  <xsl:key name="field" match="FIELD" use="@NAME"/>

  <xsl:function name="mf:col-pos" as="xs:integer">
      <xsl:param name="field-name" as="xs:string"/>
      <xsl:sequence select="mf:col-pos($field-name, $main-doc)"/>
  </xsl:function>

  <xsl:function name="mf:col-pos" as="xs:integer">
      <xsl:param name="field-name" as="xs:string"/>
      <xsl:param name="field-anc" as="node()"/>
      <xsl:for-each select="key('field', $field-name, $field-anc)">
          <xsl:number/>
      </xsl:for-each>
  </xsl:function>

  <xsl:template match="/">
      <xsl:apply-templates select="//ROW"/>
  </xsl:template>

  <xsl:template match="ROW">
     <xsl:value-of select="COL[mf:col-pos('ID')]/DATA"/>
  </xsl:template>

</xsl:stylesheet>

Upvotes: 0

Related Questions