8.2 Transform FMPDSORESULT into FMPXMLRESULT

We'll use the FMPDSORESULT export along with an XSL stylesheet to transform into an FMPXMLRESULT document. These examples will work in small steps so that you understand how to build an XSL stylesheet.

8.21 Example 1: Find the Rows/Records and Display Some Text

Create a new text document and name it Transform1.xsl.

Add the prolog and the root element for all XSL stylesheets:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet>
</xsl:stylesheet>

The stylesheet element has several attributes that we need to include. The version and XSL namespace for XSL have required values.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
  Transform">
</xsl:stylesheet>

We'll add two more attributes. The first one is the namespace for the XML source document elements. The second attribute tells the XSL processor to not include this namespace with any elements we create in the resulting XML.

<?xml version="1.0" encoding="UTF-8" ?>
<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:stylesheet>

Add the top-level XSL output element and its attributes. For this example we want to show the result as text, so the method attribute has a value of "text". Later we'll show the result as XML. The output element shows us the version and encoding for the resulting document. The indent attribute probably should be "no" for most result documents. Any indentation in the stylesheet is added for readability in the code listings and should not be included when you create your stylesheets.

<?xml version="1.0" encoding="UTF-8" ?>
<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 version="1.0" encoding="UTF-8" indent="no"
        method="text" />
</xsl:stylesheet>

Now we need to do something with the XML elements in the source document, so we set up a template. The most common test is to find the root ("/") of the XML source document. From there, you can test for other elements as needed. We'll use the XSL element for-each to get every ROW element in the source document. We'll display the literal text "We found a row!" and a carriage return for every record in the source XML. If you would prefer to use a carriage return and a linefeed, add "
" after the "". The following code shows the full stylesheet for transform1.xsl. Save this stylesheet in a convenient location and use it with an XML export.

Listing 8.1: transform1.xsl

<?xml version="1.0" encoding="UTF-8" ?>
<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 version="1.0" encoding="UTF-8" indent="no"
        method="text" />
      <xsl:template match="/">
            <xsl:text>TRANSFORM1.TXT&#13;Find our
              rows.&#13;&#13;</xsl:text>
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW"><xsl:
              text>We found a row!&#13;</xsl:text></xsl:for-each>
      </xsl:template>
</xsl:stylesheet>

To create the XML export, choose File, Export Records and name the new file transform1.txt. Select FMPDSORESULT grammar and check Use XSL style sheet. Click the File radio button if it is not already selected. When you are prompted in the Open dialog to choose your stylesheet, navigate to where you saved the transform1.xsl file and click Open. The Specify XML and XSL Options dialog shows your stylesheet, so click the OK button.

You will be asked to select the fields for export. This is just a test of the stylesheet with the records, so only a few fields need to be used. Click the Export button and find the transform1.txt document you just created. When you open it in your text editor, you should see something like the listing below (two records in the found set).

TRANSFORM1.TXT
Find our rows.

We found a row!
We found a row!

8.22 Example 2: Display Something for the Fields

Here, we'll take the above stylesheet, name it transform2.xsl, and add another XSL element, <xsl:for-each>, to display text for the fields in the export. Just to make it easier to see what is happening, we'll use the name of the field elements as the text to display. Within the xsl:for-each loop for the rows/records, we'll add another xsl:for-each loop. The select attribute tells us to get any child element ("*") of the current path ("."). The XPath expression "name()" is a function that returns the name of each of these child elements.

Listing 8.2: transform2.xsl

<?xml version="1.0" encoding="UTF-8" ?>
<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 version="1.0" encoding="UTF-8" indent="no"
        method="text" />
      <xsl:template match="/">
            <xsl:text>TRANSFORM2.TXT&#13;Find our rows and show the
              fields.&#13;&#13;</xsl:text>
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW">
                  <xsl:text>We found a row!&#13;</xsl:text>
                  <xsl:for-each select="./*">
                        <xsl:value-of select="name()" />
                        <xsl:text>&#13;</xsl:text>
                  </xsl:for-each>
            </xsl:for-each>
      </xsl:template>
</xsl:stylesheet>

Perform the same export as in Example 1, but select this new stylesheet and name the resulting document transform2.txt. Listing 8.3 shows the result for two rows and five fields from the Export.fp5 database used in Chapter 2. You can view your results in a text editor.

Listing 8.3: transform2.txt

TRANSFORM2.TXT
Find our rows and show the fields.

We found a row!
First_Name
Last_Name
City
State
Number
Date
We found a row!
First_Name
Last_Name
City
State
Number
Date

8.23 Example 3: Return an XML Result and Display Elements Instead of Text

Save a copy of transform2.txt as transform3.txt and make the following changes:

method="xml"
Don't include a title to the document, or make it a comment.
Create a ROW element in the result XML that uses the MODID and RECORDID attributes from the source XML.
Display the contents of the fields inside the <COL><DATA> </DATA></COL> elements. The transform3.xsl stylesheet is shown in Listing 8.4 and the resulting transform3.xml is shown in Listing 8.5. Use the same export as in Examples 1 and 2, but use the new stylesheet. You may select different fields if you wish.

Listing 8.4: transform3.xsl

<?xml version='1.0' encoding='UTF-8' ?><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 version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match="/">
            <xsl:comment>TRANSFORM3.XML</xsl:comment>
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW">
                  <ROW><xsl:attribute name="MODID"><xsl:value-of
                    select="@MODID" /></xsl:attribute>
                    <xsl:attribute name="RECORDID"><xsl:value-of
                    select="@RECORDID" /></xsl:attribute>
                  <xsl:for-each select="./*">
                        <COL><DATA><xsl:value-of select="." /></DATA></COL>
                  </xsl:for-each>
                  </ROW>
            </xsl:for-each>
      </xsl:template>
</xsl:stylesheet>

Listing 8.5: transform3.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--TRANSFORM3.XML--><ROW MODID="3" RECORDID="5"><COL><DATA>Beverly</DATA>
</COL><COL><DATA>Voth</DATA></COL><COL><DATA>KY</DATA></COL><COL><DATA>1.00
</DATA></COL></ROW><ROW MODID="4" RECORDID="6"><COL><DATA>Doug</DATA></COL>
<COL><DATA>Rowe</DATA></COL><COL><DATA>FL</DATA></COL><COL><DATA>2.00
</DATA></COL></ROW>

8.24 Example 4: Transformation from FMPDSORESULT to FMPXMLRESULT Without the Fields

All that is left for us to add to the stylesheet is the other elements in the FMPXMLRESULT grammar. First we will add everything but the METADATA and FIELD elements. Example 5 will demonstrate how to get the field names from the source XML. Listing 8.6 shows the transform4.xsl stylesheet and Listing 8.7 shows the result, transform4.xml.

Listing 8.6: transform4.xsl

<?xml version='1.0' encoding='UTF-8' ?><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 version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match="/">
      <FMPXMLRESULT xmlns="http://www.filemaker.com/
        fmpxmlresult"><ERRORCODE>0</ERRORCODE><PRODUCT
        BUILD="11/13/2002" NAME="FileMaker Pro"
        VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT=""
        NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a"/>
      <METADATA />
      <RESULTSET FOUND="">
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW">
                  <ROW><xsl:attribute name="MODID"><xsl:value-of
                    select="@MODID" /></xsl:attribute><xsl:
                    attribute name="RECORDID"><xsl:value-of
                    select="@RECORDID" /></xsl:attribute>
                  <xsl:for-each select="./*">
                        <COL><DATA><xsl:value-of select="."
                          /></DATA></COL>
                  </xsl:for-each>
                  </ROW>
            </xsl:for-each>
      </RESULTSET>
      </FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>

Listing 8.7: transform4.xml

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <ERRORCODE>0</ERRORCODE>
      <PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro" VERSION=
        "6.0v4" />
      <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS=""
        TIMEFORMAT="h:mm:ss a" />
      <METADATA />
      <RESULTSET FOUND="">
            <ROW MODID="3" RECORDID="5">
                  <COL><DATA>Beverly</DATA></COL>
                  <COL><DATA>Voth</DATA></COL>
                  <COL><DATA>KY</DATA></COL>
                  <COL><DATA>1.00</DATA></COL>
            </ROW>
            <ROW MODID="3" RECORDID="6">
                  <COL><DATA>Doug</DATA></COL>
                  <COL><DATA>Rowe</DATA></COL>
                  <COL><DATA>FL</DATA></COL>
                  <COL><DATA>2.00</DATA></COL>
            </ROW>
      </RESULTSET>
</FMPXMLRESULT>

Duplicate or save the stylesheet from Example 3 and name it transform4.xsl. The easiest way to add the necessary elements is to export with the FMPXMLRESULT grammar and copy parts of the resulting XML. The name of the DATABASE, the name of the LAYOUT, and the number of RECORDS can be empty, so you can use an XML export from any FileMaker Pro database. Add an empty METADATA element and the start tag for the RESULTSET element. The FOUND value may also be empty. Don't forget to close the RESULTSET and FMPXMLRESULT elements.

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002"
  NAME="FileMaker Pro"VERSION="6.0v4"/><DATABASE DATEFORMAT=
  "M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=
  "h:mm:ss a"/>
<METADATA />
<RESULTSET FOUND="">
…
</RESULTSET>
</FMPXMLRESULT>

Try to import the transform4.xml you just created in Listing 8.7. You should be able to see the Import Field Mapping dialog, but there will be no fields listed on the left side! Cancel the process and proceed to Example 5 to see how we can extract the names of the fields and put them in the FIELD elements of the METADATA element.

8.25 Example 5: Get the Field Names for the Transformation

For this example, we will create the FIELD elements with <xsl:element>, extract the field names from the first record/row, and add them as attributes to the elements. Listing 8.8 shows the snippet to replace the <METADATA /> in Example 4. You can compare the following listing with Listing 8.2, "transform2.xsl," where we just got the field names.

Listing 8.8: Create the FIELD elements

<METADATA>
      <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW[1]/*">
            <xsl:element name="FIELD"><xsl:attribute
              name="NAME"><xsl:value-of select="name()"
              /></xsl:attribute></xsl:element>
      </xsl:for-each>
</METADATA>

Listing 8.9: transform5a.xsl

<?xml version='1.0' encoding='UTF-8' ?><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 version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match="/">
      <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002"
        NAME="FileMaker Pro" VERSION="6.0v4"/><DATABASE
        DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS=""
        TIMEFORMAT="h:mm:ss a"/>
      <METADATA>
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW[1]/*">
                  <xsl:element name="FIELD"><xsl:attribute
                    name="NAME"><xsl:value-of select="name()"
                    /></xsl:attribute></xsl:element>
            </xsl:for-each>
      </METADATA>
      <RESULTSET FOUND="">
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW">
                  <ROW><xsl:attribute name="MODID"><xsl:value-of
                     select="@MODID" /></xsl:attribute>
                    <xsl:attribute name="RECORDID"><xsl:value-of
                     select="@RECORDID" /></xsl:attribute>
                  <xsl:for-each select="./*">
                        <COL><DATA><xsl:value-of select="."
                          /></DATA></COL>
                  </xsl:for-each>
                  </ROW>
            </xsl:for-each>
      </RESULTSET>
      </FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>

Listing 8.10: transform5a.xml

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro"
VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=""
RECORDS="" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD
NAME="First_Name"/><FIELD NAME="Last_Name"/><FIELD
NAME="State"/><FIELD NAME="Number"/></METADATA><RESULTSET FOUND=""><ROW MODID="3"
RECORDID="5"><COL><DATA>Beverly</DATA></COL><COL><DATA>Voth</DATA>
</COL><COL><DATA>KY</DATA></COL><COL><DATA>1.00</DATA></COL></ROW>
<ROW MODID="4" RECORDID="6"><COL><DATA>Doug</DATA></COL><COL><DATA>
Rowe</DATA></COL><COL><DATA>FL</DATA></COL><COL><DATA>2.00</DATA>
</COL></ROW></RESULTSET></FMPXMLRESULT>

You will get an error if you try to import transform5a.xml, shown above. The EMPTYOK, MAXREPEAT, and TYPE attributes are required for import. We will create an attribute set for use with the FIELD element, although we could have entered the required attributes directly in the template. transform5b.xsl in Listing 8.11 shows this addition to the stylesheet and Listing 8.12 shows the resulting XML.

Notice how all of the fields will have a TYPE of TEXT. If you already have the fields created, the import should be fine. If you are using this method to create a database from XML, the field type will also be TEXT.

<xsl:attribute-set name="fieldStuff">
      <xsl:attribute name="EMPTYOK">YES</xsl:attribute>
      <xsl:attribute name="MAXREPEAT">1</xsl:attribute>
      <xsl:attribute name="TYPE">TEXT</xsl:attribute>
</xsl:attribute-set>

Listing 8.11: transform5b.xsl

<?xml version='1.0' encoding='UTF-8' ?><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 version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:attribute-set name="fieldStuff">
            <xsl:attribute name="EMPTYOK">YES</xsl:attribute>
            <xsl:attribute name="MAXREPEAT">1</xsl:attribute>
            <xsl:attribute name="TYPE">TEXT</xsl:attribute>
      </xsl:attribute-set>
      <xsl:template match="/">
      <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002"
        NAME="FileMaker Pro" VERSION="6.0v4"/><DATABASE
        DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS=""
        TIMEFORMAT="h:mm:ss a"/>
      <METADATA>
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW[1]/*">
                  <xsl:element name="FIELD" use-attribute-sets=
                    "fieldStuff"><xsl:attribute name="NAME"><xsl:
                    value-of select="name()" /></xsl:attribute>
                  </xsl:element>
            </xsl:for-each>
      </METADATA>
      <RESULTSET FOUND="">
            <xsl:for-each select="./fm:FMPDSORESULT/fm:ROW">
                  <ROW><xsl:attribute name="MODID"><xsl:value-of
                    select="@MODID" /></xsl:attribute><xsl:
                    attribute name="RECORDID"><xsl:value-of
                    select="@RECORDID" /></xsl:attribute>
                  <xsl:for-each select="./*">
                        <COL><DATA><xsl:value-of select="." /></DATA></COL>
                  </xsl:for-each>
                  </ROW>
            </xsl:for-each>
      </RESULTSET>
      </FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>

Listing 8.12: transform5b.xml

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro"
VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=""
RECORDS="" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD EMPTYOK="YES"
MAXREPEAT="1" TYPE="TEXT" NAME="First_Name"/><FIELD EMPTYOK="YES"
MAXREPEAT="1" TYPE="TEXT" NAME="Last_Name"/><FIELD EMPTYOK="YES"
MAXREPEAT="1" TYPE="TEXT" NAME="State"/><FIELD EMPTYOK="YES"
MAXREPEAT="1" TYPE="TEXT" NAME="Number"/></METADATA><RESULTSET
FOUND=""><ROW MODID="3" RECORDID="5"><COL><DATA>Beverly</DATA></COL>
<COL><DATA>Voth</DATA></COL><COL><DATA>KY</DATA></COL><COL><DATA>
1.00</DATA></COL></ROW><ROW MODID="4" RECORDID="6"><COL><DATA>Doug
</DATA></COL><COL><DATA>Rowe</DATA></COL><COL><DATA>FL</DATA>
</COL><COL><DATA>2.00</DATA></COL></ROW></RESULTSET></FMPXMLRESULT>