4 Replies Latest reply on Feb 14, 2016 2:14 PM by beverly

    Import XML XSLT FMPXMLRESULT

    keith.r

      Hello!

      I've hit a road block while trying learn importing XML data into FMPA v14.

      First question is how to deal with <sky_condition />. Some records have more than one entry.

      Second Question is how to deal with <quality_control_flags>. Its seems to be nested with in the record.

       

      Any suggestions would be appreciated

       

      The XML source is : http://www.aviationweather.gov/adds/dataserver_current/current/metars.cache.xml

       

      This is my XSLT so far:

      <?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="altim_in_hg" TYPE="TEXT"/>
      <FIELD NAME="dewpoint_c" TYPE="TEXT"/>
      <FIELD NAME="elevation_m" TYPE="TEXT"/>
      <FIELD NAME="latitude" TYPE="TEXT"/>
      <FIELD NAME="longitude" TYPE="TEXT"/>
      <FIELD NAME="metar_type" TYPE="TEXT"/>
      <FIELD NAME="observation_time" TYPE="TEXT"/>
      <FIELD NAME="raw_text" TYPE="TEXT"/>
      <FIELD NAME="station_id" TYPE="TEXT"/>
      <FIELD NAME="temp_c" TYPE="TEXT"/>
      <FIELD NAME="wind_dir_degrees" TYPE="TEXT"/>
      <FIELD NAME="wind_speed_kt" TYPE="TEXT"/>
      </METADATA>
      <RESULTSET>
      <ROW>
      <COL><DATA><xsl:value-of select="@altim_in_hg"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@dewpoint_c"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@elevation_m"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@latitude"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@longitude"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@metar_type"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@observation_time"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@raw_text"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@station_id"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@temp_c"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@wind_dir_degrees"/></DATA></COL>
      <COL><DATA><xsl:value-of select="@wind_speed_kt"/></DATA></COL>
      </ROW>
      </RESULTSET>
      </FMPXMLRESULT>
        </xsl:template>
      </xsl:stylesheet>
      
        • 1. Re: Import XML XSLT FMPXMLRESULT
          beverly

          "more than one entry" & "nested" imply RELATED data:

          1. you can "loop" the data and push into one field as return delimited

          xsl:for-each

          2. you should import this data into another table (related) - remember to pass a key which may be temporary to set up the relationship and any real key(s)

           

          Try this XSLT to see the data in a way that may be beneficial. Import and the "paths" will be also shown.

           

          <http://jensteich.de/fmfaq/export/universal-xslt-fur-den-import-beliebiger-xml-daten/>

           

          Knowing the XPATH to your data is the most helpful is you wish to get your XSLT to correctly import.

           

          beverly

          • 2. Re: Import XML XSLT FMPXMLRESULT
            Menno

            Hi Keith,

             

            you'll need a few tweaks:

            <?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="altim_in_hg" TYPE="TEXT"/>  
                            <FIELD NAME="dewpoint_c" TYPE="TEXT"/>  
                            <FIELD NAME="elevation_m" TYPE="TEXT"/>  
                            <FIELD NAME="latitude" TYPE="TEXT"/>  
                            <FIELD NAME="longitude" TYPE="TEXT"/>  
                            <FIELD NAME="metar_type" TYPE="TEXT"/>  
                            <FIELD NAME="observation_time" TYPE="TEXT"/>  
                            <FIELD NAME="raw_text" TYPE="TEXT"/>  
                            <FIELD NAME="station_id" TYPE="TEXT"/>  
                            <FIELD NAME="temp_c" TYPE="TEXT"/>  
                            <FIELD NAME="wind_dir_degrees" TYPE="TEXT"/>  
                            <FIELD NAME="wind_speed_kt" TYPE="TEXT"/>
                            <FIELD NAME="quality_control_flags" TYPE="TEXT"/>
                        </METADATA>  
                        <RESULTSET>  
                            <xsl:for-each select="/response/data/METAR">
                                <ROW>  
                                    <COL><DATA><xsl:value-of select="altim_in_hg"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="dewpoint_c"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="elevation_m"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="latitude"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="longitude"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="metar_type"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="observation_time"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="raw_text"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="station_id"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="temp_c"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="wind_dir_degrees"/></DATA></COL>
                                    <COL><DATA><xsl:value-of select="wind_speed_kt"/></DATA></COL>
                                    <COL><DATA><xsl:value-of select="quality_control_flags/auto"/></DATA></COL>
                                </ROW>  
                            </xsl:for-each>
                        </RESULTSET>  
                    </FMPXMLRESULT>  
                </xsl:template>  
            </xsl:stylesheet> 
            

            As you can see the xsl:for0each was added as Beverly suggested, the quality_control_flags is just a different x-path. Also note that the other values are just plain selected without the @ in the x-path. That is because the values are in elements and not in attributes. The values in "sky_condition" are in attributes, as you can see in the next xslt.

            For the sky_condition you'll need to import the same xml another related table like:

            <?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="observation_time" TYPE="TEXT"/>  
                            <FIELD NAME="station_id" TYPE="TEXT"/>
                            <FIELD NAME="sky_cover" TYPE="TEXT"/>  
                            <FIELD NAME="cloud_base_ft_agl" TYPE="TEXT"/>
                        </METADATA>  
                        <RESULTSET>  
                            <xsl:for-each select="/response/data/METAR/sky_condition">
                                <ROW> 
                                    <COL><DATA><xsl:value-of select="../observation_time"/></DATA></COL>
                                    <COL><DATA><xsl:value-of select="../station_id"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="./@sky_cover"/></DATA></COL>  
                                    <COL><DATA><xsl:value-of select="./@cloud_base_ft_agl"/></DATA></COL>  
                                </ROW>  
                            </xsl:for-each>
                        </RESULTSET>  
                    </FMPXMLRESULT>  
                </xsl:template>  
            </xsl:stylesheet> 
            
            • 3. Re: Import XML XSLT FMPXMLRESULT
              keith.r

              Thanks for sharing! This helped me connect the dots. I do have a follow up question.

               

              Beverly suggested <thanks!> "push into one field as return delimited". From reviewing the CSV version of the same data set, looks like <sky_condition /> element has a maximum of 4 repetitions. <http://www.aviationweather.gov/adds/dataserver_current/current/metars.cache.csv>.


              sky_cover

              cloud_base_ft_agl

              _sky_cover

              _cloud_base_ft_agl

              __sky_cover

              __cloud_base_ft_agl

              ___sky_cover

              ___cloud_base_ft_agl

               

              Assuming these fields existed in FMP, could this be imported into a flat table? Does the XSLT support this?

               

              Thanks in advance!

              • 4. Re: Import XML XSLT FMPXMLRESULT
                beverly

                XSLT parses XML into XML, TEXT or HTML. As such '.csv' is text, however the key word is "into". It cannot "parse" text into XML (for import).

                 

                My suggestion of "return delimited" was to bring in 0-4 of these elements (maximum of 4 can be none!) into one field. But my preferred advice is to use related tables (on record per). Otherwise you'd have to go back and script pushing the "list" into separate records.

                 

                You cannot import directly into related table, you must create additional XSLT for just those segment in the same source XML. But you must also bring in whatever key links the related data to the parent record.

                 

                beverly