Help Importing from XML

[Breezer]

Hi all,

I have been trying to import from XML to Filemaker but I'm getting an error message. While importing I'm using "Import XML Data Source..." then File and specify the XML file location. The problem is I keep getting this error message "XML parsing error:"

I must be doing something wrong. Any help or pointers is greatly appreciated. Eventually I'd like to script this process since the XML file is generated from transaction in our website. I have attached the file "Serviceticket.xml"

Thank you.

ServiceTicket.zip

[IdealData]

I'm no expert with XML but I had a look in the help system to determine the correct format for XML files for FM.

The attached sample was lifted straight from the help system and it does work.

I think your XML file needs quite a bit of extra info

FMEXAMPLE.xml.zip

[Fenton]

To import xml you have to specify all the fields that are going to be imported, and their type. This is in a section at the top. Then you need to specify where a FileMaker record is going to start, a . Then the fields, specifying each if there is the possibility of some fields/data missing.

Which there were; in Transfer-Service the first one has fewer fields than the second. This is fine with xml/xsl. It is intelligent, much like a FileMaker file. But you have to specify all possible fields, just as you'd have to in FileMaker. I may not have got them all, but I think I did. It would be good to have a "schema" xsd file with all possible fields defined.

You have several different types of data in your xml. Transfer-Service, Profile-Service, New-Service, On-Line-Payment. Each of these would need its own xsl. You could import them all into one table, a flat table, as many of the same fields are present in each. But there are different types of addresses, and several other fields are different. So I did them as separate tables.

The files are encoded as UTF-8, Unicode (I think). So you may need to specify that to open them. An xml editor should have no problem, as the above is more or less the standard for xml. I'm on a Mac.

ServiceTicket.zip

[Breezer]

Thanks Fenton for putting it all in perspective. It is much clearer now. I will look at your sample and I'm sure I will learn from it and be able to share this valuable knowledge. Thanks again.

ps...when I export as XML it is UTF-8, Unicode in Windows too.

Edited December 30, 2005 by Guest

[Fenton]

One further tip. Though it took a little while to set up those xsl files, it was less than you would think; because they are quite similar, except for the field names. If you use a (real) text editor, grep, and some judicious Find & Replace, you can change one simple xsl file to match different fields.

The first thing you need is a list of the new field names, separated by carriage returns. This is best extracted from an xsd list of possible fields, but can be gotten from a complete xml file also. Choose only a section at a time for each xsl file; what matches a FileMaker table.

You use grep to remove everything except the field names (saved into a new file). Here's a list of some grep to use:

1. Remove leading spaces:

Find: ^ + (that's a space, or multiple spaces)

Replace: (that's nothing)

2. Remove all except elements

Find: (<[^ |^/]+>)(.*$)

Replace: 1

3. Remove markup

Find: (<|>)

Replace: (that's nothing)

This gives you a plain return-separated list of the fields. Now you can just add the beginnings and endings you need. (The quotes below are part of the xsl, that's why they appear unbalanced here.)

1. For the METADATA section:

Beginning:

Find: ^.

Replace:

(May need to change some TEXT to NUMBER or DATE afterwards)

2. Change the "for-each" path, to select what you want.

3. For the ROW section:

Beginning:

Find: ^.

Replace:

It beats heck out of typing. Many people add xml calcs for the count of various things, to put into the attribute values. Example (from elsewhere)

RECORDS="count(/*/location/inventoryItem)"

Perhaps I should; but FileMaker doesn't seem to care, so I don't bother.

Edited December 30, 2005 by Guest

[Breezer]

I tried out your example and it works like a charm but I guess I still have a lot to learn. Question, does this mean I need both XML and XSL files to do the import or if the XML file had the metadata defined would it still work without the XSL file? Is the XSL file acting as a template for future imports? Sorry for the lame questions but I'm just trying to understand it all. I do appreciate your time and effort.

[Fenton]

Yes, if the xml file had the Metadata defined it would import fine. You can see exactly what it would look like by just doing an XML export, using the XMLRESULT as the type; but don't specify an xsl stylesheet.

The xsl stylesheet is doing 2 things.

1. Defining the Metadata, which FileMaker needs, which is not generally present, unless the xml file was a FileMaker export.

2. Selecting the appropriate section of the xml, with a "for-each" (could alternatively use a "template").

Because an xml file is more than just a flat file (even though it is flat), because it also represents relational data, you need more than 1 xsl stylesheet to get the correct data for the correct FileMaker table.

[Breezer]

wow, you rock Fenton. Thank you very much. I feel more intelligent now. I hope one day I'll be able to return the favor. Have a prosperous New Year.

HAPPY 2006 to ALL @ the FILEMAKER COMMUNITY