6 Replies Latest reply on Jan 18, 2015 2:16 PM by squire

    XML export from portal fields

    squire

      Sorry if this post appears twice. I appear to have posted it in a personal discussion area first, and can't see how to move it.

       

      I need to export data in XML format from a college database for a government department. This is the first time I've used FileMaker's XML export capability. Analysing output in my initial tests, I find FileMaker omitting some duplicated data when exporting from portal rows. E.g., if fields in the portal rows contain:

       

      Apples     Red

      Berries     Red

      Grapes     Green

       

      then the XML output for this section reads:

       

      <COL>

           <DATA>Apples</DATA>

           <DATA>Berries</DATA>

           <DATA>Grapes</DATA>

      </COL>

      <COL>

           <DATA>Red</DATA>

           <DATA>Green</DATA>

      </COL>

      I can see this adding significant complications to the XSL transformations I will need to do. Does anyone know how to address this issue, please?

        • 1. Re: XML export from portal fields
          mbraendle

          I don't see this behaviour (see attached example, you can used the Export XML script to test). You should inspect the export script (correct layout, grouping somewhere turned on) in your solution.

          • 2. Re: XML export from portal fields
            squire

            Martin,

             

            Thank you for this response. Certainly your file works fine. I've looked at my original test again and realised that the field data that is not included is from a related field an extra step away. I've modified your file to create a new table "Objects", with 2 fields, Color and Object. The relation is Items::Color = Objects::Color.

             

            Adding the Objects::Object field to the Portal shows up the object that matches the color

            Apples     Red     Brick

            Berries     Red     Brick

            Grapes     Green     Grass

             

            Exporting this as XML gives only:

            <DATA>Brick</DATA>

            <DATA>Grass</DATA>

             

            I.e. the extra Brick entry is missing.

             

            In practice, I can probably live with this, because of the way my college database is structured. But it would still be nice to know what's going on.

            • 3. Re: XML export from portal fields
              mbraendle

              This is the problem of the FMPXMLRESULT (and also the deprecated FMPDSORESULT) schema: It can not correctly cope with with portal data. A correct schema (as the fmresultset used with CWP is) would use another nested structure such a relatedset/record/field/data within the records; FMPXMLRESULT only uses COL/DATA, and one can not distinquish if these denote a field in the main or in the related table.

               

              I have added an example where the export is done in steps. Use script "Export XML Separate": First the object data is exported as objects.xml. These are then loaded with the document() function in the export_basket.xsl XSLT stylesheet that transforms the exported basket/items to the final baskets.xml. The XSLT also makes use of XSLT key.

              • 4. Re: XML export from portal fields
                squire

                That's really helpful, Martin. Thank you.

                • 5. Re: XML export from portal fields
                  beverly

                  The "portal" in XML is as you found it (multiple DATA under one COL). Repeating fields are the same structure. Importing related data is best done with additional XSLT (and additional import script step) to the same source.

                   

                  There is a way to "walk the portal" with the position() on the first portal row, going back to the ancestor ROW and back down to the next COL (related field) and the DATA whose position() is the same as the first COL in the portal. It can seem complex, but is doable.

                  • 6. Re: XML export from portal fields
                    squire

                    Thanks, Beverly.