Multi-table Related record XML Export XSLT

[3 Square]

Hi,

I've been looking at exporting FM8 data to an XML file using the FMPDSORESULT. The file needs to be in this format, with element tags, to be read by other apps after exporting the data.

I've got the export working OK to a point in that any data from the main table is exported fine and has the element tags wrapped around the data but I'm scratching my head as to how to get related data exported too.

The database has one student table and related tables are linked by StudentID. The required XML tree is as follows:

(one element per pupil record in student table)

(unique)

(child of pupil, only ever 1 repetition)

(child of pupil and a separate related FM table called SENhistory)

(child of SENhistory only one repetition)

(child of SENhistory with possible multiple repetitions)

What I'd like help with is getting the right XSL into the stylesheet I'm using to grab the multiple related records for each pupil from the SENhistory table.

I've got this in the stylsheet at the moment but it's not getting any data, although it's not generating any error either.

(needs to grab any related field data from the SENhistory table using the StudentID field relationship)

Any help appreciated!

[Fenton]

It looks to me like you're trying to export from 3 table levels (BasicDetails is the same as level 1, 'cause there's only 1 of them). In which case I think you'd want to export from the middle table, SenHistory. It could reach up to get its parent, and it could reach down to get its (many) children.

The many children are the problem. Because they are not going to be wrapped in their field-name tags. They are going to be wrapped in only tags, within a single instance of their field-name tag. Further, all instances of a given related field are going to be right after each other. It's like looking at the value list of the related field. It is not like looking at a portal; which might be what you'd expect, but is not how FileMaker sees the data.

You are on the path it seems, but it is not going to be a lot of fun. I've done this a little, but I generally use fmpxmlresult. I imagine that fmpdsoresult is actually easier, as it supplies some of the named tags for you (if your fields are named exactly the same, which is unlikely, and one of the reasons I usually use fmpxmlresult, which is field-name independent). But many will need to be added, and it has to be parsed out and rearranged.

Could you post an actual sample file? Just the FileMaker file, with only the tables and fields you need, and a little sample data. That way we could do our own raw xml exports, to see which works best (obviously a custom xsl will have to be created, but I'd just as soon start from scratch). Also post a carefully done sample of what the final xml result must look like. It should have enough related records and sample data so we can see what it is. You can zip this altogether, rather than post it in the message. I cannot promise I'll have time right away however. Maybe someone will -]

[3 Square]

Hi Fenton,

Thanks for the reply. You're right with the child elements. The data is returned similar to a value list. I can get the various elements OK but the splitting of the releated record data into separate elements is the problem.

I kind of know what needs doing - set a key and then use the key to select for each matching record form the related table. I'm just wondering exactly how?! I've even looked at the companion files from Beverly's developer guide but they don't seem to work and produce exactly the same result as what I'm getting

I've attached a .zip of the XML file structure required and the FM database with some sample data for two records. I've also included the XSl file that I'm messing about with. The script I'm using is Export Pupil Data.

I've stopped at trying to get the SENhistory data exported as I thought I'd get that bit sorted first before moving on to look at exporting the other related tabel data such as Attendance.

The XML file has to maintain the format as it's used by other apps.

Any help greatly appreciated

Andy

xml_export_test.zip

[Fenton]

I don't have time to go into all the fields of your export. But I think I know the technique you need, for at least the 1st child level of data.

The technique is to set the position() of the defining DATA element of each set of related data into a variable. Then use that to specify the other related DATA elements. That 1st related fields MUST have data in every one of its related records. So the safest course is to use the parent ID foreign key, UPN in your case.

One glitch which affect this, and any other related fields is that the field names, within FileMaker, MUST be unique (not just unique within their own table). Because in the xml dso export they are all at the same level, and if the same name ("UPN", "SchoolName") it will be hard to specify each. I added a prefix. So, in PreviousSchools, it's "Prev_UPN", "Prev_SchoolName". Field names which are already unique don't have this problem.

Looks like:

The specified Prev_UPN does NOT have to be returned in the data. We're just using it to set the variable. Then, to get the data, we must first step back up to the ROW level, then back down to the field and DATA line we want.

This can be done for each of the different portals. I've attached a stripped-down example xsl, of just the PreviousSchools, with the name changes, to show the technique.

[i should add that I was wrong to think you could export from the "middle" table. I cannot tell for sure if you have only 2 levels or 3. It looks like only 2, but there are several different children. If so, all can be done exporting from the parent. You couldn't easily get the different children from one of them (possibly, but it would weird). Another possibility if you have 3 levels would be to do several exports, and use the document() function of xsl to insert them. This is not as hard as it sounds; but I've seldom done it. We'd not be in Kansas anymore -]

student_history_fej.zip

Edited August 21, 2006 by Guest
no Import()

[3 Square]

Thanks again Fenton, works great.

Thanks to your help I'm starting to get a grip of the xsl grammar and have got the import and export working fine to and from multiple related tables. I've also included use of choose, concat, string and string-length functions to manipulate the data which cuts down on the number of fields required in FileMaker and makes sure the XML is correctly formatted.

I'd be interested to know if you've got any ideas or tips for how to use document() or import()? I can see me possibly needing to use this to get data into one XML doc from several non-related tables.

Andy

[Fenton]

The Document() function is fairly simple to use. I've only used it a couple times, but it worked well. Basically whatever xml document you specify, with either a relative or absolute (http:) path, becomes available to the current xsl stylesheet, and whatever is specified from it is included in the results.

The entire document can be loaded into a variable once, then its data accessed via the variable.

 and 

are not functions, they are xsl elements, for bringing in another xsl stylesheet (for you modular wiz-kids). There is no Import(). I guess I'm kind of sloppy ]

Edited August 21, 2006 by Guest

[Osman]

this topic help me too thanks