7 Replies Latest reply on Mar 19, 2010 3:49 PM by comment_1

    Converting XML in FileMaker to Table Format

    BENTO-BOX

      Title

      Converting XML in FileMaker to Table Format

      Post

      Hi everyone,

       

      I am new to the FileMaker forum, as well as a novice to some of the advanced functions of FileMaker, but could anyone help me out with this problem?  (Update: I am using FileMaker Pro 10.)

       

      I have some XML within a FileMaker field, and would like to automatically convert it into a standard table format.

       

      I understand that exporting the XML, converting it to Excel, and then re-importing it back will indeed make it that way, but I was looking for a more automatic process.  (Perhaps using the script functions within FileMaker to parse the XML?)

       

      I also understand that, if the XML were outside the FileMaker database, then importing it would require the use of XSLT style sheets.  (However, in this case, the XML is within the FileMaker database.)

       

      I'd appreciate your help.  Thanks!

       

      *************************************************

       

      Here is the XML I am working with.  (Unfortunately, I cannot post the entire XML for some reason.)


       <GetClientListResult>
       <Clients>
        <Client ClientID="2iQs24qt0fcI" ClientName="3UK" ClientShortName="3UK" ClientNumber="" />
        <Client ClientID="2dR2QecHL9og" ClientName="Acer" ClientShortName="ACER" ClientNumber="" />
        <Client ClientID="2COiSYfuAf00" ClientName="Aigo" ClientShortName="AIGO" ClientNumber="" />
        </Clients>
        </GetClientListResult>
        </GetClientListResponse>
        </soap:Body>
        </soap:Envelope>

        • 1. Re: Converting XML in FileMaker to Table Format
          BENTO-BOX

          Does anyone have any ideas?

          • 2. Re: Converting XML in FileMaker to Table Format
            comment_1

            I don't see that you have too many options here: either export the field and import it back as XML, or parse it yourself using a looping script.

            • 3. Re: Converting XML in FileMaker to Table Format
              FentonJones

              As comment says, you can Export the field, as a single text field, single record (or Export Field Contents; but then you've got UTF-16), then Import XML, using a stylesheet. 

               

              Or, you can parse it yourself, via FileMaker Text Functions; which is a little clunky, but doable.

               

              Or, if you have FileMaker Pro Advanced, you could get a Custom Function, from Brian Dunning's site

              http://www.briandunning.com/filemaker-custom-functions/list.php

              There was one there which looked like it would work. I didn't try it, because I have a basic knowledge of XSL, so I'd use that. 

               

              One big advantage of Import XML vs. parsing is that it can handle any number of rows of Clients in the result, automatically (each in its own record).

               

              If you want to go the XML route, this is an XSL stylesheet which would transform your xml to what FileMaker needs to Import XML.

               

               

              <?xml version="1.0" encoding="utf-8"?>
              <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
              <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
              <xsl:template match="/">
              <ERRORCODE>0</ERRORCODE>
              <PRODUCT BUILD="" NAME="" VERSION=""/>
              <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a"/>
              <METADATA>
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClientID" TYPE="TEXT"/>
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClientName" TYPE="TEXT"/>
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClientShortName" TYPE="TEXT"/>
              </METADATA>
              <RESULTSET FOUND="{count(/GetClientListResult/Clients/Client)}">
              <xsl:for-each select="/GetClientListResult/Clients/Client">
              <ROW>
              <xsl:attribute name="MODID">0</xsl:attribute>
              <xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>
              <COL>
              <DATA>
              <xsl:value-of select="@ClientID"/>
              </DATA>
              </COL>
              <COL>
              <DATA>
              <xsl:value-of select="@ClientName"/>
              </DATA>
              </COL>
              <COL>
              <DATA>
              <xsl:value-of select="@ClientShortName"/>
              </DATA>
              </COL>
              </ROW>
              </xsl:for-each>
              </RESULTSET>
              </FMPXMLRESULT>
              </xsl:template>
              </xsl:stylesheet>

              • 4. Re: Converting XML in FileMaker to Table Format
                BENTO-BOX

                What are the script procedures used to get to another part of the same field (not a different record)?

                 

                For example, this XML is all found within the same field.

                 

                  <Client ClientID="2iQs24qt0fcI" ClientName="3UK" ClientShortName="3UK" ClientNumber="" />
                  <Client ClientID="2dR2QecHL9og" ClientName="Acer" ClientShortName="ACER" ClientNumber="" />
                  <Client ClientID="2COiSYfuAf00" ClientName="Aigo" ClientShortName="AIGO" ClientNumber="" />
                Doing a SET SELECTION for the "ClientID" will get to the first "ClientID".  (The one with client 3UK.)
                I'm interested in continuously looping through each of these Client items with the SET SELECTION method, until FileMaker cannot find any more words "ClientID."
                Can someone let me know how this LOOP script is written, especially the EXIT LOOP IF condition?  Thanks.
                • 5. Re: Converting XML in FileMaker to Table Format
                  comment_1

                  I don't see how Set Selection[] can be useful here. I would do something like:

                   

                  Loop

                  Set Variable [ $occurrence ; $occurrence + 1 ]

                  Exit Loop If [ not Position ( text ; "Client ClientID" ; 1 ; $occurrence ) ]

                  ...

                   

                  then extract the part/s you need using the Middle() and Position() functions.

                   

                  • 6. Re: Converting XML in FileMaker to Table Format
                    BENTO-BOX

                    I'm using the SET SELECTION method, combined with the POSITION method, to get hold of each of the bolded items.  That is, "2iQs24qt0fcI", "3UK", and "3UK" for the first record.  And the corresponding values (in the second line) for the second record.

                     

                    In my script, I have it as

                     

                    SET SELECTION (WebServiceClient::xml_result, Start Position: Position (Web Service Client::xml_result, "ClientID", 1, 1, +10; End Position: Position (WebServiceClient::xml_result, "ClientName"; 1; 1 ) - 3 ]

                     

                    This SET SELECTION selects the text "2iQs24qt0fcI", which I then copy and paste to another record.  The same process goes for the other values in the record.

                     

                    So, I have all the information I need for the first client.  However, there are many more clients after that (which is why I need that LOOP.)

                     

                    • Could you let me know what function the second line in your LOOP statement means?

                    • 7. Re: Converting XML in FileMaker to Table Format
                      comment_1

                      Copy and paste is not good practice (it requires the field to be on the layout and it destroys the user's clipboard). It's better to use Set Variable[] and Set Field[] instead.

                       

                      Other than that, you are on the right track, except you need to raise the occurrence parameter of the Position() function by 1 on each iteration of the loop.