Including and matching data from another XML file via XSL

[Codeus]

Hi there,

I am having trouble matching the contents of a second XML file to be merged with my export via XSLT / FMPXMLRESULT.

I have a bunch of records for individual pupils each with a key field (UPN). I then have a separate xml file (in FMPXMLRESULT format) with a list of exclusions - COL[1] of each record contains the UPN key field as a match field with the pupil records. COL[2] of each record contains the data I need to insert (exclusion start date).

Each pupil could have none, one or many exclusion records depending on whether or not they have ever been excluded from school.

Here is an excerpt of what I have so far: -

This seems to be grabbing the data ok but obviously it is printing all exclusion records against each and every pupil. How do I tell it to only show records where the upn from the exclusion record (child) matches the upn from the pupil record (parent)?

I think I am trying to match: -

fmp:COL[24]/fmp:DATA

... with ...

$exclusions/fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[$exclusionPos]/fmp:COL[2]/fmp:DATA ).

I know I could normally export child record data at the same time as the parent data and use position() to match it up but I am actually exporting from a higher level in my database which means that the pupil records are children and these exclusion records are actually 2 tiers below the root table and are effectively unrelated to the exporting table.

Kind regards,

Codeus

[beverly]

Are you exporting related fields or trying to use 'document()' to grab (and match)?

[Codeus]

Hi Beverly,

I am using document() to grab the exclusion records and am then trying to match them to the relevant pupil records.

The problem I have is that I am unsure how to match zero, one or many exclusion records with a matching UPN in COL[1] (from the document() variable) to a pupil record with the UPN in COL[24] of the main export.

Kind regards,

Codeus

[Codeus]

Hi again,

Maybe it would help if I explained what I am trying to do. I have to export 3 levels of data to a single XML file for output to local government. I am currently exporting from the top level (school data - single record). I can export the school data from level 1 and the pupil data from level 2 with no problems but run in to trouble when I add the third-level exclusion data.

Here is a visual of the data layout: -

This is also how the data must appear in the output (ie with the exclusion records nested in the pupil records).

From looking around I think I need to use key() and a template match but I am unsure how to get this to work. From what I understand there are issues with referencing an external document as a key match.

As always, any help would be very much appreciated.

Codeus

[Fenton]

I believe the problem is that you need to match the UPN in the Exclusions file (yes you know that -). But you haven't tested against it. In pseudo logic:

set the current UPN of the student into a variable

get the Exclusions document

For-each select="whatever node your exclusion UPN data is in[$UPN= relative location of the UPN element]"

(will only get the node matching your current student's UPN)

other exclusion data's relative locations

It would seem easier to export it all in one piece from Students table however, if this is all accessible FileMaker files/tables. You say it is "unrelated", but I think it could be related; via the UPN. It's just a question of creating the table occurrences and relationships, and possibly a layout (if you want to do it in a separate table occurrence group).

[Codeus]

Thanks Fenton!

I knew I needed to match the upn from the external file to the internal records somehow but couldn't figure out how to do that.

Your suggestion is really neat and works perfectly.

The reason I wanted to export from the top-level table rather than from Pupils is that in the future I can envisage needing to add more sub-tables at the top-level of the table - perhaps for a table for example.

I have posted the final code below incase it helps others: -

<?xml version='1.0' encoding='utf-8'?>



    xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp">

    

    

        

        

            

        

        

            

                

                

                

                

                    

                    

                        

                            

                        

                    

                

            

        

        

    

and a sample .xml document that the above produces: -

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





    

        SomeSchool

    

    

        Fred

        

            

                1/1/2001

            

            

                2/2/2002

            

            

                3/3/2003

            

        

        Bert

        

            

                1/1/2001

            

        

        Ernie

        

            

                4/4/2004

            

            

                5/5/2005

            

        

    

I have also attached the database, xsl and xml files.

Once again, thanks for all your help.

Codeus

3level_xml.zip

[Fenton]

Good. But shouldn't there be a to enclose the name and exclusions?

        Fred

        

            

                1/1/2001

            

            

                2/2/2002

            

            

                3/3/2003

            

        

[Codeus]

You're quite right! Each pupil is a distinct node.

While I was trying to get the principle of the import, I cut out a quite a few bits to simplify things and dropped the xsd association.

The actual format has many more fields which I will now start to build up again.

Cheers,

Codeus

[3 Square]

Hi,

I'm working on a similar School Census return thing, is it possible for us to compare notes Codeus?

We already have data download and CTF import/export working via XSL and FileMaker if they're of any interest to you.

I can also help with what's going to appear in SC 2008 if that's any good?