Stuck Campaign Monitor XML into FileMaker

Question

I'm trying to import an export from the Campaign Monitor API into FileMaker but am stuck with getting the XSLT to work with the Custom Fields.

I'm trying to key the Values of ID and Mr_or_Mrs.

I think I need to do something with the key function but whatever I've tried hasn't worked.

XML:

<PagedResult>
<NumberOfPages>1</NumberOfPages>
<OrderDirection>asc</OrderDirection>
<PageNumber>1</PageNumber>
<PageSize>1000</PageSize>
<RecordsOnThisPage>439</RecordsOnThisPage>
<Results>
<Subscriber>
  <CustomFields>
    <CustomField>
      <Key>[Mr_or_Mrs]</Key>
      <Value>Mrs_email</Value>
    </CustomField>
    <CustomField>
      <Key>[ID]</Key>
      <Value>ABCEF1234</Value>
    </CustomField>
  </CustomFields>
  <Date>2015-06-15 17:40:00</Date>
  <EmailAddress>[email protected]</EmailAddress>
  <Name>Mrs Smith</Name>
  <ReadsEmailWith/>
  <State>Active</State>
</Subscriber>
<Subscriber>
  <CustomFields>
    <CustomField>
      <Key>[Mr_or_Mrs]</Key>
      <Value>Mr_email</Value>
    </CustomField>
    <CustomField>
      <Key>[ID]</Key>
      <Value>1234567890</Value>
    </CustomField>
  </CustomFields>
  <Date>2015-06-15 17:40:00</Date>
  <EmailAddress>[email protected]</EmailAddress>
  <Name>Mr Jones</Name>
  <ReadsEmailWith/>
  <State>Active</State>
</Subscriber>

XSLT:

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
            <METADATA>
                <FIELD NAME="ID" TYPE="TEXT"/>
                <FIELD NAME="Name" TYPE="TEXT"/>
                <FIELD NAME="Email" TYPE="TEXT"/>
                <FIELD NAME="Mr_Mrs" TYPE="TEXT"/>
            </METADATA>
            <RESULTSET>
                <xsl:for-each select="PagedResult/Results/Subscriber">
                    <ROW>
                        <COL>
                            <DATA>*This Is Where I'm Stuck*</DATA>
                        </COL>
                        <COL>
                            <DATA><xsl:value-of select="Name/text()"/></DATA>
                        </COL>
                        <COL>
                            <DATA><xsl:value-of select="EmailAddress/text()"/></DATA>
                        </COL>
                        <COL>
                            <DATA>*This Is Where I'm Stuck*</DATA>
                        </COL>
                    </ROW>
                </xsl:for-each>
            </RESULTSET>
        </FMPXMLRESULT>
    </xsl:template>
</xsl:stylesheet>

Desired Output:

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
   <METADATA>
      <FIELD TYPE="TEXT" NAME="ID" />
      <FIELD TYPE="TEXT" NAME="Name" />
      <FIELD TYPE="TEXT" NAME="Email" />
      <FIELD TYPE="TEXT" NAME="Mr_Mrs" />
   </METADATA>
   <RESULTSET>
      <ROW>
         <COL>
            <DATA>ABCEF1234</DATA>
         </COL>
         <COL>
            <DATA>Mrs Smith</DATA>
         </COL>
         <COL>
            <DATA>[email protected]</DATA>
         </COL>
         <COL>
            <DATA>Mrs_Email</DATA>
         </COL>
      </ROW>
      <ROW>
         <COL>
            <DATA>1234567890</DATA>
         </COL>
         <COL>
            <DATA>Mr Jones</DATA>
         </COL>
         <COL>
            <DATA>[email protected]</DATA>
         </COL>
         <COL>
            <DATA>Mr_Email</DATA>
         </COL>
      </ROW>
   </RESULTSET>
</FMPXMLRESULT>

Any help would be greatly appreciated.

Answer 1

I think I need to do something with the key function

Not really. Try:

<xsl:template match="/">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <METADATA>
            <FIELD NAME="ID" TYPE="TEXT"/>
            <FIELD NAME="Name" TYPE="TEXT"/>
            <FIELD NAME="Email" TYPE="TEXT"/>
            <FIELD NAME="Mr_Mrs" TYPE="TEXT"/>
        </METADATA>
        <RESULTSET>
            <xsl:for-each select="PagedResult/Results/Subscriber">
                <ROW>
                    <COL>
                        <DATA>
                            <xsl:value-of select="CustomFields/CustomField[Key='[ID]']/Value"/>
                        </DATA>
                    </COL>
                    <COL>
                        <DATA><xsl:value-of select="Name"/></DATA>
                    </COL>
                    <COL>
                        <DATA><xsl:value-of select="EmailAddress"/></DATA>
                    </COL>
                    <COL>
                        <DATA>
                            <xsl:value-of select="CustomFields/CustomField[Key='[Mr_or_Mrs]']/Value"/>
                        </DATA>
                    </COL>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>