Export with XSLT

[Steve Taylor]

Anyone know how this works when exporting related fields? I have no problem exporting using a stylesheet when I'm exporting just from the one table, but if I include related fields, and hence have more than 1 related records per main record, I only get one related record in the resulting XML, not the whole lot.

Here's my XSLT stylesheet, which is very happy with "local" fields only, but I need to get it to drill down and process all the related fields instead of just the first one:

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

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="fmp">

Baffled!

[TheTominator]

I have no problem exporting using a stylesheet when I'm exporting just from the one table, but if I include related fields, and hence have more than 1 related records per main record, I only get one related record in the resulting XML, not the whole lot.

Exporting to XML works the same as exporting to any other file format from FileMaker. Only the current table's records are exported. Any related fields will export the first occurrence in the related table.

If you want to export all of the fields in the related table, navigate to the related table an export from there. You can then see the fields from the Parent record and export them as well.

If you need to export fields from many related tables, do multiple exports into separate XML files and consolidate them into one XML file afterwards with the appropriate XSLT statements.

For example if you have already exported to "dataset1.xml" you can insert that file into the current output using

[Steve Taylor]

That's an interesting idea and I may have to do it with separate exports in the end, but I think you'll find that what you say about exports is wrong- if you export related records, you get the lot: if you have one record in table A with 10 related records in table B, when you export to, say, Excel, you'll get ten rows, the first of which will have the data from table A and B and the others will have it only from table B. You also get all the related records if you export XML using FMPXMLRESULT, but that format is no use to my target application, so I need to parse it into nested tags if at all possible.

But I'll try the separate-export thing, thanks.

[comment]

It's difficult to answer your question without seeing the data and understanding what you want as the final result. In general, I find it easier to export from the child table and include parent fields in the export order, instead of dealing with individual elements that have no common parent.

[TheTominator]

but I think you'll find that what you say about exports is wrong- if you export related records, you get the lot: if you have one record in table A with 10 related records in table B, when you export to, say, Excel, you'll get ten rows, the first of which will have the data from table A and B and the others will have it only from table B.

I did a simple test with a tab-delimited file (I have no use for Excel exports) and found I am indeed wrong about how related records are exported in general. Thank you for pointing that out.

[Steve Taylor]

OK. I'll get down to the nitty-gritty. I'm dealing with media (video) files, and want to export the editing data for many records all at once, in a nice nested XML structure. Each video has a number (called idHouse in the example) and associated edits which will have a start timecode, and possibly an end timecode. If I export the records for one idHouse, I get this, in tab-format:

90407 10:00:43:24

10:01:45:13

10:04:15:04 10:04:15:05

10:05:47:01

10:09:40:21

10:11:22:05 10:11:22:06

10:17:01:17 10:17:01:18

10:23:41:18

90407 is the idHouse, and there are 8 edits, three of which have both a start and end timecode. If I export the same stuff as FMPXMLRESULT, I get this:

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

0

90407

10:00:43:2410:01:45:1310:04:15:0410:05:47:0110:09:40:2110:11:22:0510:17:01:1710:23:41:18

10:04:15:0510:11:22:0610:17:01:18

I put some CRs into that to make it easier to read- as you can see the 1st

is the idHouse value (the parent record) and the other 2 s are the start and end timecode values. Using this XSLT stylesheet to parse the FMPXMLRESULT on the way out:

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

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="fmp">

I get this:

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

90407

10:00:43:24

In other words, the export with all the related records but the first ignored. But the related records are all there in the FMPXMLRESULT; it's just that my stylesheet isn't clever enough to get them out. It seems to me that I'm going to have to rewrite it with some kind of nested structure, so maybe that "" needs to be something different, but nothing I've tried so far works.

Incidentally the unltimate object of the excercise is much more complicated than this, but if I can just get this parsing-related-records thing sorted I should be OK.

If you have read this far, you have my thanks and admiration.

[comment]

As I said, it would be easier if you exported from the edits table. In any case, see if something like this can suit your needs:

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



xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

exclude-result-prefixes="fmp">

[Steve Taylor]

That's fantastic- it works fine. Dare I ask how I'd extend it to cope with more columns? (ie if I start exporting more than just two related fields)?

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



xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

exclude-result-prefixes="fmp">

By any chance?

BTW I do completely agree it would be better to export from the edits directly but I've been asked to do it this way...

[comment]

You don't need to start a new loop. The existing loop:

goes over all the edits and collects the data from the corresponding  elements in the other columns. So:

...

BTW I do completely agree it would be better to export from the edits directly but I've been asked to do it this way...

Sheesh. Does the person doing the asking even care? And do they know how to do it?

[Steve Taylor]

Well, this is great, and I'm immensely grateful. I have noticed that this seems to be impossible when you have three levels of relationship- ie Table A, related to Table B, itself related to Table C: if you do an export from A involving records from A, B and C, the values come out in the wrong order, so the stylesheet can't do its thing.

For the benefit of anyone else reading this would you agree that the generic stylesheet is this:

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



xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

exclude-result-prefixes="fmp">











































...



































...



















 

Where X "parenttagnameN" is the tag you want to put on a field from the parent table and "childtagnameN" is the tag name you want to put on a field form the related records, and X is the number of the first column in the export that comes form the related table. No need to know the field names of the export, just their positions in the output.

[comment]

I have noticed that this seems to be impossible when you have three levels of relationship- ie Table A, related to Table B, itself related to Table C: if you do an export from A involving records from A, B and C, the values come out in the wrong order, so the stylesheet can't do its thing.

I am not sure I would agree with your assessment.

[Steve Taylor]

Do you mean about the export, or the possibility of parsing the data? The export, from table A including related records from table B and C ought to be like this:

A		A/B1	B1/C1

				B1/C2

				B1/C3

		A/B2	B2/C1

				B2/C2

		A/B2	B3/C1

				B3/C2

		

but is in fact like this:

A		A/B1	B1/C1

		A/B2	B1/C2

		A/B2	B1/C3

				B2/C1

				B2/C2

				B3/C1

				B3/C2

making me think that it won't work with a three-level export.

Edited May 25, 2010 by Guest

[comment]

I am afraid I don't follow your diagram. Any field being exported, no matter from which table, will end up as a in the exported record's . Fields from related tables that have multiple related records will have multiple elements. The order of the elements is the field export order. The rest is a matter for the stylesheet to figure out - that's what the T in XSLT stands for.

[Steve Taylor]

The diagram is just to show the fact that if you do a three-level export, the results aren't grouped according to the parent field, as they are if you do a two-level one. I'm talking about tab-delimited or Excel type exports here. I was getting a bit put off by that fact. But you're quite right; XSLT should be able to handle the FMPXMLRESULT output, however many levels of relationship you're exporting. I shall persist if I need to do one of those.

Thanks again.

[comment]

OK, I think I understand what you meant now. The FMPXMLRESULT grammar has a fixed number of hierarchy levels (ROW -< COL -< DATA). So if you export from 3 tables - parent, child and grandchild - you cannot tell which grandchild belongs to which child from the structure alone.

However, if you include the matchfields in the export, you can then reconstruct the relationships within the stylesheet and write out the result as a nested hierarchy.

[Steve Taylor]

Exactly. It's a bit of a shame to have to do it that way, but that's the way it is.

[comment]

Well, as I said in the beginning: it can be much easier if you export from the grandchild table. So you don't have to do it that way.

[smishler]

Is anyone else having trouble viewing the code samples? They are mostly blank for me yet this thread is something I am very interested in. I am on OSX 10.7.1 and have tried current versions of Safari and Chrome to no avail. Thanks.

[comment]

Try here:

http://attic.fmforums.com/forum/showtopic.php?tid/214940/

[beverly]

Is anyone else having trouble viewing the code samples? They are mostly blank for me yet this thread is something I am very interested in. I am on OSX 10.7.1 and have tried current versions of Safari and Chrome to no avail. Thanks.

I can't see the code samples, either.

But my suggestion for this post: export from the CHILD table. Yes, you'll get multiple PARENT duplications, but it's easy enough to grab one as you need it. And you can loop the GRANDCHILDREN "portal rows" easily enough. It's easier to write the XSLT when exported that way.