Jump to content
Jeff M

XML Import & Export

Recommended Posts

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

Share this post


Link to post
Share on other sites

1a)

$ git clone https://github.com/TyrfingMjolnir/nmap2fmpxmlresult

 take port or node or 1b) copy this file's raw representation: https://github.com/TyrfingMjolnir/nmap2fmpxmlresult/blob/master/nmap2fmpxmlresult_port.xslt

2) and modify to fit your needs.

Edited by ggt667

Share this post


Link to post
Share on other sites
26 minutes ago, Jeff M said:

Not sure if it makes sense to import into 2 tables (workflows, workflow steps) or just a single table.

That depends on what exactly do you mean by "manipulate the data, do some various lookups, etc.". If it requires maintaining the parent-child relationship between workflows and workflow steps, then you will certainly want to do two imports, one into each table.

In any case, you will need custom XSLT stylesheet/s to transform the XML into Filemaker's FMPXMLRESULT grammar. Will all workflow elements always have the same attributes, in the same order? If yes, you could use the following stylesheet to import them:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/data">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <METADATA>
            <xsl:for-each select="workflows/workflow[1]/@*">
                <FIELD NAME="{name()}"/>
            </xsl:for-each>
        </METADATA>
        <RESULTSET>
            <xsl:for-each select="workflows/workflow">
                <ROW>
                    <xsl:for-each select="@*">
                        <COL><DATA><xsl:value-of select="."/></DATA></COL>
                    </xsl:for-each>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Similarly, if all workflow steps follow the same pattern of attributes, you can import them using:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/data">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <METADATA>
            <xsl:for-each select="workflows/workflow[1]/workflow_step[1]/@*">
                <FIELD NAME="{name()}"/>
            </xsl:for-each>
        </METADATA>
        <RESULTSET>
            <xsl:for-each select="workflows/workflow/workflow_step">
                <ROW>
                    <xsl:for-each select="@*">
                        <COL><DATA><xsl:value-of select="."/></DATA></COL>
                    </xsl:for-each>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

 

Share this post


Link to post
Share on other sites

First of all, thank you so much for the example code. I'm having some trouble with it, however...I wonder if the field name and attributes need to be called out to address the issue.

In the sample data, you'll notice the first 2 workflow steps do not contain a description attribute, but the second 2 do contain it. I believe this is causing the attributes to flow into the wrong fields upon import. As a result I'm getting the description value in the wf_state_id field. I'm attaching my filemaker file for reference.

I've seen examples where the XSLT contains more specific mapping information...perhaps that's what is needed here?

The second part of my challenge, however, is the export. I need to be able to export the data from filemaker in same format as the original sample xml. Do I need a stylesheet for that as well?

workflows_wip.fmp12.zip

Share this post


Link to post
Share on other sites
31 minutes ago, Jeff M said:

you'll notice the first 2 workflow steps do not contain a description attribute, but the second 2 do contain it. I believe this is causing the attributes to flow into the wrong fields upon import.

As I said, this was based on the assumption that all alike elements have exactly the same attributes, in the same order. If some workflow_step elements have a description attribute and some don't, then obviously this assumption is wrong. 

Do you have a list of all possible attributes that each element can have?

 

35 minutes ago, Jeff M said:

I need to be able to export the data from filemaker in same format as the original sample xml. Do I need a stylesheet for that as well?

Yes. But that is a separate problem and I suggest you put it aside for the moment.

 

Share this post


Link to post
Share on other sites

Sure:
 

workflow can have:

id, name, name_de, assettype_def, domain, domain2, enabled, ten, rowid

 

workflow_step can have:

wf_id, wf_step, name, name_de, description, wf_state_id, def_party, color, sorting, enabled, tcn, rowid
 

 

Share this post


Link to post
Share on other sites
26 minutes ago, Jeff M said:

workflow can have:

id, name, name_de, assettype_def, domain, domain2, enabled, ten, rowid

But I see that the second workflow also has description and def_step attributes? 

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 Gilles Plante
      Hi,
       
      I am running version 17.0.2 - in demo mode for not but we will a buy a licence today.
      In the bottom pane I have a list of fields for a table and would like to get that list inside a FileMaker file. When I right-click on the list - all items are selected - I get a choice Calle Copy for FIleMaker. What do I get when picking this menu choice, and how can I paste in FileMaler ?
      Thanks
       
    • By DarioDN
      Dear all, I've just create a script to export my records in multiple xml file (one xml for each record) applying also an XSL stylesheet.
      So, if I use the style.xsl taking from my computer ("source" button when filemaker ask me to select the file) it works. If I put the same file online, it doesn't work giving me a SAXParseException error: internal error in NetAccess (occurred in an unknown entity, at line 0, column 0).
      In the same folder where I have the style.xsl there are other xsl files that works, but they are used to "import" process (from XML to FILEMAKER, while in this case I'm working to an export process, from FILEMAKER to XML, I don't know if this could be the problem)
      Someone could help me to understand the error? Thank you
      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp" > <xsl:output omit-xml-declaration="yes" indent="yes"/> <xsl:strip-space elements="*"/> <xsl:template match="fmp:FMPXMLRESULT"> <xsl:for-each select="fmp:RESULTSET/fmp:ROW"> <xsl:text>&#xA;</xsl:text> <xsl:text disable-output-escaping="yes">&lt;!--</xsl:text> <xsl:value-of select="fmp:COL[1]/fmp:DATA"/>.<xsl:value-of select="fmp:COL[2]/fmp:DATA"/>.<xsl:value-of select="fmp:COL[3]/fmp:DATA"/><xsl:text disable-output-escaping="yes">--&gt;</xsl:text> <problem display_name="Checkboxes" > <choiceresponse> <label><xsl:value-of select="fmp:COL[4]/fmp:DATA"/></label> <checkboxgroup> <choice correct="{fmp:COL[6]/fmp:DATA}"> <xsl:value-of select="fmp:COL[5]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[7]/fmp:DATA"/> </choicehint> </choice> <choice correct="{fmp:COL[9]/fmp:DATA}"> <xsl:value-of select="fmp:COL[8]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[10]/fmp:DATA"/></choicehint> </choice> <choice correct="{fmp:COL[12]/fmp:DATA}"> <xsl:value-of select="fmp:COL[11]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[13]/fmp:DATA"/></choicehint> </choice> </checkboxgroup> </choiceresponse> </problem> <xsl:text>&#xA;</xsl:text> </xsl:for-each> </xsl:template> </xsl:stylesheet>  
    • By Franziska B
      Dear 360 Works Team

      In our FileMaker Solution we want to create an Excel report including text, numbers (dates) and images via Scribe.

      The FileMaker export of text and numbers works fine. But we have a problem with the export of images in the xlsx.

      After the Creation of the Excel reports all text- and number elements are in the .xlsx but the image not.


       
      When we open the generated .xlsx, Excel shows the attached error massage:


      For the export of the pictures, we test following script commands:  
       
      ScribeDocValue[Name:“F5“;Value: table:: cell]
       
      ScribeDocWriteValue("Table1!F5", table::cell)
      SetVariable[$PicXYZ; Value: ScribeDocWriteValue("Table1!F5", table::cell)]
       
      Do we make a mistake, or could this be a known bug?
       
      Our Systemconfig:
      FileMaker Pro Advanced 16.0.4.403
      Excel 2010 and Office 365;          
      360 Works Scribe 3.08 and Scribe 3.09
      Server:
      FM Server 16.0.3.304
      Microsoft Windows Server 2008 R2 Standard
      Version 6.1.7601 Service Pack 1 Build 7601
      Could you help me please?
      Thanks


    • By Jhon Doe
      Hi everyone,
      I'm a beginner in FM, and i'm trying to develop a solution that allows me to import data from a csv file. I have two related tables Customer ("parent") and Alerts ("child"), each one has a primary key and the Alerts table has a foreign key to conect with the Customer table. I have to populated the alerts table from a cvs file that could have lots of records so I want to relate this new data to the right customers automatically when i import it, the new data does not have a field with the customer ID (primay key) only has its name. Could you please give me any advice with that?
×

Important Information

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