Creating an XSLT file for Filemaker 12 import

[Jaesin]

I have a xml file I would like to import into Filemaker 12, however I find that I will need a XSLT file to make this work.

I do not know much about XSLT files, and have picked up a "for dummies" book covering this, but still have a few questions I could use help on.

 

I have an XSD file that would be used for validation of the XML file, does this file play any roll in the creation of my XSLT file?

 

Is it possible to import the XML file so that the data is divided into multiple tables that I have in my database? 

 

Is it a better idea to import the XML file into a single staging table, and then query that table to populate my production tables?

 

Last question, is it possible to reverse engineer the needed XSLT file by Building a table that caters to the XML structure and rules (using the XSD file) , then populating sample data, and then exporting the table and the Filemaker XSLT language? Would this create the needed XSLT file that can then be used with my XML import?

 

Sorry if these questions seem to be simplistic, I am new to filemaker with mostly previous MS Access database experiences, I am trying to learn as much as I can self taught.

 

Thank you in advance for any help given.

 

[Karsten Wolf]

I have a xml file I would like to import into Filemaker 12, however I find that I will need a XSLT file to make this work.

I do not know much about XSLT files, and have picked up a "for dummies" book covering this, but still have a few questions I could use help on.

 

I have an XSD file that would be used for validation of the XML file, does this file play any roll in the creation of my XSLT file?

 

No.

 

Is it possible to import the XML file so that the data is divided into multiple tables that I have in my database?

 

Kind of. You create one XSL for each import and have a script that does all the imports.

 

Is it a better idea to import the XML file into a single staging table, and then query that table to populate my production tables?

 

I haven't seen a need for that yet. Keep it as simple as possible. If I were you I would concentrate on importing ONE table and use the resulting XSL as a template for remaining tables.

 

Last question, is it possible to reverse engineer the needed XSLT file by Building a table that caters to the XML structure and rules (using the XSD file) , then populating sample data, and then exporting the table and the Filemaker XSLT language? Would this create the needed XSLT file that can then be used with my XML import?

 

For imports the XML is always of the structure given in FMPXMLRESULT. Google that if you haven't heard the term yet.

 

Exporting a table as XML(FMPXMLRESULT) without a stylesheet gives you the import structure; how your transformed XML has to look. The XSL still needs to be written.

 

The XSL structure usually follows the structure of your source XML.

 

Sorry if these questions seem to be simplistic, I am new to filemaker with mostly previous MS Access database experiences, I am trying to learn as much as I can self taught.

 

Thank you in advance for any help given.

 

 

If it's not confidential, post a sample XML file.

[Jaesin]

Thank you for your quick reply, as requested I have added a sample file.

You had responded to one of my questions as -

"You create one XSL for each import and have a script that does all the imports."

This is what I think would be best being that the information would be spread across multiple staging tables before being appended into my production database.

Please have a look and let me know what you think, or the best way to approach this, as I mentioned Its a bit of a learning curve, but one I am interested in learning fully.

As mentioned I also have an XSD for this if it would help, let me know

Thanks again

Sample3.0.xml

[Karsten Wolf]

By looking at the sample xml my guess would be that you want to import in at least 2 tables. the "Header" part and the "App"s.

 

Here is a import xsl for the header part. Please note, that it would import several "Header" nodes if there are more than one.

 

To play around with these open the sample.xml as "XML Data Source", choose one of the two xsl as stylesheet file and see what happens. The file "sample_app_minimal.xsl" is just a starter. You'll have to fill in the remaining fields. The other file should be complete.

 

sample xml import.zip

[Jaesin]

A big thank you, this samples you gave me really help me to understand the process. I will play with adding fields further, and try to learn as much as I can from this!

Terrific help, and I now have a working idea of how the XSL helps to transform the XML!

I do have a question about this   -       - <xsl:value-of select="position()"/>

I have tried to find some information on its purpose / function in the file, but just cant seem to understand the examples I had found on the web.

Could you give me some insight on its roll?

[Karsten Wolf]

It must be seen in context:

 

          <ROW MODID="0" xmlns="http://www.filemaker.com/fmpxmlresult">
            <xsl:attribute name="RECORDID">
              <xsl:value-of select="position()" />
            </xsl:attribute>

 

ROW is the current output node. It has an attribute MODID.

 

<xsl:attribute... generates another attribute for the current node: RECORDID.

 

It's value will be a unique number, which is required by Filemaker, generated by simply counting the current input node set. position() gives the number of the node inside the set.

 

Have a look at what is generated. This is created by running the XML and XSL files through an external XSLT-processor. These files can be imported directly into FileMaker; that is the step you don't see in FMP.

 

results.zip

 

 

[Jaesin]

Thank you again for the fast reply back.

In review of the FMPXMLResult gramar found on Filemakers site it notes the Row element is required, but the RECORDID and MODID attributes are not needed.

In experimenting I have pulled these Attributes from the ROW element, and the import results look to be the same.

Could you tell me what benefit/use is made in leaving the attributes values in this element?

[Karsten Wolf]

I did this  in FMP 10 and had the RECORDID missing and FileMaker complained about it. Perhaps it can be left empty. I don't remember now.

 

These lines get copied from stylesheet to stylesheet ;-) and 'real' questions are answered by the XSLT book from Michael Kay (highly technical, highly recommended, 2 pounds with all the answers on the topic)

[Jaesin]

I ordered a copy on Amazon of that book, thank you for the recommendation and the help.

[Smef]

I've just written a brief guide on importing data and creating XSL stylesheets for FileMaker.  Hopefully this can be helpful for anyone else who comes across this.

 

http://bluefeathergroup.com/blog/importing-xml-data-into-filemaker-using-xslt-xsl-style-sheets/

[beverly]

Nice article, David! I always advise to EXPORT from each table where you want to IMPORT into FileMaker. This is the basis for most of your XSLT. it really makes it easy to use "matching names", makes it easy to create the XSLT. From there you add the bits as needed.

 

All the other posters are correct about ONE XSLT per table to import. This is a FMP convention and true for any kind of import. You can create a script to make successive imports from the same source with different XSLT (go to layout for each table as needed, of course!)

 

The main parts of import with XML/XSLT is the 'mapping' using XPATH and XSL functions so that the correct data goes from the XML source into the correct location on the XML result (as seen in the import dialog).  Studing XPATH axes is helpful to know about the location of the XML elements (and/or attributes) you need to match to the FMP fields.

 

http://www.w3schools.com/xsl/xpath_intro.asp

Edited December 15, 2015 by Lee Smith
updated the link