5 Replies Latest reply on Oct 16, 2016 6:13 PM by user19752

    Can you change which XSLT style sheet processor FileMaker uses when importing XML data?

    wpreid

      Good evening,

       

      I am importing a large amount of scientific data (formatted as XML files) into a Filemaker Pro 15 solution.  I have written an XSL transform that converts the original XML data files to the required FMPXMLRESULT grammar for importing XML data into FileMaker.  I thoroughly tested my XSLT on my local MacBook Pro using the Oxygen XML editor and the Saxon-PE XSLT style sheet processor and got the values I expected in my solution  when I imported the transformed data into FileMaker.

       

      However... When I automated the import process for my solution I decided to use the option built into the Import Records script step that automatically performs the transformation to FMPXMLRESULT if you provide a path to your XSLT file.  This appeared to work very well...  but upon closer inspection some of the transformations (particularly the ones where I convert values to different units - i.e. microvolts to volts) did not produce the correct results. 

       

      After some research, I believe the issue relates to the XSLT style sheet processor that FileMaker uses by default.   I have read that FileMaker uses the Xalan XSLT style sheet processor which (among other issues) only supports XSLT version 1.0.  To see if this is causing my transformation issues, I decided to try using Xalan to transform some of my XML test data using Oxygen.  When I did, I received the very same errors in the transformed values.  But by comparison, when I use the Saxon-PE XSLT style sheet processor, as mentioned earlier, I get all the correct values I expected. 

       

      So while I could perform the transformation myself (i.e. use the Saxon-PE XSLT style sheet processor to convert my source XML files into one big FMPXMLRESULT grammar XML file) I was really hoping to take advantage of this integrated transform step when importing XML data.

       

      Since I believe that the problem lies with FileMaker's use of Xalan, my question is simple...  Can you change which XSLT style sheet processor FileMaker uses when importing XML data?

       

      Thanks,

      Perry

        • 1. Re: Can you change which XSLT style sheet processor FileMaker uses when importing XML data?
          FabriceNordmann

          Hello,

           

          you can't change the xslt processor used by FileMaker, but there are ways to run a command line. (depending on the platform).

          That said, how do you divide by 1000? I can assure you that xslt 1.0 can do this    Can we take a look at your xslt?

          • 2. Re: Can you change which XSLT style sheet processor FileMaker uses when importing XML data?
            wpreid

            Hi Fabrice,

             

            Thanks for quick reply...  I agree, the math associated with the units conversion is not likely the issue so much as the format of the source data value I am converting from since many of the values I am getting from the device I am using are expressed in scientific notation (i.e. 1.798697303e-05).  Also, I misspoke earlier - I am converting Volts to MicroVolts so in my transform I am multiplying Volts by 1000000 to get Microvolts (1 Volt = 1000000 Microvolts).

             

            As requested, here are the areas of interest from my implementation starting with the source XML. 

             

            <Test>

              ...

              <StartTime>2016-04-25T11:50:10</StartTime>

              <TestStepType>TestStep_Analysis</TestStepType>

              <InitialAmplitude>1.798697303e-05</InitialAmplitude>

              <ImplicitTime>0.032395762</ImplicitTime>

              ...

            </Test>

             

            NOTE:  In the sample above, the value of interest is the <InitialAmplitude>.  It is the value that is not getting transformed correctly when FileMaker performs the transform. 

             

            Here are some excepts from my XSLT...

             

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

                
            <xsl:output method="xml"/>

                
            <!-- Constant used to convert volts to microvolts -->
                
            <xsl:variable name="VoltsToMicroVolts" select="1000000"/>
                
            <!-- Constant used to convert seconds to milliseconds (sec to ms) -->
                
            <xsl:variable name="SecondsToMilliseconds" select="1000"/>
                
                
            <xsl:template match="/">
                    
            <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
                        
            <METADATA>
                            
            <!--  Test Report Data -->

                                ... SKIPPING A BUNCH OF STUFF

                             <FIELD NAME="StartTime" TYPE="TIMESTAMP"/>
                            
            <FIELD NAME="TestStepType" TYPE="TEXT"/>
                            
            <FIELD NAME="InitialAmplitude" TYPE="NUMBER"/>
                            
            <FIELD NAME="ImplicitTime" TYPE="NUMBER"/>

                                ... SKIPPING A BUNCH OF STUFF

                         </METADATA>
                         <RESULTSET>
                             <xsl:apply-templates select="/root"/>
                         </RESULTSET>
                     </FMPXMLRESULT>
                 </xsl:template>

                

                 <xsl:template match="rffx">
                    
            <ROW>
                        
            <!-- Test Report Data -->
                        
            <xsl:apply-templates select="Report"/>

                         <xsl:apply-templates select="Report/Test[TestedSample = 'Both']" mode="summary"/>

                                ... SKIPPING A BUNCH OF STUFF

                     </ROW>
                
            </xsl:template>

             

                 <!-- Test Results Summary and Test Configuration Field Values -->
                 <xsl:template match="Test" mode="summary">

                              ... SKIPPING A BUNCH OF STUFF

                     <COL>
                         <DATA>
                             <xsl:value-of select="StartTime"/>
                         </DATA>
                     </COL>
                     <COL>
                         <DATA>
                             <xsl:value-of select="TestStepType"/>
                         </DATA>
                     </COL>
                     <COL>
                         <DATA>
                             <xsl:value-of select="(InitialAmplitude * $VoltsToMicroVolts)"/>
                         </DATA>
                     </COL>
                     <COL>
                         <DATA>
                             <xsl:value-of select="(ImplicitTime * $SecondsToMilliseconds)"/> 
                         </DATA>
                     </COL>

             

                              ... SKIPPING A BUNCH OF STUFF

             

                </xsl:template>

             

            Here are the results I have observed so far... When I transform my source XML file using the XSLT above and the Saxon-PE processor, I get the following values written to the FMPXMLRESULT grammar XML file:

             

                         <COL>
                             <DATA>2016-04-25T11:50:10</DATA>
                         </COL>
                         <COL>
                             <DATA>TestStep_Analysis</DATA>
                         </COL>
                         <COL>
                             <DATA>17.98697303</DATA>
                         </COL>
                         <COL>
                             <DATA>32.395762000000005</DATA>
                         </COL>

             

            But when I transform my source XML file using the XSLT above and the Xalan processor (either in Oxygen or by using FileMaker's Import Records -> XML Data Source), I get the following values written to the FMPXMLRESULT grammar XML file or imported to my solution:

             

                         <COL>
                             <DATA>2016-04-25T11:50:10</DATA>
                         </COL>
                         <COL>
                             <DATA>TestStep_Analysis</DATA>
                         </COL>
                         <COL>
                             <DATA>NaN</DATA>
                         </COL>
                         <COL>
                             <DATA>32.395762000000005</DATA>
                         </COL>

             

            The NaN (not a number) that appears in the 3rd data column above is what I am trying to get rid of. 

             

            Thanks for taking a look.

            Perry

            • 3. Re: Can you change which XSLT style sheet processor FileMaker uses when importing XML data?
              beverly

              You are correct in your assessments. And the answer is: not with Import. You may use plug-ins or web-based processing or other means to  automate processing outside FM then import.

              There are IDEAS posted requesting update of the processor & parser for XML & XSLT.

               

              Sent from miPhone

              1 of 1 people found this helpful
              • 4. Re: Can you change which XSLT style sheet processor FileMaker uses when importing XML data?
                wpreid

                Thanks Beverly. 

                 

                I will modify my solution so that I transform my source XML data to the FMPXMLRESULT grammar XML outside of FileMaker and then perform the import. 

                 

                 

                Perry.

                • 5. Re: Can you change which XSLT style sheet processor FileMaker uses when importing XML data?
                  user19752

                  For workaround on this case, make FM calculation instead of convert value, like

                   

                  <xsl:value-of select="concat(InitialAmplitude, '*',  $VoltsToMicroVolts)"/>

                   

                  then add auto-enter calculation (replace existing value) for the number field as

                   

                  Evaluate(Self)

                   

                  apply auto-enter when import.

                   

                  Auto-enter will be done on all fields, so this makes import slower, or unsuable if any other fields don't want it.