XML Import & Export

[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

[ggt667]

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 July 26, 2017 by ggt667

[comment]

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>

 

[Jeff M]

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

[comment]

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.

 

[Jeff M]

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
 

 

[comment]

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?