Jump to content
blueworld4

Importing a UK Govt formatted xml into Filemaker

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Similar Content

    • By Olypatchmaster
      Our network person and I have done a bunch of troubleshooting and came up with this summary and a few questions.
      Ever since we installed FileMaker 16 in our two-machine setup, Zulu has been failing. In the Zulu error log from C:\Program Files\360Works\Applications\logs:
      Oct 2, 2017 8:44:59 AM PDT com.prosc.mirror.config.server.FileMakerServerInfo getHostAddressForProperties
      INFO: Will use 127.0.0.1:16020 for direct WPE address.
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FmXmlRequest doRequest
      INFO: Starting request: http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FmXmlRequest setProductVersion
      WARNING: Null product version; assuming 12
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FileMakerException <init>
      INFO: com.prosc.fmpjdbc.FileMakerException: Error 959: XML Web Publishing is not enabled - Run the FileMaker Server deployment assistant and make sure that the XML web publishing checkbox is selected () username: domain\user / requestUrl:http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view

       I verified that the URL does indeed return an error code of 959. Playing around with it, I found that if you use the DNS name of the machine instead of the localhost IP:port (https://wpe.domain.edu/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view), the request returns an error 105 (expected response) instead. 
       
      I poked around on the internet trying to find out more about this, and discovered the -dbnames command; again, it works fine on the main https port (https://wpe.domain.edu/fmi/xml/FMPXMLRESULT.xml?-dbnames), but returns a 959 on the Custom Web Publishing port of 16020 (http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-dbnames). So it’s like XML is enabled on port 443, but not for the Custom Web Publishing port 16020. How is that possible?
       
      For reasons we don’t fully understand and haven’t figure out how to correct, both the FileMaker Master and the Worker machines ended up with the Web Publishing Engine. So I tried the port 16020 request on the Master server, and it works! Which is great, except we want it to work on the Worker machine, not the Database server which ended up with a WPE.
       
      Some questions:
      1)      We know XML is enabled on the main (port 443) site, what do we need to do to make work on the localhost:16020 site?
      a. I hope this involves moving the ‘Web Server’ listed in the Admin Console to the Worker machine, but if not any info on what might be going on there would be great.
      2)      If we can’t get the Worker machine to realize XML is enabled on port 16020, can we change the FM config so Zulu doesn’t use port 16020 ‘for direct WPE address.’?
      3)      If we can’t do that, are there any issues with installing it on the Master, other than the obvious issue that we don’t want the WPE on that machine?
      Thanks for any insight anyone might have.
    • By fmdataweb
      I'm doing an export of a table with 5 fields to an Excel file with an XSL stylesheet to control the formatting of the Excel data. So far it's working well - I just need to add some additional formatting for a few of the cells. I'm looking for help with examples/syntax for setting the following attributes:
      - making a cell bold
      - changing the font size of a cell
      - making all columns set to auto resize so they fit the contents of each column (same as double clicking the divider between 2 columns)
      All fields I'm exporting are text fields.
      Thanks,
      Steve
    • By Jeff M
      Hello,
      I've done quite a bit with FileMaker in the past, but have never jumped into the XML arena. Now I have a need to do so, and I'm really needing some help with something I'm sure is quite basic...but still foreign to me. I have an external data source which export XML in a very specific format (see attached). I need to import that into FileMaker so I can manipulate the data, do some various lookups, etc. Once I've made my changes in the data, I need to export in the exact format it was when imported so the external data source can properly import.
      Not sure if it makes sense to import into 2 tables (workflows, workflow steps) or just a single table. I'm open either way. Once the data is in FileMaker I can get all my calculations and lookups and scripts going and make the data what I need it to be, but then I'll need to export it.
      Can someone point me in the right direction?
      Thanks so much in advance!
      Jeff
      workflows_export.xml
    • By Andreas T.
      I need some help in determining if I am missing something crucial here or if there is a limitation or bug in the function BE_ApplyXSLT from the BaseElements plugin. I am using version 3.3.4 (which is the latest) on Mac OS X 10.11.6 using FileMaker 15 Adv. and 16 Adv.
      I am unable to get any output from the transformation, nor any error.
      According to the documentation, the command shall return either 0 when successful or an error. Link
      In my testing it only returns blank, and gives no error even when no output file is generated. It should not return blank in cany case according to the docs. (I suspect there may be a documentation error and that it actually returns blank when there is no error.)
      No error is found using Get(LastError). BE_GetLastError returns 0.
      The function format is this:
      BE_ApplyXSLT ( xmlPath ; xslt ; outputPath ) Here are the input parameters I have used:
      BE_ApplyXSLT (      "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/ABC_nota_108.xml" ;      BE_ReadTextFromFile ( "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/HTMLvisning/Stylesheet_Full.xsl" );     "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/output.html" ) I have verified that the files exist and that both the XML and XSLT are valid. All files are in UTF-8. I can transform the XML with this XSLT using other
      methods without errors. I have also tried having the XSLT in a variable.
      As I understand it, BaseElements uses the libxml2 engine. I have tried doing the transform from the command line directly and it works as it should:
      xsltproc -o /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/output.html /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/HTMLvisning/Stylesheet_Full.xsl /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/ABC_nota_108.xml It also works fine using the XMLLib OSAX in AppleScript. Unfortunately, I need Windows support also so I would like to get the BaseElements function to work.
      I hope somone can shed some light on what may be going on.
×

Important Information

By using this site, you agree to our Terms of Use.