XSLT question. How to pair field tags with data when original XML has them in separate sections?

Question

I hope I don't lose anyone by mentioning Filemaker. I am trying to turn it's XML export into something usable by SSIS. FM's native XML export has field names and data in separate sections of the same XML file. This lists what I need it to do, what I currently did, and the original FM export at the bottom for reference. I have not seen XML translations before this morning, so bear with me :D. I can post more info as necessary.

<!-- What we actually want example -->
<?xml version="1.0" encoding="UTF-8"?>

<PRODUCTRECS>
<PRODUCT>
    <name>Dr. Zim</name>
    <address>1234 Internet Way</address>
    <city/><state/><zip/>
</PRODUCT>
...
</PRODUCTRECS>

Is there a way with XSLt to read the field names at the top and put the field names around the actual data when we translate it? Currently, I just pick out the position with IF statements like so (which works but is very dependent and messy):

<!-- Current nightmare code, check for each individually and print it out -->
<xsl:template match="fmp:FMPXMLRESULT">
<PRODUCTRECS>
<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
    <PRODUCT>
<xsl:for-each select="fmp:COL">
<xsl:if test="position()=1">
    <name><xsl:value-of select="fmp:DATA"/></name>
</xsl:if>
...
</xsl:for-each>
    </PRODUCT>
</xsl:for-each>
</PRODUCTRECS>
</xsl:template>

This is what Filemaker outputs by default:

<?xml version="1.0" encoding="UTF-8" ?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"&gt;
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="01-01-2009" NAME="FileMaker Pro" VERSION="10.0v3"/>
<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="filename.fp7" RECORDS="10" TIMEFORMAT="h:mm:ss a"/>
<METADATA>
  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="name" TYPE="TEXT"/>
  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="address" TYPE="TEXT"/>
  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="city" TYPE="TEXT"/>
  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="state" TYPE="TEXT"/>
  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="zip" TYPE="TEXT"/>
</METADATA>
<RESULTSET FOUND="10">
<ROW MODID="0" RECORDID="1">
  <COL><DATA>Dr. Zim</DATA></COL>
  <COL><DATA>1234 Internet Way</DATA></COL>
  <COL><DATA></DATA></COL>
  <COL><DATA></DATA></COL>
  <COL><DATA></DATA></COL>
  ...
</ROW>
...
</RESULTSET>
</FMPXMLRESULT>

Looking forward to the guru XSLTers out there. :) Another question I had was how to format a numeric price when the original is stored as Text (00009.99000000) in the XML in to a currency of $9.99, but I can work on this one.

Answer 1

You can jump back out to the root starting the xpath query with / i.e /fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
     xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
    >
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/fmp:FMPXMLRESULT">
     <PRODUCTRECS>
      <xsl:apply-templates select="fmp:RESULTSET/fmp:ROW" />
     </PRODUCTRECS>
    </xsl:template>

    <xsl:template match="fmp:RESULTSET/fmp:ROW">
     <PRODUCT>
      <xsl:apply-templates select="fmp:COL" />
     </PRODUCT>
    </xsl:template>

    <xsl:template match="fmp:COL">
     <xsl:variable name="currentPosition" select="position()" />
     <xsl:element name="{/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[position() = $currentPosition]/@NAME}">
      <xsl:value-of select="fmp:DATA"/>
     </xsl:element>
    </xsl:template>


</xsl:stylesheet>

Answer 2

This should get you started:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:fm="http://www.filemaker.com/fmpxmlresult"&gt;
    <xsl:template match="/fm:FMPXMLRESULT">
     <PRODUCTRECS>
      <xsl:apply-templates select="fm:RESULTSET/fm:ROW"/>
     </PRODUCTRECS>
    </xsl:template>

    <xsl:template match="fm:ROW">
     <PRODUCT>
     <!--
      Use this if the element containing the NAME="FileMaker Pro" attribute is the one you want to use
      for each row name.
      <xsl:element name="{name(/fm:FMPXMLRESULT/*[@NAME='FileMaker Pro'])}">-->
      <xsl:for-each select="fm:COL/fm:DATA">
       <xsl:variable name="currentPos" select="position()"/>
       <xsl:element name="{/fm:FMPXMLRESULT/fm:METADATA/fm:FIELD[position()=$currentPos]/@NAME}">
        <xsl:value-of select="."/>
       </xsl:element>
      </xsl:for-each>
     <!--</xsl:element>-->
     </PRODUCT>
    </xsl:template>
</xsl:stylesheet>

Also, take a look at xsl:number for the other part or possibly just the number() function.

Answer 3

One elegant way to solve this problem is this:

<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"
>

  <!-- the key indexes the METADATA fields by their position -->
  <xsl:key 
    name="kMetaData" 
    match="fmp:METADATA/fmp:FIELD" 
    use="count(preceding-sibling::fmp:FIELD) + 1" 
  />

  <!-- separate templates increase readability -->
  <xsl:template match="/fmp:FMPXMLRESULT">
    <PRODUCTRECS>
      <xsl:apply-templates select="fmp:RESULTSET/fmp:ROW" />
    </PRODUCTRECS>
  </xsl:template>

  <xsl:template match="fmp:ROW">
    <PRODUCT>
      <xsl:apply-templates select="fmp:COL" />
    </PRODUCT>
  </xsl:template>

  <xsl:template match="fmp:COL">
    <!-- column name lookup is high-speed because of the key -->
    <xsl:element name="{string(key('kMetaData', position())/@NAME)}">
      <xsl:value-of select="fmp:DATA" />
    </xsl:element>
  </xsl:template>

</xsl:stylesheet>

Which outputs on my system:

<PRODUCTRECS>
  <PRODUCT>
    <name>Dr. Zim</name>
    <address>1234 Internet Way</address>
    <city></city>
    <state></state>
    <zip></zip>
  </PRODUCT>
</PRODUCTRECS>

However, be warned that XML element names are subject to stricter rules than FileMaker column names. The above will crash and burn if your column names violate those rules.

The notable features of the stylesheet are:

• an <xsl:key> for speedy lookup of nodes - this should become noticeable for larger inputs
• exclude-result-prefixes to prevent declaration of the fmp namespace in the result
• <xsl:element> to create elements with a dynamic name
• the use of the preceding-sibling XPath axis as a way of determining node position (because the position() function does not work in <xsl:key>s

Go ahead and ask if anything is unclear.

Your other problem (number formatting) has an answer here: XSL: Formatting numbers, excluding trailing zeroes.