2 Replies Latest reply on Nov 16, 2010 4:42 PM by FentonJones

    XML I/O

    JoeBrown

      Title

      XML I/O

      Post

      I have an XML file which I would like to import to FMP and parse, merge with data from my database then export the xml in it's original format.  The file is xml version 1.0 UTF-8 encoding and the doctype is ozxmlscene.  Any ideas?

      Thanks

        • 1. Re: XML I/O
          FentonJones

          Yes, this is possible. Yes, it also involves some somewhat tedious work. FileMaker has its own XML format; which you can see by doing an Export, then choosing XML, with FMPXMLRESULT as the grammar; but no XSL stylesheet chosen. 

          That is the format that FileMaker Imports also. So that is what you must produce, by transforming your original XML via a custom XSL stylesheet. It depends mostly on your original XML whether this is difficult or not. It depends on how many fields how tedious it is to write. It is mostly the same thing over and over, but specifying a different field.

          Fortunately most of the stuff at the top of the XSL is the same for any FileMaker file. So you can just copy/paste it into the stylesheet as is. The next METADATA section is the xml description of the fields. So the field names are different for each line, and maybe which type of field, TEXT, NUMBER, etc.. But the rest is repetitive.

          Then you must decide WHAT to define as the specifier within the data which will tell FileMaker "this is a new record" (ROW in XML). Then each field is a <COL><DATA>, inserting the appropriate value from your original XML. If done correctly, then you'll see the fields you defined in the Metadata section when you Import XML source from FileMaker, your xml, using that xsl stylesheet. They will appear just like any other fields, and the Import will work just like any other Import. If there is any error, nothing will work :-]

          Hence I recommend you get some kind other program to write your xsl stylesheet and test the transformation. If there's no fatal errors in the xsl (and almost all structural errors are fatal, it's simple that way), then you will get something like FileMaker XML as the result, hopefully with data from your xml. I don't even try to Import XML into FileMaker until I've seen the result. You can easily test the resulting xml file (which is just a text file with an "xml" extension) by using Open from FileMaker, from XML Source file... If all is well, it will open it, create the fields as specified, and import the data.

          Then you've got the opposite task when you Export XML from FileMaker. You need a stylesheet to transform the FileMaker XML back to the original, recreating its structure, and inserting the FileMaker data.

          One trick to this is to know that FileMaker attaches its "namespace" to the resulting data, so you need to declare its namespace, identify its elements with a prefix, then remove that prefix from the result. Looks something like this. This is dummy stylesheet for Export XML. It would just tumble all the data in one long string, as it has no XML Elements added. But you can use this header, and general idea:

          <?xml version="1.0" encoding="utf-8"?>
          <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >
          <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
          <xsl:template match="/">
          <xsl:for-each select="fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW">
          <xsl:for-each select="fm:COL/fm:DATA">
          <xsl:value-of select="."/>
          </xsl:for-each>
          </xsl:for-each> 
          </xsl:template>
          </xsl:stylesheet>

          • 2. Re: XML I/O
            FentonJones

            P.S. If anyone knows the magic HTML tag which would let us paste XML code in without going crazy, please let me know. It used to be <pre>, but that was long ago. The HTML editor kind of web forum is often a PITA.