Automating an XML Import

[grantwood]

Hello,

Is it possible to script an import from an XML data source? The Import Records script step doesn't seem to have an option to import XML files. Am I missing something? I'm currently using FMP Advanced 8.0v3. Do I need to upgrade to 8.5 Advanced?

Regards, Sean

[grantwood]

Well, one problem solved: I overlooked the "Specify data source" popup menu in the Import Records step, but I have another problem.

If I hard-code the filename when choosing the XML data source, my import script works fine. However, I won't know the filenames in advance, so I would prefer to choose the XML data source using a calculation (in my case, it's just a local variable). But when I try and specify the import order, the left side (Source Fields) is blank, and I think that's my problem, because I keep getting "XML parsing" errors.

Any ideas?

[grantwood]

FWIW, the exact error message is:

XML parsing error:

An exception occurred! Type:XMLPlatformException, Message: Could not open file:

Line Number: 0

Column Number: 0

looks something like "filemac:/Macintosh HD/Users/username/Desktop/some_folder/some_file.xml"

[Fenton]

It sounds like it's stopping dead on the first character. But a couple of question. Is the XML file in FileMaker format, FMPXMLRESULT? Which is what happens when you export XML in that format from FileMaker. If not, you'll need an XSL stylesheet file to transform your xml to that format. We'd need to see an example of the xml you have, as they're all different; each XSL stylesheet for import is usually custom-built.

Another possibility is that the encoding of the XML is something like UTF-16 instead of the usual UTF-8. That can give you that 1st character error, as there's an extra character there. It's hard to tell or change unless you open the xml with something BBEdit or TextWrangler, or a dedicated XML editor, as either encoding is rendered transparently by apps like TextEdit, so you don't see the difference. I don't completely understand the encoding problems, but I get them, and fix them by encoding as either UTF-8 or Mac Roman (or Windows encodings if on that platform). This can be done either manually on the file, or just by specifying UTF-8 in the XSL; which you usually need anyway.

The trick to being able to set up the Import order, when you're actually using a variable for the file path, is to add an existing xml file (with the correct stuff in it) to the paths, using the "Add file" button. It'll go on a 2nd line, below your variable. It's fine to have a 2nd path; but make it the 2nd choice, the 2nd line.

[grantwood]

Hi Fenton,

Thanks for the really helpful reply. I should have mentioned that I had already created an XSL stylesheet. The problem was that I could manually import an XML file, but not automatically.

I had this nagging thought that I was overlooking something simple, and it turns out I was right. I have a variable called $importPath, which oddly enough stores the path of each XML file to be imported. In the Specify XML and XSL Options dialog there are three radio buttons for the XML section. I initially selected Calculation, with a value of $importPath. All I had to do was select File instead and specify $importPath as one of the file paths. Doh!

Thanks again for your reply.