Import XML via HTTP From Filemaker to FileMaker

[The Software Man]

I need to pull data from my FMServer to a FMSolution. Since FileMaker cripples importing from any FileMaker sources I figured I could use XML via HTTP.

I have a local file and table and a clone of the same file and table on the server. All security is set.

I can use safari to browse to the url of...

"http://192.168.1.1/fmi/xml/FMPXMLRESULT.xml?-db=timeattend.fp7&-lay=xml&-findall"

The result all looks XMLish to me.

However.....

I made a script with the step of:

Import Records [http://192.168.1.1/fmi/xml/FMPXMLRESULT.xml?-db=timeattend.fp7&-lay=xml&-findall]

But when I go to specify import order, I get

XML parsing error:

expected end of tag 'meta'

Line Number: 6

Column Number: 3

Shouldn't this work without XSL?

Edited August 14, 2010 by Guest

[comment]

Can you post the "XMLish" XML?

[The Software Man]

<?xml version="1.0" encoding="UTF-8" standalone="no"?>008/11/2010382909:00:0011:00:0012:00:0014:00:0017:00:0008/11/201034607:00:0012:00:0013:00:0008/02/201034809:02:0012:00:0013:02:0016:58:00

Edited August 14, 2010 by Guest

[The Software Man]

XML Sample was cut and pasted from Safari "View Source"

Here it is pretty like...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

0

08/11/2010

3

829

09:00:00

11:00:00

12:00:00

14:00:00

17:00:00

08/11/2010

3

46

07:00:00

12:00:00

13:00:00

08/02/2010

3

48

09:02:00

12:00:00

13:02:00

16:58:00

Edited August 14, 2010 by Guest

[comment]

The forum software doesn't like XML - it's best to zip a file and attach it.

Anyway, it seems like a valid FMPXMLRESULT document and you shouldn't need a stylesheet to import it (you can test this by saving it as a file and trying to import that).

[The Software Man]

Saving the data from safari and importing it into FileMaker worked like a charm.

I tried recreating the script (just in case I corrupted it) still gives the meta error

Argh... Frustrating!

[The Software Man]

Can you try this with your own databases?

[comment]

I have a few suspects, but not enough info:

1. Is it always EXACTLY the same error message?

2. Try putting the saved file somewhere on the web and import it through HTTP request (i.e. eliminate FMS from the process);

3. Do you have the FMPXMLRESULT.dtd document where the DOCTYPE declaration says it should be?

Can you try this with your own databases?

Sorry, no access to a server ATM.

[The Software Man]

I got it figured out. It was two fold, the username and password should be in the url AND instant web publishing must be on for that user....

So, the Script step becomes....

Import Records [http://username:[email protected]/fmi/xml/FMPXMLRESULT.xml?-db=mydatabase.fp7&-lay=mylayout&-findall]

[comment]

Excellent. And you are saying this works from a runtime? No doubt this could be useful to others.

[Cabinetman]

Useful to me for sure........ It solves a question I've had in developing my runtime version even though most importing is from text files.

[The Software Man]

To be specific, it's the Instant Web Publishing - Extended Security Privilege for the User that needs to be on Full Access. Not necessarily the Instant Web Publishing Configuration in the Server Admin Console.

––––––––––

Works from the runtime nicely. I too have been hoping to get this solved for ages. Glad to have it done so gracefully. As it should be!

Now to come up with an equally graceful way to push data to the server, hopefully without a plug-in. May need to use the WebViewer object.

Let us know if anyone comes up with something sweet.

Edited August 15, 2010 by Guest

[fseipel]

If the server is configured for XML publishing, then the reverse operation, pushing data back to it from the runtime, should be no problem using a WebViewer.

e.g. to find the first record in Filemaker's sample database you might use a URL such as this to obtain its record ID:

http://127.0.0.1/fmi/xml/fmresultset.xml?-db=FMServer_Sample&-lay=English_Form_View&Title=Alaska%2024/7&-find

where you would replace 127.0.0.1 with host name, and would replace DB name, layout and field name appropriately. URL encode spaces. This searches db "FMServer_Sample" layout "English_Form_View" for book with "Title=Alaska 24/7". Presumably you'd search for a UUID field in a real DB.

To edit data once record ID is known from above query, or from runtime DB which might already know its ID, you might use:

http://192.168.0.21/fmi/xml/fmresultset.xml?-db=FMServer_Sample&-lay=English_Form_View&-recid=1&Author=New_Author_Name&-edit

to change the author field to New_Author_Name in record id #1.

I tested this and it worked fine. You may want to always do the find first, since ID's might change if records are deleted.

-new would be used for new record creation. If a large number of records require updating, then it might be better to ftp the differences via shell script, and have server import them, or import server file into a second table in DB, compare the two, and upload differences to server to sync.

You should be able to handle authentication with username:password@hostname:port in the server URL.

I'm not sure this qualifies as graceful, but it is relatively straightforward. The script would check for indicating success in both the find, edit and/or new operations, of course, before marking records as synced.

If it must be achieved with Instant Web publishing then I think you need to do form posts? ScriptMaster could take care of that.