Search code examples
xmlxsltfilemaker

Filemaker 13 export formatting with XSLT

I'm exporting my DB from Filemaker Pro 13 to XML in FMPDSORESULT Grammar and try to create an XSLT file to format XML export to a specific structure I need. I've already did it for simple initial one-string parameters for a record. But now I have slightly different data.

Here's full one record sample in FMPDSORESULT Grammar, its part with parameters:

<?xml version="1.0" encoding="UTF-8" ?><!-- This grammar has been deprecated - use FMPXMLRESULT instead -->
<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">
    <ERRORCODE>0</ERRORCODE>
    <DATABASE></DATABASE>
    <LAYOUT></LAYOUT>
    <ROW MODID="178" RECORDID="6">
        <ID>535</ID>
        <Design_Name_Original>G001_Folder</Design_Name_Original>
        <Material_Type>Folding Carton</Material_Type>
        <Usage>Packaging</Usage>
        <Design_Group_Original>Folders (Group G)</Design_Group_Original>
        <Display_Order>
            <DATA>2</DATA>
            <DATA>3</DATA>
            <DATA>4</DATA>
            <DATA>5</DATA>
        </Display_Order>
        <Parameter_Name>
            <DATA>Allowance</DATA>
            <DATA>Length</DATA>
            <DATA>Width</DATA>
            <DATA>Depth</DATA>
        </Parameter_Name>
        <Parameter_Datatype>
            <DATA>Numeric Material Thickness</DATA>
            <DATA>Numeric Distance - LWD</DATA>
            <DATA>Numeric Distance - LWD</DATA>
            <DATA>Numeric Distance - LWD</DATA>
        </Parameter_Datatype>
        <Default_Inches>
            <DATA>1/8</DATA>
            <DATA>7</DATA><DATA>7</DATA>
            <DATA>2</DATA>
        </Default_Inches>
        <Default_Metric>
            <DATA>3</DATA>
            <DATA>175</DATA>
            <DATA>175</DATA>
            <DATA>50</DATA>
        </Default_Metric>
        <Min_for_Inch>
            <DATA>1/64</DATA>
            <DATA>1/32</DATA>
            <DATA>1/32</DATA>
            <DATA>1/32</DATA>
        </Min_for_Inch>
        <Min_for_mm>
            <DATA>,5</DATA>
            <DATA>1</DATA>
            <DATA>1</DATA>
            <DATA>1</DATA>
        </Min_for_mm>
        <Max_for_Inch>
            <DATA>3/4</DATA>
            <DATA>200</DATA>
            <DATA>200</DATA>
            <DATA>200</DATA>
        </Max_for_Inch>
        <Max_for_mm>
            <DATA>15</DATA>
            <DATA>5000</DATA>
            <DATA>5000</DATA>
            <DATA>5000</DATA>
        </Max_for_mm>
    </ROW>
</FMPDSORESULT>

I need get this structure:

<Designs>
    <Design>
    <ID>535</ID>
    <Name>G001_Folder</Name>
    <Category>Folders (Group G)</Category>
    <Parameters>
        <Parameter>
            <ParamID>2</ParamID>
            <ParamName>Allowance</ParamName>
            <ParamDatatype>Numeric Material Thickness</ParamDatatype>
            <DefaultInches>1/8</DefaultInches>
            <DefaultMM>3</DefaultMM>
            <MinInches>1/64</MinInches>
            <MinMM>,5</MinMM>
            <MaxInches>3/4</MaxInches>
            <MaxMM>15</MaxMM>
        </Parameter>
        <Parameter>
            <ParamID>3</ParamID>
            <ParamName>Length</ParamName>
            <ParamDatatype>Numeric Distance - LWD</ParamDatatype>
            <DefaultInches>7</DefaultInches>
            <DefaultMM>175</DefaultMM>
            <MinInches>1/32</MinInches>
            <MinMM>1</MinMM>
            <MaxInches>200</MaxInches>
            <MaxMM>5000</MaxMM>
        </Parameter>
        <Parameter>
            <ParamID>4</ParamID>
            <ParamName>Width</ParamName>
            <ParamDatatype>Numeric Distance - LWD</ParamDatatype>
            <DefaultInches>7</DefaultInches>
            <DefaultMM>175</DefaultMM>
            <MinInches>1/32</MinInches>
            <MinMM>1</MinMM>
            <MaxInches>200</MaxInches>
            <MaxMM>5000</MaxMM>
        </Parameter>
        <Parameter>
            <ParamID>5</ParamID>
            <ParamName>Depth</ParamName>
            <ParamDatatype>Numeric Distance - LWD</ParamDatatype>
            <DefaultInches>2</DefaultInches>
            <DefaultMM>50</DefaultMM>
            <MinInches>1/32</MinInches>
            <MinMM>1</MinMM>
            <MaxInches>200</MaxInches>
            <MaxMM>5000</MaxMM>
        </Parameter>
    </Parameters>
</Design>
</Designs>

I've already written this code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:fm="http://www.filemaker.com/fmpdsoresult" exclude-result-prefixes="fm">
  <xsl:output method="xml" indent="yes"/>

  <xsl:variable name="rename">
    <item from="ID" to="ID" />
    <item from="Design_Name_Original" to="Name" />
    <item from="Material_Type" to="Material" />
    <item from="Usage" to="Usage" />
    <item from="Design_Group_Original" to="Category" />
    <item from="Created" to="Created" />
    <item from="Modified" to="Modified" />
    <item from="Display_Order" to="ParamID" />
    <item from="Parameter_Name" to="ParamName" />
    <item from="Parameter_Datatype" to="ParamDatatype" />
    <item from="Default_Inches" to="DefaultInches" />
    <item from="Default_Metric" to="DefaultMM" />
    <item from="Min_for_Inch" to="MinInches" />
    <item from="Min_for_mm" to="MinMM" />
    <item from="Max_for_Inch" to="MaxInches" />
    <item from="Max_for_mm" to="MaxMM" />
  </xsl:variable>

  <xsl:template match="/*">
    <Designs>
      <xsl:apply-templates select="fm:ROW"/>
    </Designs>
  </xsl:template>

  <xsl:template match="fm:ROW">
    <Design>
        <xsl:apply-templates 
         select="fm:ID | fm:Design_Name_Original | fm:Material_Type | fm:Usage | fm:Design_Group_Original | fm:Created | fm:Modified" mode="rename" />
        <Parameters>
          <Parameter>
            <xsl:apply-templates 
            select="fm:Display_Order" mode="rename" />
          </Parameter>   
        </Parameters>
    </Design>
  </xsl:template>

  <xsl:template match="*" mode="rename">
    <xsl:element name="{document('')//xsl:variable[@name = 'rename']/item[@from = local-name(current())]/@to}">
      <xsl:value-of select="."/>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>

And have got this result:

<?xml version="1.0" encoding="UTF-8"?>
<Designs>
<Design>
<ID>535</ID>
<Name>G001_Folder</Name>
<Material>Folding Carton</Material>
<Usage>Packaging</Usage>
<Category>Folders (Group G)</Category>
<Created>04.09.2014 10:20:30</Created>
<Modified>04.09.2014 16:58:16</Modified>
<Parameters>
<Parameter>
<ParamID>2345</ParamID>
<ParamName>AllowanceLengthWidthDepth</ParamName>
<ParamDatatype>Numeric Material ThicknessNumeric Distance - LWDNumeric Distance - LWDNumeric Distance - LWD</ParamDatatype>
<DefaultInches>1/8772</DefaultInches>
<DefaultMM>317517550</DefaultMM>
<MinInches>1/641/321/321/32</MinInches>
<MinMM>,5111</MinMM>
<MaxInches>3/4200200200</MaxInches>
<MaxMM>15500050005000</MaxMM>
</Parameter>
</Parameters>
<Prices>
<Currency/>
<Type/>
<Price/>
</Prices>
</Design>
</Designs>

How can I spread those parameters to get my structure? Thanks.

UPD. I've added full versions of codes.

Solution

  • I believe you are making this (and many other things) more difficult by using repeating fields (if that's what's causing the double DATA elements). Note also that using DSO grammar will bite you if you ever decide to rename a field.

    That said, try the following stylesheet:

    <xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:dso="http://www.filemaker.com/fmpdsoresult"
exclude-result-prefixes="dso">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">
    <Designs>
        <xsl:for-each select="dso:FMPDSORESULT/dso:ROW">
            <Design>
                <ID><xsl:value-of select="dso:ID" /></ID>
                <Name><xsl:value-of select="dso:Design_Name_Original" /></Name>
                <Category><xsl:value-of select="dso:Design_Group_Original" /></Category>
                <Parameters>
                    <xsl:for-each select="dso:Display_Order/dso:DATA">
                        <xsl:variable name="i" select="position()" />
                        <Parameter>
                            <ParamID><xsl:value-of select="." /></ParamID>

                            <ParamName><xsl:value-of select="../../dso:Parameter_Name/dso:DATA[$i]" /></ParamName>
                            <ParamDatatype><xsl:value-of select="../../dso:Parameter_Datatype/dso:DATA[$i]" /></ParamDatatype>
                            <!-- more of the same here -->

                        </Parameter>
                    </xsl:for-each>
                </Parameters>
            </Design>
        </xsl:for-each>
    </Designs>
</xsl:template>

</xsl:stylesheet>

    Note that your source has only one record, so some guessing is included here regarding the correct breakup.