Importing a UK Govt formatted xml into Filemaker

[blueworld4]

I am having some trouble getting my XSLT file to work.  I have followed many of the posts on here and have referenced

https://www.w3schools.com/xml/xsl_intro.asp

https://www.w3schools.com/xml/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog

I seem to be able to get the examples to work and have had success with the example files for metars.cache.xml weather meter readings, but can't see why my own isn't working properly when it looks the same to me.  Any guidance would be gratefully received.  I am not new to Filemaker but this is my first attempt at importing and exporting data to/from our solution in XML which meets the UK Government standards.  My XSLT seems only to match fields but not actually import and records.  I have attempted referencing the template match to root with  <xsl:template match="/Message"> but this causes an "Invalid Document Structure" error in Filemaker, which doesn't seem to be a problem with the w3schools try xslt page.  I have reverted back to the format in the meters.xslt example file and now hope that somebody can help!!

metars.cache.xml

meters.xslt

ILR-99999999-1617-20160229-144401-01.xml

ILRLearningDeliveryImport.xslt

[comment]

This is confusing. Which of the two XML files attached to your post is the real file you want to import? 

[comment]

I am guessing you are having a problem with the "ILR-99999999-1617-20160229-144401-01.xml" file. The reason for this is that all the elements in the file are in a namespace.  They are put there by the default namespace declaration:

xmlns="SFA/ILR/2016-17" 

carried by the root Message element and inherited by all it descendant elements.

This means all your select expressions select nothing. In order to select an element in a namespace, you must declare the same namespace in your stylesheet, assign it a prefix (an arbitrary string), and use that prefix to address the elements in the source XML:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ilr="SFA/ILR/2016-17">

<xsl:template match="/">    
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">    
        <METADATA>    
            <FIELD NAME="LearnAimRef" TYPE="TEXT"/>    
            <FIELD NAME="AimType" TYPE="TEXT"/>    
            <FIELD NAME="AimSeqNumber" TYPE="TEXT"/>    
            <FIELD NAME="LearnStartDate" TYPE="TEXT"/>    
            <FIELD NAME="LearnPlanEndDate" TYPE="TEXT"/>    
            <FIELD NAME="FundModel" TYPE="TEXT"/>    
            <FIELD NAME="ProgType" TYPE="TEXT"/>
        </METADATA>    
        <RESULTSET>    
            <xsl:for-each select="/ilr:Message/ilr:Learner/ilr:LearningDelivery">  
                <ROW>    
                    <COL><DATA><xsl:value-of select="ilr:LearnAimRef"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:AimType"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:AimSeqNumber"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:LearnStartDate"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:LearnPlanEndDate"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:FundModel"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:ProgType"/></DATA></COL>    
                </ROW>    
            </xsl:for-each>  
        </RESULTSET>    
    </FMPXMLRESULT>    
</xsl:template>
    
</xsl:stylesheet>   

 

Note that I have also deleted the:

<COL><DATA><xsl:value-of select="ilr:elevation_m"/></DATA></COL> 

part from your attempt, because I don't see an elevation_m element in your input nor a corresponding field in the field names you have defined.

[ggt667]

Sometimes XML/XSLT is better with a good tool; I bought Xmplify

The moment the files are 2 MB and up you are not really interested in using for-each; you want to call template

Edited May 9, 2017 by ggt667

[blueworld4]

Dear Both,

Thank you so much for this, "comment" of course your solution worked. "ggt667" I'll definitely check out Xmplify.

What a relief it is, to know where to start   now I can start to break down the file!

Many thanks

[blueworld4]

5 hours ago, comment said:

I am guessing you are having a problem with the "ILR-99999999-1617-20160229-144401-01.xml" file. The reason for this is that all the elements in the file are in a namespace.  They are put there by the default namespace declaration:

xmlns="SFA/ILR/2016-17" 

carried by the root Message element and inherited by all it descendant elements.

This means all your select expressions select nothing. In order to select an element in a namespace, you must declare the same namespace in your stylesheet, assign it a prefix (an arbitrary string), and use that prefix to address the elements in the source XML:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ilr="SFA/ILR/2016-17">

<xsl:template match="/">    
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">    
        <METADATA>    
            <FIELD NAME="LearnAimRef" TYPE="TEXT"/>    
            <FIELD NAME="AimType" TYPE="TEXT"/>    
            <FIELD NAME="AimSeqNumber" TYPE="TEXT"/>    
            <FIELD NAME="LearnStartDate" TYPE="TEXT"/>    
            <FIELD NAME="LearnPlanEndDate" TYPE="TEXT"/>    
            <FIELD NAME="FundModel" TYPE="TEXT"/>    
            <FIELD NAME="ProgType" TYPE="TEXT"/>
        </METADATA>    
        <RESULTSET>    
            <xsl:for-each select="/ilr:Message/ilr:Learner/ilr:LearningDelivery">  
                <ROW>    
                    <COL><DATA><xsl:value-of select="ilr:LearnAimRef"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:AimType"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:AimSeqNumber"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:LearnStartDate"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:LearnPlanEndDate"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:FundModel"/></DATA></COL>    
                    <COL><DATA><xsl:value-of select="ilr:ProgType"/></DATA></COL>    
                </ROW>    
            </xsl:for-each>  
        </RESULTSET>    
    </FMPXMLRESULT>    
</xsl:template>
    
</xsl:stylesheet>   

 

Note that I have also deleted the:

<COL><DATA><xsl:value-of select="ilr:elevation_m"/></DATA></COL> 

part from your attempt, because I don't see an elevation_m element in your input nor a corresponding field in the field names you have defined.

A further query, is it possible with this structure to take values from the parent? i.e.. <COL><DATA><xsl:value-of select="../LearnRefNumber"/></DATA></COL>

I really need to get these related fields into the import so that I can related the data in Filemaker.

<COL><DATA><xsl:value-of select="../LearnRefNumber"/></DATA></COL>

<COL><DATA><xsl:value-of select="../ULN"/></DATA></COL>

<COL><DATA><xsl:value-of select="/ilr:Message/ilr:LearningProvider/UKPRN"/></DATA></COL>

I'm not sure how this would be achieved, as you can see I have tried using the parent expression but it's not working.

Thanks again 

[comment]

To get the LearnRefNumber:

<xsl:value-of select="../ilr:LearnRefNumber"/>

 

To get the ULN:

<xsl:value-of select="../ilr:ULN"/>

 

To get the UKPRN, you could either go up to the grandparent, then down:

<xsl:value-of select="../../ilr:LearningProvider/ilr:UKPRN"/>

or start at the top:

<xsl:value-of select="/ilr:Message/ilr:LearningProvider/ilr:UKPRN"/>

But since there is only one LearningProvider, it would be smart to define it as a variable once, then reuse it, e.g.:

 

		...  
		<RESULTSET>
			<xsl:variable name="UKPRN" select="/ilr:Message/ilr:LearningProvider/ilr:UKPRN" />    
			<xsl:for-each select="/ilr:Message/ilr:Learner/ilr:LearningDelivery">  
				<ROW>
					...
					<COL><DATA><xsl:value-of select="$UKPRN"/></DATA></COL> 
					...   
				</ROW>    
			</xsl:for-each>  
		</RESULTSET>
		...    

 

 

--
Note the use of the prefix in every reference to an element in the source XML. Think of it as a family name; elements will not respond when being called by their first name only.