Using XSLT to modify XML for Filemaker Pro data import

Here should go questions about transforming XML with XSLT and FOP.
pderby
Posts: 3
Joined: Tue Mar 22, 2005 12:04 am

Using XSLT to modify XML for Filemaker Pro data import

Post by pderby »

Hi,

I'm a newbie to XSLT and have been reading a lot, but most of the ideas are still pretty foreign at this stage. Here is my basic challenge:

I want to take an XML document provided by the US National Weather Service: http://weather.gov/data/current_obs/KDCA.xml

And apply an XSLT provided by FileMaker so that the resulting transformation meets the FileMaker Pro grammar requirements.

The XSLT is as follows:

Code: Select all

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
version="1.0">
<!--
File: msdso_elem.xslt

Transforms data in an ELEMENT based MSDSO grammar
into the FMPXMLRESULT grammar, suitable for import.

===============================================================

Copyright © 2002 FileMaker, Inc.
All rights reserved.



===============================================================
-->
<xsl:template match="/*">
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="" NAME="" VERSION=""/>
<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=""
RECORDS="{count(/*/*)}"
TIMEFORMAT="h:mm:ss a"/>
<METADATA>
<xsl:for-each select="/*/*[position()=1]/*">
<FIELD>
<xsl:attribute name="EMPTYOK">YES</xsl:attribute>
<xsl:attribute name="MAXREPEAT">1</xsl:attribute>
<xsl:attribute name="NAME">
<xsl:value-of select="name()"/>
</xsl:attribute>
<xsl:attribute name="TYPE">TEXT</xsl:attribute>
</FIELD>
</xsl:for-each>
</METADATA>
<RESULTSET>
<xsl:attribute name="FOUND">
<xsl:value-of select="count(child::*)"/>
</xsl:attribute>
<xsl:for-each select="child::*">
<ROW>
<xsl:attribute name="MODID">0</xsl:attribute>
<xsl:attribute name="RECORDID">0</xsl:attribute>
<xsl:for-each select="child::*">
<COL>
<DATA>
<xsl:value-of select="."/>
</DATA>
</COL>
</xsl:for-each>
</ROW>
</xsl:for-each>
</RESULTSET>
</FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>
If I go in and put a tag at the beginning and end of the .xml document Filemaker is happy. Without a tag Filemaker doesn't think there is a "record" to import. Is there a way in the XSLT to generate atag around the entire XML document body so FileMaker is happy?

Thanks for any help.

Paul[/code]
george
Site Admin
Posts: 2095
Joined: Thu Jan 09, 2003 2:58 pm

Post by george »

Hi Paul,

Probably all you need to change is the template/@match attribute value to be

<xsl:template match="/">

Best Regards,
George
pderby
Posts: 3
Joined: Tue Mar 22, 2005 12:04 am

Post by pderby »

Hello George,

Thanks for the suggestion. I did make that change but FileMaker Pro still isn't happy when doing the XML import. It seems that FileMaker Pro wants a "record" structure to import. Without having an additional tag set, like <root> and <\root>, at the beginning and end of the document, FileMaker throws an error complaining about not having enough data. If I hand edit in the tag pair, FMP is happy.

I can pull in the XML and edit the document and then write it to a file and then pull it back in with the XSLT, but I'd like to not have to do those text manipulation steps to make FMP happy.

So is there a way in XSLT to transform the XML to where FMP will have the "correct" nesting of tags to be happy?

Kind regards,

Paul
george
Site Admin
Posts: 2095
Joined: Thu Jan 09, 2003 2:58 pm

Post by george »

Hi Paul,

It is not clear for me where do you need this new element... Do you need to have the result of the stylesheet wrapped in the "root" element? Or do you need the http://weather.gov/data/current_obs/KDCA.xml to be wrapped in a "root" element?

Best Regards,
George
pderby
Posts: 3
Joined: Tue Mar 22, 2005 12:04 am

Post by pderby »

If I add the "root" tag pair so the XML loooks like this, Filemaker Pro is happy. So the question is, can XMLT be written to add this tag pair? Or is there another way without writing string manipulation code to modify the XML?

Code: Select all

<?xml version="1.0" encoding="utf-8"?>


<root>


<current_observation>
<credit>NOAA's National Weather Service</credit>
<credit_URL>http://weather.gov/</credit_URL>
<suggested_pickup>15 minutes after the hour</suggested_pickup>
<suggested_pickup_period>60</suggested_pickup_period>
<location>Washington DC, Reagan National Airport, VA</location>
<station_id>KDCA</station_id>
<latitude>38.50.54N</latitude>
<longitude>077.02.03W</longitude>
<elevation>NA</elevation>
<observation_time>Last Updated on Mar 20, 8:51 pm EST</observation_time>
<weather>Partly Cloudy</weather>
<temperature_string>46 F (8 C)</temperature_string>
<temp_f>46</temp_f>
<temp_c>8</temp_c>
<relative_humidity>58</relative_humidity>
<wind_string>From the Northwest at 9 MPH</wind_string>
<wind_dir>Northwest</wind_dir>
<wind_degrees>320</wind_degrees>
<wind_mph>9.2</wind_mph>
<wind_gust_mph>0</wind_gust_mph>
<pressure_string>30.03" (1016.8 mb)</pressure_string>
<pressure_mb>1016.8</pressure_mb>
<pressure_in>30.03</pressure_in>
<dewpoint_string>32 F (0 C)</dewpoint_string>
<dewpoint_f>32</dewpoint_f>
<dewpoint_c>0</dewpoint_c>
<heat_index_string>Not Applicable</heat_index_string>
<heat_index_f>Not Applicable</heat_index_f>
<heat_index_c>Not Applicable</heat_index_c>
<windchill_string>41 F (5 C)</windchill_string>
<windchill_f>41</windchill_f>
<windchill_c>5</windchill_c>
<visibility>10.00 mi.</visibility>
<two_day_history_url>http://www.weather.gov/data/obhistory/KDCA.html</two_day_history_url>
<ob_url>http://www.nws.noaa.gov/data/METAR/KDCA.1.txt</ob_url>
<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>
<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>


</root>
george
Site Admin
Posts: 2095
Joined: Thu Jan 09, 2003 2:58 pm

Post by george »

Hi Paul,

You can get that with another stylesheet, something like below:

Code: Select all


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="node() | @*">
<xsl:copy>
<xsl:apply-templates select="node() | @*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="/">
<root>
<xsl:apply-templates/>
</root>
</xsl:template>
</xsl:stylesheet>
Then you can apply your current stylesheet on the result.

BUT I think you need to change your current stylesheet to accept as input a document without the wrapper root element. For instance where you have RECORDS="{count(/*/*)}" you need to change it to RECORDS="{count(/*)}" and so on.


Another observation is that if I apply the stylesheet with the first change on the template match attribute then I get what it seems the correct result. If I wrap the document in a root tag I get something that I believe it is a wrong result, that is a single row with all the information inside a data element.

Hmm... looking at that as I type the response....

In one case you get the matadata right and in the other the data right but no metadata...

Try with this stylesheet and use the not wrapped input:

Code: Select all


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output indent="yes"/>
<!--
File: msdso_elem.xslt

Transforms data in an ELEMENT based MSDSO grammar
into the FMPXMLRESULT grammar, suitable for import.

===============================================================

Copyright © 2002 FileMaker, Inc.
All rights reserved.



===============================================================
-->
<xsl:template match="/">
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="" NAME="" VERSION=""/>
<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=""
RECORDS="{count(/*/*)}"
TIMEFORMAT="h:mm:ss a"/>
<METADATA>
<xsl:for-each select="/*[position()=1]/*">
<FIELD>
<xsl:attribute name="EMPTYOK">YES</xsl:attribute>
<xsl:attribute name="MAXREPEAT">1</xsl:attribute>
<xsl:attribute name="NAME">
<xsl:value-of select="name()"/>
</xsl:attribute>
<xsl:attribute name="TYPE">TEXT</xsl:attribute>
</FIELD>
</xsl:for-each>
</METADATA>
<RESULTSET>
<xsl:attribute name="FOUND">
<xsl:value-of select="count(child::*)"/>
</xsl:attribute>
<xsl:for-each select="child::*">
<ROW>
<xsl:attribute name="MODID">0</xsl:attribute>
<xsl:attribute name="RECORDID">0</xsl:attribute>
<xsl:for-each select="child::*">
<COL>
<DATA>
<xsl:value-of select="."/>
</DATA>
</COL>
</xsl:for-each>
</ROW>
</xsl:for-each>
</RESULTSET>
</FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>
Best Regards,
George
Post Reply