XML imports problems. Please Help

[Etnadan]

Hi, I am not new to FileMaker since I used it from the very first version, but I don’t know how to handle the new complex languages there are such as XML and so on.

I need to import in FileMaker a large video database the database in English or in Latin Chars, I need to import it in FileMaker where I can add additional information in 2Byte chars (Traditional Chinese). With Unicode Filemaker finally works well ao no problems here.

The problems are the Import from the XML file. Although I verify the original XML file (it's a UTF-8 file) with OXYGEN I learn the file has no errors and it is well formatted. FileMaker just doesn’t want to import it. It finds errors which I am unable to understand. If I open the same file in SAFARI it appears unformatted but I can read properly.

This is a sample of my file with only one record.

If anyone would be so truly kind to tell me what to do I would appreceite it tremendously.

This database will eventually go inmySQL on my server and the goal of this all is to open international movies to my Chinese students.

Thanks

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

1

Asin

0790731959

Comments

My Comment

Country

USA

Date

2005-07-08T06:45:08Z

Director

Clint Eastwood

Discs

1

Display Gone

0

Genre

Action & Adventure

IMDB

0118548

Image

1496.jpg

Language

English

Media

DVD

Names

Clint Eastwood

Gene Hackman

Ed Harris

Laura Linney

Scott Glenn

Dennis Haysbert

Judy Davis

E.G. Marshall

Melora Hardin

Kenneth Welsh

Penny Johnson

Richard Jenkins

Mark Margolis

Elaine Kagan

Alison Eastwood

PictureFormat

Pan & Scan

Rated

R

Release

2005-02-08T12:00:00Z

Roles

Luther Whitney

President Allen Richmond

Seth Frank

Kate Whitney

Bill Burton

Tim Collin

Gloria Russell

Walter Sullivan

Christy Sullivan

Sandy Lord (as Ken Welsh)

Laura Simon

Michael McCarty

Red Brandsford

Valerie

Art Student

RunningTime

121

ScreenRatio

2.35:1

Sound

Dolby

Starring

Clint Eastwood, Gene Hackman, Ed Harris, Laura Linney, Scott Glenn, Dennis Haysbert, Judy Davis, E.G. Marshall, Melora Hardin, Kenneth Welsh, Penny Johnson, Richard Jenkins, Mark Margolis, Elaine Kagan, Alison Eastwood

Studio

Castle Rock

Subtitles

English, French, Spanish

Summary

the Summary

Theatrical

1997-02-14T12:00:00Z

Title

Absolute Power

UPC

012569250826

VideoFormat

PAL

Writer

David Baldacci, William Goldman

Version

New

Thanks you

[Martin Brändle]

For Import/Export, FM only understands XML files in a FM-specific grammar:

- either FMPXMLRESULT

- or FMPDSORESULT

Your XML file has to be transformed to one of these grammars. For this you need XSLT (eXtensible Stylesheet Language Transformations), which is itself written in XML, and during the import you have to specify a XSLT stylesheet (or file) that defines the transformation rules.

It depends both on your data structure of your database and the data structure of your XML files how your XSLT code has to be written.

The best is, you buy the book "FileMaker Pro 6 : Developer's Guide to XML/XSL" by Beverly Voth, Wordware Publishing, 2003, ISBN 1-55622-043-X. It explains step-by-step how to write XSLT.

[Fenton]

Both FileMaker and Apple Plists xml formats are somewhat abstracted. But both are well thought-out and hierarchical. You just have to translate one to the other. A simplistic attempt at explanation: Plist stores what we think of as the "field name" in the element values. It then stores the data in elements named according to the type of data of their value: , , (which contains more levels).

This is all fine and dandy, but it is a little abstract, and you cannot know the name of each specific element containing the data ahead of time (, ), when you're using a generic "for-each" (which is like a Loop). Fortunately the element with data is always the next element after its ; which is written in xsl as: "following-sibling::*"

Notes on this file:

The original XML had a couple improperly encoded "&" ampersands, which should have been:

&

So I changed those first.

The xsl is fairly straightford, as these things go. The only glitch is that most of the data is deeper than the "Version" key data. So extra "for-each" blocks were needed to get it. In the case of the data, it needed to go "up" a level to get it, as everything else is already inside the row; so it used: ../

There were some "extra spaces" problems with the Names and the Roles fields. So I just used an auto-enter calculation inside FileMaker to strip them down. (There is a "normalize-spaces()" function in xsl, but that seems to take out the returns as well, making it impossible to tell the names apart. Probably could be done properly with xsl, with a but I know FileMaker better.)

These Name and Role fields would be better imported separately into child table(s). The Names and the Roles seem to line up 1-to-1, so that would work peachy. But I didn't do that, yet...

AppleFilm_XML.zip

Edited September 6, 2005 by Guest
Ampersand xml got translated

[Etnadan]

Thanks you really ... so now

1- I must edit all my files and erase the "&" sign, and I can do this in BBedit?

2- I need to change the spaces in the NAMES-rOLES fields with an hard space? such as opt+spc ?

Since all the xml data I have is basically the same can I Import to Filemaker using your template as an XSLT? (after doing the changes you suggest)

Sorry but I am really a new comer in XML. I really am grateful for the time you spent helping me out

[Fenton]

Yes, you need to change & to

&

& is a special character in XML (or HTML for that matter). I surprised Apple would output it as is in its xml. They could have wrapped it like this to make it safe: &

About the names and roles. In the xml Apple produces they are a multi-line list, with 8 spaces at the beginning of each line.

In my FileMaker file you can see that I used an auto-enter calculation to remove the 8 spaces. But it's still a multi-line field. You might want to set each line into another table as a record, I don't know.

There is probably a way to do this clean-up with xsl. The normalize-text() function strips all the whitespace, including returns, so that's not so good. Perhaps the translate() function? But, since I know FileMaker better, I just did it there.

Edited September 9, 2005 by Guest

[Etnadan]

DearSir, unfortunately something goes wrong if the records I output are more than one. I think it's the way FILEMAKER imports XML files which creates the problem.

After cleaning the original xml with test Xslt (or importing directly in Filemaker using your StyleSheet)

Firstly: several recordsno longer go to the correct field inFMP andthey vanish during the conversion. In factin the origianl file they are all there.

Second: Filemaker creates several additional fields which increase according to the number of records I input.

Example: I exported all the data I havebeginning with letter "J" took off all the "&" as you suggested. I had 13 records and end up zith 468 fields in my filemaker file.

It seems there is a "loop" which name every additional record with the string name, prefixing it with an "_". SO I have files with 13 "_"and the stringname (example, title) which are randomly empty.

This wouldn't be such a great problem as I can erase them from the "fields" in FMP8, but as I am never certain if the fields are empty it's a bit of complication.

I created a script which navigates everywhere and returns me the field names when they are not empty, but this doesnt really help.

I think I should show you the originalxml file, the one I cleaned up and the database after the import.

I can see you are a very kindperson,but I don't wish to distrub you at all. So if you don'tmind let me know.

Kind regards

MyFiles.zip

[Fenton]

Oops. I was using a modified xsl which someone else had written, with a generic method to parse plist files. Unfortunately they didn't deal with the possibility of multiple "record" node sets. It was not too difficult to specify only using the 1st node set for the Metadata fields.

However, I am running into one of those inexplicable problems with FileMaker's xml parser. My xsl works fine in TestXSLT, with any of 4 different xsl parsers. And, the resulting xml can be imported directly into FileMaker fine. But FileMaker kicks up a very consistent error when I try to import via the xsl file; something about the element and its place in the xml data. I can't tell whether it's a bug/limitation in FileMaker's xml. But I haven't been able to get around it (and I've tried); it's hard when only FileMaker kicks up an error.

So please upload a zipped version of your xml export file, or post it here. Like the original xml you posted, but with 2 records. I have to confirm that my method of extending the xml to 2 records was correct; which was I just copied everything between the elements, including the upper level element. It is valid xml, but I don't know how yours is; like whether the version applies to the record or to the file as a whole.

Further thoughts: First, do you need that "version" field? Its data in your original file is "new".

I believe that FileMaker, unlike the other xsl engines, may be validating the plist? Because the error message I'm getting is not your regular generic xml/xsl error. Since I'm on a Mac, plists are pretty integral (like the FileMaker Preferences file -). And I've somehow broken the plist rules (though I can't see how exactly). So it's essential for me to see what what one of your multi-record xml file looks like.

[Etnadan]

Hi Fenton, I downlded the files in my previoius post. I know the problem must be in filemaker because if every record has the same "format" it seems to work fine.

Non working import Example

the xml record 1 has datafor all fields

the xml record 2 only has data in fields 1,3 7

the xml record 3 has data only in fields 1, 2, 3, 9

Filemaker will import field 1 as such for record 1 and 2

field 3 (of record 2) as his record 2, field 7 (of record 2) as his record 3.

At this point I am not sure anymore because sometimes the next field 1 will land in FMP field 1, at other times it will simply disappear.

Working Import Example

XML-field-1 contain data

XML-field-2 no data

XML-field-3 no data

XML-field-4 contain data

In this example it does not place them in the wrong order, even if there is no data in the fields. So in the Filemaker DB the emtpy fields willbe empty and the fileds with data will be filled. All in order.

After my tests: filemaker will just skip empty fields in the XML file, importing the fields with data one after the other, no matter what is the "header' for the field.

Maybe the reason why Filemaker halts before importing XML filescan be that it looks if fields are empty or not, this is my idea. May be it only checks certain parts of the files to import.

As another test, I exported from my original database (DVDPEDIA from www.bruji.com) one record at the time, with datain very different fields: if the record is only one FileMaker imports fine with everythign in the right place.

Besides the madness of this workaround (I have about 2000 records) Filemaker doesn't treat XML files as a regular import. I need to open each of them and Filemaker will create one single database out of any XML file. If I did export each record separtely I will end up with 2000 databases with one record only.

I don't know enough about XML or XSLT but perhaps a workaround could be to add some data if the file has no data. But this would require conditional importing

bad example (not from a programmer)

if isempty (UPC)

set field UPC to 000

else next

I am just expressing a concept here I am not a programmer. I wouldknow how to do a script in fileMaker but XML or XSLT

Thanks again for your help

[Fenton]

The problems:

1) FileMaker apparently validates plist xml data itself. We know it can do this because its preference files on Mac are plist files. The transformation works outside FileMaker, using other xml/xsl applications, but got a plist structure error when I tried to import.

But I don't have the actual xml data files, I only have one I faked, so I can't tell what multiple record files are like exactly. You only posted a 1 record example. You need to post a multiple record xml data file; ie., what you want to import.

2) I did not know that the import fields could be missing. In that case the generic xsl method is not going to work. You're going to have to specify each piece of data explicitly, both in Metadata and in Results. It's an alternate (tedious) method. I could do this for a some of the fields, and you can extend. It's not that difficult, just dull as toast.

Please post some example xml files of exactly what you have to import. Don't paste them in the post (too long), zip them together and attach as a file.

[Etnadan]

Here they are:

the original XML file including 13 titles all starting with letter "j"

the converted file using your 2 css

the FileMakerDatabase with so many errors

Thanks again

XML_Original_fileConversion_and_FMP8.fp7.zip

[Fenton]

I'm not getting the xml files. At least I don't see them. I only got the FileMaker file, which isn't much use to fix the import. Upload just the original xml files, in a folder, zipped. Tell me what the name of the folder is.

[Etnadan]

Here it is again: just one file from my dvdpedia database.

The file name is original_file_J.xml.

It contains 13 records

original_file_J.xml.zip

Edited September 11, 2005 by Guest

[Fenton]

OK. I see the problems. Basically these plists are not such a hot way to get data for FileMaker. First of all we need all the possible fields in the FileMaker database. Hopefully I've got that now. Next, we need a placeholder for data that is not in the source. Because fields, both the key and the data, are missing in the data in some records of the plist, we have to create a blank field for that field anyway, so as not to screw up the import order. It's OK if the data isn't there, but we need

to hold its place.

I've done this for some of the fields. And the rest are just more of the same. Look at the xsl file, you'll see. This is what you need to get the data (or a placeholder, and empty field) for each field in the Metadata (must be an entry there for any field in FileMaker)

So, you put the name of the field (case-sensitive), as the key to match. Then you use "following-sibling::*" to get the next value. You have to use this construction, because you don't know the name of the next element, which is where the data is. It could be or or or . But "following-sibling::*" doesn't care what the name is.

I don't know if my auto-enter calculation for the Names and Roles still works. Hopefully. I didn't get that far in building the xsl file (yet).

 

Apple_xml.zip

Edited September 12, 2005 by Guest

[Etnadan]

Thanks a lot for your help!!! Everything works properly now. As for the 'Names and roles' I will try to work something out before importing in filemaker ...

Thanks again

[Etnadan]

BTW, I changed the template importing the arrays, because Filemaker wouldn't load and change the fields at the same time. I created calculation fields which did the job and a script which will transfer the calculated results back to the original badly formatted array fields.

Regards

[Fenton]

I thought my file was able to load and change those 2 array fields (names, roles) while importing. Did you "Allow replacing of current field contents" in the auto-enter calculation for those fields.

I didn't finish coding the xsl for those fields in the 2nd "hard-coded" version, so I can't test easily. But it was working before in the earlier version that did 1 record, so I assumed it would work in the 2nd version.

(P.S. Another method would be run a grep Find/Replace on the original xml file, to just remove all the leading whitespace; which is superfluous to the xml anyway. Then run the xml through the xsl. There's different methods to do that, either straight shell script, or AppleScript using a Scripting Addition supporting grep.)

 

Edited September 18, 2005 by Guest