CODEWITHSUNDEEP
xmlxsltimportfilemaker
Tom
Tom

Reputation: 2873

importing complex XML data into multiple FileMaker tables

I understand the basics of import to FileMaker (csv, xml) and I know a little about XSLT.

I have a data set containing lists that I need to import into FileMaker. There are 3 tables for this - the main table, the datapoints table and the positions table. My data looks like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<result>

<data mode="test" ram="on">
    33,869
    34,115
    46,074
    225,233, E
    226,122, E
    235,763, E
    237,408, E
    237,722, E
    242,503
    256,271
    273,741
</data>

<statistics>

    <positions>
        <position id="1" unit="c">0,00</position>
        <position id="2" unit="c">5,05</position>
        <position id="3" unit="c">14,30</position>
        <position id="4" unit="c">23,47</position>
    </positions>
</statistics>    
</result>

how do I import this XML into FM in one go? I understand I need to convert it to fmpxmlresult, but everything I've read assumes that the XML is essentially a fancy CSV - individual rows without related/sub-rows.

And yes, the positions data is properly XML'ified, and the data data is newline-seperated and I know that's not the XML way, but it's the way I receive the data.

Is there something I can do to make this easy for my users? I can pre-process the data outside of FileMaker if absolutely necessary, but would like to avoid that if possible.

(split off from this question - Export and Import date from/into current record only in FileMaker 18 - which contained a simplified version of this question and an unrelated other question, someone remarked I should ask one question at a time)

Upvotes: 0

Views: 598

Answers (3)

MrWatson
MrWatson

Reputation: 516

Here's how you can simplify the triple-import for the users:

  • create a global container
  • place it on the layout (or on a temp layout specifically for this process)
  • in your script use the Insert File script step to let the user choose a file
  • set it up to always insert a reference, no zipping, no choice
  • then you can GetAsText the container and extract the path into a variable $filePath
  • after that you can use the variable $filePath to specify the file in the proceeding imports and set them to run with no dialog.

Voila!

I don’t know why this answer has been voted down. It is a suggestion how to solve the Corresponding problem of choosing a source file once but performing two imports, as the poster asked in his reaction to the solution above.

Upvotes: -1

michael.hor257k
michael.hor257k

Reputation: 117103

To import the positions into a table with fields for PositionID, Unit and Value, you can use the following stylesheet:

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

<xsl:template match="/result">
    <FMPXMLRESULT>
        <METADATA>
            <FIELD NAME="PositionID"/>
            <FIELD NAME="Unit"/>
            <FIELD NAME="Value"/>
        </METADATA>
        <RESULTSET>
            <xsl:for-each select="statistics/positions/position">
                <ROW>
                    <COL>
                        <DATA>
                            <xsl:value-of select="@id"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="@unit"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="."/>
                        </DATA>
                    </COL>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

To import the data into a table with two target fields (not sure what to call them), you can use:

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

<xsl:template match="/result">
    <FMPXMLRESULT>
        <METADATA>
            <FIELD NAME="A"/>
            <FIELD NAME="B"/>
        </METADATA>
        <RESULTSET>
            <xsl:call-template name="tokenize">
                <xsl:with-param name="text" select="data"/>
            </xsl:call-template>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

<xsl:template name="tokenize">
    <xsl:param name="text"/>
    <xsl:param name="delimiter" select="'&#10;'"/>
    <xsl:param name="sep" select="', '"/>
    <xsl:variable name="token" select="normalize-space(substring-before(concat($text, $delimiter), $delimiter))" />
    <xsl:if test="$token">
        <ROW>
            <COL>
                <DATA>
                    <xsl:value-of select="substring-before(concat($token, $sep), $sep)" />
                </DATA>
            </COL>
            <COL>
                <DATA>
                    <xsl:value-of select="substring-after($token, $sep)" />
                </DATA>
            </COL>
        </ROW>
    </xsl:if>
    <xsl:if test="contains($text, $delimiter)">
        <!-- recursive call -->
        <xsl:call-template name="tokenize">
            <xsl:with-param name="text" select="substring-after($text, $delimiter)"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>

Upvotes: 2

AndreasT
AndreasT

Reputation: 2337

The import procedure is still as I described in my answer on the question you link to. You need an XSLT file to specify during import to transform your XML to FileMaker's schema. This is not very difficult with a simple source file, but still necessary.

Upvotes: 1

Related Questions