Simple(???) XML Import

[sbg2]

I have attached 3 files - XML file, XSLT file and a FileMaker file of how the file should import IMO. Unfortunately I get only 4 child records instead of the 6 I am expecting. Can anyone explain to me how to import the XML file so the records are the same as the example FMP file I have provided with two tables (4 Parent and 6 child records)?

P.S. I really don't know much about XML, someone provided the XSLT file for me but I'm about to give up and reinvent the wheel by parsing out the information manually : .

Thanks in advance.

MyXML.zip

[Fenton]

The problem is that there are only 4 parent records. The 1st record has multiple children. That is the structure of your FileMaker file also. To import relational data, whether it's from another FileMaker file or an XML file, you have to do a separate import for each table (if there's multiple children anyway).

So you need 2 imports, which means you need 2 xsl files. They are very similar. One just gets the TxnID. The other gets the other data.

I wrote the xsl a little differently, more specific to get what you want. I also redid the "counts." They are not really necessary though. You can just put "" for the counts. FileMaker doesn't seem to care.

Oh yeah, another little thing. You can "reach back" and get the TxnID for the child records, using the syntax "../", which means "up one level" (same as html, etc.). That's your relational key.

MyXML_fej.zip

Edited May 25, 2006 by Guest

[sbg2]

Thanks for the file and the explanation. In this case the one xslt file may be all I need?

a) I set the Parent TxnID field to Unique to filter out duplicates.

: In my actual XML file there are some TxnID's that do not have any child records. I will end up delteing these after I would import.

I used the child xslt file for the import into a new parent file and it imports the same number of records as using the parent xslt file and then deleting the parent records that did not have children. I also checked that the first and last TxnID matched. Seems to work but I could be missing something.

[Fenton]

I'm not sure what exactly you're doing (or want to do). I created a script in the FileMaker file which imports the parents into the parent, and the children into the child. You could also just import the children into the child table, and post-process the records with a loop to create the parent records in the parent table. But that seems like a lot of work just to avoid another simple xml/xsl import.

It is possible to use the regular FileMaker Import options when importing xml. You can use "Update matching". But that requires showing all records in FileMaker first, and gets slow in tables with lots of records.

[sbg2]

The XML file comes from Quickbooks, it is a list of the unique identifier for Invoices (TxnID). The child records (DataExtRet) are custom fields included on each invoice. The custom field Other unfortunately can not be written to outside of Quickbooks but can be read. Since the custom field Other holds our FileMaker Order_ID I need to move this information to a different custom field in QuickBooks that subsequently can be written to outside of QuickBooks.

The XML file I have from Quickbooks lists every single invoice and includes any and all child records (Custom Fields). Not all invoices have data entered in a custom field, about 50% of the 8000 invoices do not have data in any custom field. The XML file is much like the txt file I have attache to this post but lists many many more invoices.

Since my parent table only needs to contain parent records where there are custom fields...

1)Using the xslt for the child records only imports Parent TxnID's where there are child records.

2) Setting the TxnID field to unique prevents duplicate TxnID's in my parent table. In the text file I have attached there are 3 child records for the first parent record, since the TxnID in the parent table is set to unique only one instance of the TxnID is imported not 3 instances.

So I think I'm correct that in this case using the child XSLT file for both the Parent and Child import is acceptable.

Yes I probably could have limited the number of records returned from QuickBooks by excluding those that did not have child records. The request however takes a very long time to run and bogs down my system, the path of least resistance is to work with the XML file I already have. The XML requests takes about 45 minutes and it takes filemaker another 15 minutes of processing before the program is usable and stable. On the other hand the import of the XML records into two new tables is done in less than 30 seconds (including time to manually run the imports).

QB_Custom_Field_Example.txt

[sbg2]

Someone else on another board pointed out I could have just imported into one table and used a self-join relationship on TxnID.

Taking it one step further I realized I didn't even need a relationship. After completing the import doing a search where DataExtName = Other would return all the records I needed.

[Fenton]

You can alternatively do the "find" within the xsl itself. It has the ability to test for things:

         

            

            

				



               

                  0

                  

                  

                  

                  

                  

                  

               

            

         

Because I've used a "for-each" that is at the same level as the data, I have to "go up" 1, then down to get its sibling data, and "up 2" to get its parent data.

(P.S. the $pos position() variable is not necessary, but it's a cute way to set the record number attribute. Position is often useful in xsl. I didn't do that other counts at all. All this counting stuff is pretty much optional, as far as FileMaker is concerned.)

Edited May 25, 2006 by Guest