Trouble parsing XML with XSLT style sheet

Question

I am having trouble importing XML returned by Salesforce Marketing Cloud API into a FileMaker database. I've done this with other XML sets with no issue, but this XML is different than what I have seen before. Here is an example of what comes back:

<Results xsi:type="DataExtensionObject">
                <PartnerKey xsi:nil="true"/>
                <ObjectID xsi:nil="true"/>
                <Type>DataExtensionObject</Type>
                <Properties>
                    <Property>
                        <Name>Account</Name>
                        <Value>000000</Value>
                    </Property>
                    <Property>
                        <Name>LastName</Name>
                        <Value>Doe</Value>
                    </Property>
                    <Property>
                        <Name>FirstName</Name>
                        <Value>John</Value>
                    </Property>
                    <Property>
                        <Name>EmailAddress</Name>
                        <Value>[email protected]</Value>
                    </Property>
                                        <Property>
                        <Name>JobName</Name>
                        <Value>30029-Name</Value>
                    </Property>
                    <Property>
                        <Name>Primary Key</Name>
                        <Value>000000</Value>
                    </Property>
                    </Properties>
            </Results>

The multiple occurrences of the <Property> tag has me a little stumped on mapping the xpath. Any thoughts are appreciated.   

Here is a bit of the stylesheet I tried to make work, starting with a simple attempt to just parse one field. This gets no result.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tmp="http://tempuri.org" exclude-result-prefixes="SOAP-ENV tmp" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
    <xsl:template match="/">
        <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
            <!-- FIELDS -->
            <METADATA>
                <FIELD NAME="f1" TYPE="TEXT"/>              
             </METADATA>        
                <!-- DATA -->
                <RESULTSET>
                    <xsl:for-each select="Results/Properties">
                        <ROW>
                            <xsl:if test="Property/Name='Account'">
                                <COL>
                                    <DATA>
                                        <xsl:value-of select="Value"/>
                                    </DATA>
                                </COL>
                            </xsl:if>                                                     
                        </ROW>
                    </xsl:for-each>
                </RESULTSET>
            </FMPXMLRESULT>
        </xsl:template>
    </xsl:stylesheet>

Answer 1

The correct way to import your example* would be like this:

<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="/Results">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <!-- FIELDS -->
        <METADATA>
            <FIELD NAME="Account"/>              
            <FIELD NAME="LastName"/>              
            <FIELD NAME="FirstName"/>              
            <FIELD NAME="EmailAddress"/>              
            <FIELD NAME="JobName"/>              
            <FIELD NAME="Primary Key"/>              
        </METADATA>        
        <!-- DATA -->
        <RESULTSET>
            <xsl:for-each select="Properties">
                <ROW>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='Account']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='LastName']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='FirstName']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='EmailAddress']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='JobName']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="Property[Name='Primary Key']/Value"/>
                        </DATA>
                    </COL>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

As I mentioned in the comment to your question, this will create a single record with 6 fields. If there can be multiple records, and each set of fields is in its own Properties element, then this will import them correctly too.

If every record will always have the same set of fields, listed in the same order, then you could shorten this to:

<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="/Results">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <!-- FIELDS -->
        <METADATA>
            <xsl:for-each select="Properties[1]/Property">
                <FIELD NAME="{Name}"/>              
            </xsl:for-each>
        </METADATA>        
        <!-- DATA -->
        <RESULTSET>
            <xsl:for-each select="Properties">
                <ROW>
                    <xsl:for-each select="Property">
                        <COL>
                            <DATA>
                                <xsl:value-of select="Value"/>
                            </DATA>
                        </COL>
                    </xsl:for-each>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

---

(*) provided the missing namespace declaration for the xsi prefix is added.