How does one import XML files with repeating fields into a FileMaker database?

Question

I'm using FileMaker Pro 9 and I want to take a database with repeating records (e.g. one field is "Lines" and it can have up to 9 strings) and import data into it from an XML file.

Right now, with the following XML file, I only get the first entry imported ("Room"):

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"&gt;
    <ERRORCODE>0</ERRORCODE>
    <PRODUCT NAME="" VERSION="" BUILD=""/>
    <DATABASE NAME="New Invoice" RECORDS="1" DATEFORMAT="M/d/yyyy" TIMEFORMAT="" LAYOUT="hh/mm/a"/>
    <METADATA>
        <FIELD NAME="Description Index" TYPE="TEXT" EMPTYOK="NO" MAXREPEAT="9"/>
    </METADATA>
    <RESULTSET FOUND="1">
        <ROW RECORDID="" MODID="">
            <COL>
                <DATA>Room</DATA>
                <DATA>Equipment</DATA>
                <DATA>Labor</DATA>
            </COL>
         </ROW>
    </RESULTSET>
</FMPXMLRESULT>

How can I get the rest of the entries ("Equipment" and "Labor") imported?

Answer 1

You can't. But you can get around it:

As far as I can tell, FileMaker seems to use the ASCII-29 character internally to split the data that is in repeating fields. You have a couple of options.

1. If you can work out the encoding issues, just have the one element and split Room, Equipment and Labor up with the ASCII-29 character in between.

2. Add your own separator and write a script/custom function that takes the imported data and pushed it into the required repeated field. (this script will look icky)

3. DON'T USE REPEATING FIELDS

Personally I stay as far away from Repeating Fields as possible.

If it is at all possible, use a new related table for the repeating data. You can use a portal or similar to display it if you so desire, or you can even pull it into a calculated field in the main table if it has to be there. Something like this:

INVOICE
-------
ID
Invoice Date

LINES
-------
ID
fk_InvoiceID
Item

They can then be related INVOICE::ID --< LINES::fk_InvoiceID and you can show all the items in a portal.

You can also have a calculated field that looks something like this in the Invoice table:

cLines = List(LINES::Item)

Which will give you something like:

Room
Equipment
Labor

In that one field.

Answer 2

Actual answer is that FileMaker doesn't support importing repeating fields in XML.