1 2 Previous Next 19 Replies Latest reply on Mar 25, 2010 8:06 AM by BruceHolmes

    Importing XML after HTTP request

    BruceHolmes

      Title

      Importing XML after HTTP request

      Post

      I'm using FileMaker Pro 9 Advanced on a Mac OS X, 10.5.8.

      I want to send an http request to the post office servers and get in response a zip+4.  They have well established systems for doing this.

      On the "importing XML data" page in the help system it says:

      "The XML can be the result of an HTTP request sent to a web server. For example, you could set up an Orders database to send tracking numbers to a package delivery company in the form of an HTTP request, and their web server could return the latest package tracking information in XML format, ready to be imported into the Orders database."

      So my heart started beating fast.  All was possible.

      Hours later my head is spinning and I haven't a clue.  I can find nothing about how to do this.  It's possible I'm just too new to FMP to understand that the solution is right before me, but as far as I can tell I haven't found anything that seems even closely related.

      The post office uses a test that looks like this:

      Sending:

      http://SERVERNAME/ShippingAPITest.dll?API=Verify&XML=<AddressValidateRequest%20USERID="xxxxxxx"><Address ID="0"><Address1></Address1>

      <Address2>6406 Ivy Lane</Address2><City>Greenbelt</City><State>MD</State>

      <Zip5></Zip5><Zip4></Zip4></Address></AddressValidateRequest>

       

      Expect back:

      <?xml version="1.0"?>

      <AddressValidateResponse>

      <Address ID="0">

      <Address2>6406 IVY LN</Address2>

      <City>GREENBELT</City>

      <State>MD</State>

      <Zip5>20770</Zip5>

      <Zip4>1441</Zip4>

      </Address>

      </AddressValidateResponse>

       

      So the question is, how do I write a script, and what script steps do I use in order to talk to the post office?  My first guess is to use "Open Url" to send the http request.  But then what?  And how does FMPXMLRESULT fit in to all this?  I doesn't seem to be a script step.

      Bruce

        • 1. Re: Importing XML after HTTP request
          comment_1

           


          BruceHolmes wrote:

          what script steps do I use


          You need to use the Import Records [] step. For data source, specify XML Data… and enter your HTTP request (you'll probably want to calculate it in order to include the current record's data).

           

           


          BruceHolmes wrote:

          how does FMPXMLRESULT fit in to all this?


           

          Filemaker will import XML documents in FMPXMLRESULT grammar only. To transform the source XML into FMPXMLRESULT grammar, you need to use a custom XSLT stylesheet during the import.

           

           

           



          • 2. Re: Importing XML after HTTP request
            BruceHolmes

            It's already been 24 hours and I haven't gotten back to you, so I thought I ought.  First, thanks so much for the response.  You got me on the right path and I feel like if I just keep plugging I can figure it out.  I've found a very good source for learning about XML and XSLT and XPath, and I'm plowing through it all.  I'm assuming when I've read the material, I'll have some sense of what to do with the XML dialog you pointed me at.

            So, I'm full of hope.  I'll get back to you when victory is at hand.

            Bruce

            • 3. Re: Importing XML after HTTP request
              comment_1

              Try something like:

               

               

              <?xml version="1.0" encoding="UTF-8"?>
              <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              
              <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
              
              <xsl:template match="/">
              
              <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
              <ERRORCODE>0</ERRORCODE>
              <PRODUCT BUILD="" NAME="" VERSION=""/>
              <DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>
              
              <METADATA>
              <FIELD NAME="AddressID" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
              <FIELD NAME="Zip5" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
              <FIELD NAME="Zip4" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
              </METADATA>
              
              <RESULTSET FOUND="">
              
              <xsl:for-each select="AddressValidateResponse/Address">
              <ROW MODID="" RECORDID="">
              <COL><DATA><xsl:value-of select="@ID"/></DATA></COL>
              <COL><DATA><xsl:value-of select="Zip5"/></DATA></COL>
              <COL><DATA><xsl:value-of select="Zip4"/></DATA></COL>
              </ROW>
              </xsl:for-each>
              
              </RESULTSET>
              </FMPXMLRESULT>
              
              </xsl:template>
              </xsl:stylesheet>

               

               

              • 4. Re: Importing XML after HTTP request
                BruceHolmes

                Holy **bleep**.  My guess is you've saved me a day of pulling my hair out.  I have a girlfriend I need to spend time with so I can't study what you've done until tomorrow.  But I will.  Thank you.

                Bruce

                • 5. Re: Importing XML after HTTP request
                  BruceHolmes

                  I assumed I would be able to do better than I have, but I’m back to spinning my wheels.  I fear my background is just not extensive enough in this sort of programming.  I’ve learned an awful lot in the last couple of days.  Buy none of it seems to bring me to a solution.  Here’s what I think I’ve accomplished. I’ve got an XSL file created with the code you suggested and I sort of understand what you’ve done.  I’ve got the “Import Records” script step set up to calculate an http request and on the bottom half of the screen points to the XSL file.  It looks like your XSL creates a result, a table perhaps, and the first column is the address ID number (since I’ll only be sending across one record at a time this will always be 0 and I could probably drop this), the second column is the zip5 and the third is the zip4.  My major issue is, I don’t know how to use the result.  Is there a name for the Import Record result such that I could assign the various result fields and then figure out what to do with what the post office sent me (i.e. combine the zip5 and zip4 and move them into my zip code field).  For one moment of hopeful enlightenment I realized that FMPXMLRESULT must be the result and that if I just…  But I can’t find anything to support that theory.  After you've called the Import Records script step, how do you use the data you've supposedly retrieved?  For what it's worth, the w3schools.com site has a screen you can go to as part of their xslt tutorial where you can put in an xml result and the xslt template one wants to us on it and see the result at the bottom of the screen.  All I get when I try doing that with the theoretical post office response and the XSL code you sent is a 0.  Don't know what that means, but my theory that it results in a table looks shaky.

                  I’ve also discovered in my additional reading that an error response is a possibility, and that it would look like:

                  <Error>

                  <Number></Number>

                  <Source></Source>

                  <Description></Description>

                  <HelpFile></HelpFile>

                  <HelpContext></HelpContext>

                  </Error>

                  Where:

                  Number = the error number generated by the Web Tools server.

                  Source = the component and interface that generated the error on the Web Tools server.

                  Description = the error description.  (The two help fields are reserved for future use and I assume are irrelevant.)

                  So before working with the fields, I probably ought to check for an error.  I’m assuming I could add the elements <Error/Number> and <Error/Description> to my fields of interest list and my results if it didn’t have a error number, I’d know I didn’t have an error code to worry about.  If I did, I could show the description in a dialog.

                  I can't imagine how big a heart it takes to help ignorant newbie fools with this sort of problem.  Thanks in advance if there's any further guidance you can offer.

                  Bruce

                  • 6. Re: Importing XML after HTTP request
                    comment_1

                    One step at a time...

                     

                    I suggest you save your xml result in a text file as Source.xml and the stylesheet above as Sheet.xsl.

                     

                    Next, tell Fllemaker to open file Source.xml and - when asked -  to use XSL stylesheet from file Sheet.xsl. Filemaker will create a new file as Source Converted.fp7. Delete all records from this file, then do Import Records > XML Data Source… > and select again Source.xml as the data source file and Sheet.xsl as the stylesheet file. Now you should get a dialog for mapping the imported fields into your fields.

                    • 7. Re: Importing XML after HTTP request
                      BruceHolmes

                      Everything worked as you suggested.  It's killing me that I can't just put the xml values in variables I've created.  But I suppose I could do a push pop sort of process by saving field contents in variables, apply the new info into fields, assign field contents to variables and restore the fields used from variables.  But my goodness, is that really the only way to do it?  It seems so inelegant.

                      Is there any way to also have the stylesheet check for error codes?  In your stylesheet code I was thinking I could nest the row stuff outside the record and have two records in that row.  One for the normal data you get back and then putting the error codes in as the last couple of cells in the row (if there is an error code instead of getting a data response).  Do you want to warn me off.  Is there a better way?  It's certainly something I could play with and see what I get.

                      • 8. Re: Importing XML after HTTP request
                        BruceHolmes

                        I tried looking for both possibilities, good data and an error and it almost worked.

                        Here's what I used:

                         

                         

                        <METADATA>
                        <FIELD NAME="Address2" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
                        <FIELD NAME="Zip5" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
                        <FIELD NAME="Zip4" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
                        <FIELD NAME="ErrorNumber" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
                        <FIELD NAME="ErrorDescription" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
                        </METADATA>
                        <RESULTSET FOUND="">
                        <ROW MODID="" RECORDID="">
                        <xsl:for-each select="ZipCodeLookupResponse/Address">
                        <COL><DATA><xsl:value-of select="Address2"/></DATA></COL>
                        <COL><DATA><xsl:value-of select="Zip5"/></DATA></COL>
                        <COL><DATA><xsl:value-of select="Zip4"/></DATA></COL>
                        </xsl:for-each>
                        <xsl:for-each select="Error">
                        <COL><DATA><xsl:value-of select="Number"/></DATA></COL>
                        <COL><DATA><xsl:value-of select="Description"/></DATA></COL>
                        </xsl:for-each>
                        </ROW>
                        </RESULTSET>

                         

                        End of code.  When I fed it a positive response where the zip code lookup had worked, everything was great.  I had data in the first three columns.  When I tried feeding it an Error code and description it unfortunately put them in the first two columns instead of columns 4 and 5 like I wanted.  Is there any way to specify which column to put a result in?  If not, any suggestions?

                        Is there a great book that teaches Filemaker Programming?  I mean that solution you came up with?  I can't imagine that's documented anywhere.  How do you keep from getting lost?

                        • 9. Re: Importing XML after HTTP request
                          FentonJones

                          You cannot add another <xsl:for-each> for the Errors. FileMaker cannot create multiple related records via a single XML Import step (it can if you do a separate Import).

                           

                          I assume in this case that you only get 1 Error, so why not put its fields in the main table. You can then Delete those records later, if you need to. But you cannot push them into another child table at that same time as importing the Address fields.

                           

                          As far as which field the data goes into, that's just lining them up during the Import XML step (or, better, in a script using it). But the extra for-each could be messing with lining them up.

                           

                          There was a book by Beverly Voth, years ago, about XML & FileMaker 6. FileMaker's XML implementation has not really changed since then. There was another by Bob Bowers and Steve Lane, which also covered some of this, slanted toward web development.* I don't know that there are any newer books.

                           

                          *Interesting, Beverly mostly uses <xsl:for-each> to pick the data, whereas Bob & Steve use mostly <xsl:template> exclusively. Both are good methods. It sort of depends on the data you're getting. I (and most non-programer types) tend to use <xsl:for-each>, while many with programing backgrounds favor <xsl:template> (which is a little more "abstract" and "modular", to me). So both books are valuable.

                           

                          It also helps greatly to get a basic beginner's level book on just XSL (XSLT).

                          • 10. Re: Importing XML after HTTP request
                            BruceHolmes

                            I feel like I'm on the one yard line and slipping in the mud.  Everything works, I've checked in all sorts of tangential ways, except one thing.  It has to do with the screen where you set up the Import Record.  If you have a XML data file and a XSL template and have followed your instructions, you are then able to map the fields, i.e. what gets imported into what.  If you then go back and switch to the http request for data instead of the XML file, the field mapping goes away.  I've tried half a dozen times in hope that some magical sequence would keep the field mapping available, and it always goes away.  When I run the process the report comes back: "There are less records in the data source than in the target table.  Not all records were updated."

                            When I'm on the Import Field Mapping screen I've got:

                            Import Action:  Update existing records in found set

                            and the "Don't import first record (contains field names)" is checked.  This worries me because it could be the source of the problem, but it's grayed out and I can't uncheck it.  I haven't found any setting where it will ungray itself.

                            Any suggestions?

                            Bruce

                            • 11. Re: Importing XML after HTTP request
                              comment_1

                              Are you doing this in a script? If you save the script after mapping the fields to a FILE, then change the script step to use a URL, it should work fine (do the step with dialog to check this).

                               

                              The "Don't import first record..." is a known cosmetic bug - you shouldn't worry about it.

                              • 12. Re: Importing XML after HTTP request
                                BruceHolmes

                                So I changed it back to referencing an xml file and went back to the field allocation screen.  Everything looked good.  I saved the script.  Then I changed it back to the url instead of the xml file.  Saved once again.  Ran the script.  As you suggested I had the Perform without dialog unchecked.  When I got to the Import Field Mapping screen I could see the Source FIelds listed, so I breathed a big sigh of relief.  It was going to work.  Clicked Import.  Got the screen of import options.  Continue.  And then I got the same error message about Not all records were updated.  And when I took a look at the data as the script step finished, the fields were empty.

                                I've gone back over everything.  If I do the process you suggested of opening the XML file and suggesting the related XSL file it creates the Converted file just fine and the data has been popped into the correct field columns.  I then delete that record line.  Then I ran it as a script step not using the URL and instead using the XML and XLS files.  Still doesn't transfer anything across into the customer record fields.  Same error message.  Everything seems to work except the part of moving the result into the selected fields.  I keep going back to that Import Fields screen and staring at it, trying to figure out where I've messed up.  But I swear it looks right.

                                How do you debug something like this where it's a black box and you don't get to see what's happening?  Feels like we're flying blind.  Years ago I programmed in assembler and there were no mysteries, ever.  You could see everything happening and debugging was a breeze.  I miss the good old days.  I also miss feeling competent.

                                • 13. Re: Importing XML after HTTP request
                                  comment_1

                                  I suspect your problem is not related to XML, but to the "Update existing records in found set" option that you have selected. I suggest you read the help on that.

                                  • 14. Re: Importing XML after HTTP request
                                    Bonnerbl

                                    Bruce - I am trying the same thing as you only I want to import just one record not several. I have a button on a data entry screen. The user presses that button and it goes to the USPS site and retrieves the zip code and sticks it in the appropriate field on the screen (at least that is my intent). Have you come across a pointer/reference on how to import just one record?

                                    1 2 Previous Next