XML and Style Sheet

"... you mean these fans?" · May 3, 2012

Hi,

I am trying to understand how filemaker wants the data from an xml, however all the examples; Filemaker site, w3school only confuses me more.

Here is the XML that is only visible in the view source from my safari pull down menu:

http://www.RealDropShippers.com/Forex/USD.xml

All I am wanting is to extract the date, currency code, name, and rate for the United Arab Emirates Dirham to filemaker $variables or global fields on my preference table the layout is name preference as well if the layout name is important.

For the life of me I can not figure out how the Style sheet is suppose to be written. And again, I am trying to follow the instruction via the w3school what filemaker is say but w3school pushes there results into an html page with all the appropriate tags so I am complete lost.

I can not find any simple examples how Filemaker builds their style sheet. All I get are complicated examples for me that is, where you would require years of experience just to follow along.

Any help, reference point ( simple examples with just the basic ) or if you are hirable :-) to hold my hand and teach me the basic ?? :-) I would be grateful.

Thank you.

Tom :-)

comment · May 3, 2012

All I am wanting is to extract the date, currency code, name, and rate for the United Arab Emirates Dirham to filemaker $variables or global fields on my preference table the layout is name preference as well if the layout name is important.

That's not how it works. The XML source needs to be imported. The sylesheet will merely transform it into a format that Filemaker knows how to import. That is the same format you would get if you exported the data out of Filemaker as XML, using the FMPXML grammar and no stylesheet.

In your case, the stylesheet can be probably as simple as:

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>



<xsl:template match="/">



<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="" NAME="" VERSION=""/>

<DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>



<METADATA>

<FIELD NAME="Date" TYPE="DATE" EMPTYOK="YES" MAXREPEAT=""/>

<FIELD NAME="Rate" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>

</METADATA>



<RESULTSET FOUND="">



<xsl:for-each select="Forex">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="@time"/></DATA></COL>

<COL><DATA><xsl:value-of select="Currency[@code='AED']/@rate"/></DATA></COL>

</ROW>

</xsl:for-each>



</RESULTSET>

</FMPXMLRESULT>



</xsl:template>

</xsl:stylesheet>

Note that the "date" in the original is in YYYY-MM-DD format. You cannot import it directly into a Date field unless your file is set to use the same date format.

Lee Smith · May 3, 2012

Hi Tom,

I am trying to understand how filemaker wants the data from an xml,

Please update your profile to reflect your current FileMaker version, platform and operating system. you can do that by clicking on this link. http://fmforums.com/forum/index.php?app=core&module=usercp&tab=core&area=profileinfo

Lee

"... you mean these fans?" · May 3, 2012

Hmm ...

Is there a book / pdf that explains each one of your steps you typed in a layman term w3school is ok to a point. Yes it looks simple but since this will be my first style sheet, I need what w3school does for the xml style sheet where they push the data to html tags. Step by step they explain it, but where can I find a step by step guide that explains getting information into Filemaker in the why you wrote, what you wrote, and the way you wrote it ?? Hehehehe :-)

Anyway, Filemaker examples are to complicated for me, at this moment. Sorry Filemaker.

Any reference material that would guide someone wanting to write style sheets, that is the Filemaker part, would be grateful.

Thank you again :-)

Tom

comment · May 4, 2012

The one thing I can suggest is this: export an XML document out of Filemaker and study its structure. In this case (using only two fields) it would look something like this (after some prettifying):

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



<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="10-14-2010" NAME="FileMaker" VERSION="ProAdvanced 11.0v2"/>

<DATABASE DATEFORMAT="Yyyy-m-d" LAYOUT="" NAME="Yourfile.fp7" RECORDS="1" TIMEFORMAT="k:mm:ss "/>



<METADATA>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Date" TYPE="DATE"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Rate" TYPE="NUMBER"/>

</METADATA>



<RESULTSET FOUND="1">



<ROW MODID="0" RECORDID="1">

<COL><DATA>2012-05-03</DATA></COL>

<COL><DATA>3.6734</DATA></COL>

</ROW>



</RESULTSET>

</FMPXMLRESULT>

If you compare this to the stylesheet, you'll see that it aims to return a document similar to the one above.

Note that the FMPXMLRESULT format separates between the fields (in the METADATA section) and their contents (in the RESULTSET section).

"... you mean these fans?" · May 4, 2012

Thank you.

Exporting was a good idea, I also noted your date issue. Hmm ... since the style sheet can manipulate XML how do I write in my style sheet to change the xml source date yyyy-mm-dd, to: mm-dd-yyyy. Since the style sheet can change the xml I should be able to change the date to what Filemaker wants; YES / NO

I went ahead and imported anyway, but got a parsing error. Can you point me to a source to show me how to change the source XML date and where to enter it in the xml style sheet.

When I exported, this was set to "1": <RESULTSET FOUND="1"> But since I'm importing, I entered nothing: <RESULTSET FOUND="">

Here is what I entered with my current database info from the export you suggested :-)

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

</METADATA>

<xsl:for-each select="Forex">

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

comment · May 4, 2012

I went ahead and imported anyway, but got a parsing error.

That would indicate some problem other than the date format. Importing the wrong date format will merely insert invalid date values into the target field.

To change the date format, the stylesheet needs to use string functions to parse out the date elements and put them back together in the "correct" order.

When I exported, this was set to "1": <RESULTSET FOUND="1"> But since I'm importing, I entered nothing:

A lot of these attributes are ignored when importing; see the example in post #2.

BTW, I am not sure why you need to import the currency code - which will be always "AED" - or the currency name.

"... you mean these fans?" · May 5, 2012

I been looking at the FMPXMLRESULT grammar table and notices some fields are not required / ignored.

The "AED" and "NAME" is more for customer convenience when they navigation to the Preference page. My customer will be in other areas of this region and are planning on multiple currency rates depending or region and customer preferences with billing. Usually, the currency name is known but not the code. And I am planning of giving them a robust layout with importing options base on available data. This is a practice run to get me into XML :-)

Currently, yes you are correct! There is only one currency rate he is using and the Name and the Code would not be required.

I want to be ready :-)

I also found the function at w3school web site you spoke of on how to manipulate XML so ... back to the books.

Thank you for your assistance here, I am grateful :-)

Tom

comment · May 5, 2012

Usually, the currency name is known but not the code.

Ideally you would have a separate table of Currencies, with fields for Code (unique) and Name (and perhaps Country). This way, the table of Rates (i.e. the imported data) needs only the currency code - the rest would be fetched from the related record in the Currencies table.

"... you mean these fans?" · May 5, 2012

The is my idea with the currency table, however the rate changes daily, and I was going to provide a what for my customer to select only the currencies they are working with from a list generated by the XML. After they selected the Currencies, I would import with a different xml, anyway ...

I looked at what the import window was asking for and what I did was select Http for the Style sheet source then pasted all the XML inside the window, Oops! That explains the parsing error.

So I clicked on File instead and selected the XML file on my desktop that I've been working on and I was able to get the import fields selection box window :-) However, what was imported when I selected Match Names then imported, all three fields; Code, Name, Rate all received "AED" Hmm.... rather then their individual data.

Also, I had to select the style sheet to be use from my desk top. Where can I put this document so my customer does not have to worry about what file to select. And where I can script automatically to go get it not mater what computer this datafile is on ??

I'm guessing the issue is here:

Let me see what w3shool has to say here.

Here is the XML again, any suggestions ?? I would be grateful.

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

</METADATA>

<xsl:for-each select="Forex">

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

Also, I had to select the style sheet to be use from my desk top. Where can I put this document so my customer does not have to worry about what file to select.

Thank you again.

Tom :-)

comment · May 5, 2012

The is my idea with the currency table, however the rate changes daily,

That's exactly the point: the rates change daily, the names do not. So the names can be stored once in the Currencies table.

If your customers work with different currencies, then either each customer needs their own stylesheet or let them import the rates of all the currencies, then work with only the ones they need.

You can host the stylesheet anywhere it can be retrieved using a HTTP request. Or you can place it in a known location on the user's system (e.g. in the same folder as the solution file). Keep in mind that it can be stored in a container field and script-exported from there.

"... you mean these fans?" · May 5, 2012

Thank you.

All 3 import fields on my layout where the same fields, Oops! What a dope! Thank you. And I keep forgetting about those container fields, thank you again. And thank you for your XML help. All that is left is to format the date and I'll have some fun on the w3schools website figuring that out!

Again, thank you.

dysong3 · July 28, 2012

If I may pursue a similar line of interest here, I am trying to set up a style sheet for a regular import of XML data. I have gotten to the point where the import functions, but not quite well enough. Here is the style sheet as it stands for the moment.


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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="" NAME="" VERSION=""/>

<DATABASE DATEFORMAT="D/m/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>



<METADATA>

<FIELD EMPTYOK="NO" MAXREPEAT="" NAME="_kp_adresses_id" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="prenom" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="nom" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="email::email" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="adresse" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="nlp" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="ville" TYPE="NUMBER"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="téléphone::no_de_telephone" TYPE="TEXT"/>

</METADATA>

<RESULTSET FOUND="">



<ROW MODID="" RECORDID="">

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

<COL><DATA></DATA></COL>

</ROW>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

The problems I have not yet resolved are that this only create one record whereas the XML has many. And I can't figure out how you reference the data that should be inserted in between <COL><DATA> and </DATA></COL>

Obviously I don't know much about coding so please excuse my ignorance.

comment · July 28, 2012

this only create one record

If you glance above at post #2, you'll see that the entire ROW element is enclosed within a xsl:for-each element.

how you reference the data that should be inserted

That's difficult to answer without seeing the source XML document. In general, you use the XPath language to point at the data you want - see, for example:

http://www.w3schools.com/xpath/default.asp

dysong3 · July 29, 2012

Thanks for that. I know I'm cutting corners but I can't really afford the time to learn all the syntax to resolve this issue. Would you have any idea why the following isn't working?


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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="" NAME="" VERSION=""/>

<DATABASE DATEFORMAT="D/m/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>



<METADATA>

<FIELD EMPTYOK="NO" MAXREPEAT="" NAME="_kp_adresses_id" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="prenom" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="nom" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="email::email" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="adresse" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="nlp" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="ville" TYPE="NUMBER"/>

<FIELD EMPTYOK="YES" MAXREPEAT="" NAME="téléphone::no_de_telephone" TYPE="TEXT"/>

</METADATA>

<RESULTSET FOUND="100">

<xsl:for-each select="clients">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="id"/></DATA></COL>

<COL><DATA><xsl:value-of select="name"/></DATA></COL>

<COL><DATA><xsl:value-of select="lastname"/></DATA></COL>

<COL><DATA><xsl:value-of select="email"/></DATA></COL>

<COL><DATA><xsl:value-of select="address"/></DATA></COL>

<COL><DATA><xsl:value-of select="zip"/></DATA></COL>

<COL><DATA><xsl:value-of select="city"/></DATA></COL>

<COL><DATA><xsl:value-of select="phone"/></DATA></COL>

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

comment · July 29, 2012

Would you have any idea why the following isn't working?

No, not without seeing the XML source document being imported.

dysong3 · July 30, 2012

Here is a truncated version of the source document. There are hundreds of tables of which only two appear here


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

<!--

- phpMyAdmin XML Dump

- version 3.5.1

- http://www.phpmyadmin.net

-

- Host: localhost

- Generation Time: Jul 28, 2012 at 11:16 AM

- Server version: 5.0.84-log

- PHP Version: 5.2.17

-->

<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/">

    <!--

    - Structure schemas

    -->

    <pma:structure_schemas>

	    <pma:database name="pulloffch" collation="latin1_german1_ci" charset="latin1">

		    <pma:table name="clients">

			    CREATE TABLE `clients` (

				  `id` int(11) NOT NULL auto_increment,

				  `name` varchar(255) collate latin1_german1_ci NOT NULL,

				  `lastname` varchar(255) collate latin1_german1_ci NOT NULL,

				  `email` varchar(255) collate latin1_german1_ci default NULL,

				  `address` varchar(255) collate latin1_german1_ci default NULL,

				  `zip` int(11) default NULL,

				  `city` varchar(255) collate latin1_german1_ci default NULL,

				  `phone` varchar(45) collate latin1_german1_ci default NULL,

				  `comment` text collate latin1_german1_ci,

				  `places` int(11) NOT NULL,

				  `venu` tinyint(1) default NULL,

				  `representation_id` int(11) NOT NULL,

				  PRIMARY KEY  (`id`),

				  KEY `fk_clients_representations1` (`representation_id`)

			    ) ENGINE=MyISAM AUTO_INCREMENT=1211 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

		    </pma:table>

	    </pma:database>

    </pma:structure_schemas>

    <!--

    - Database: 'pulloffch'

    -->

    <database name="pulloffch">

	    <!-- Table clients -->

	    <table name="clients">

		    <column name="id">160</column>

		    <column name="name">xxx</column>

		    <column name="lastname">chobaz troupe</column>

		    <column name="email">[email protected]</column>

		    <column name="address">-</column>

		    <column name="zip">0</column>

		    <column name="city">-</column>

		    <column name="phone">077777777777</column>

		    <column name="comment">Invit troupe

</column>

		    <column name="places">1</column>

		    <column name="venu">0</column>

		    <column name="representation_id">283</column>

	    </table>

	    <table name="clients">

		    <column name="id">161</column>

		    <column name="name">Vincent</column>

		    <column name="lastname">yyyyy</column>

		    <column name="email">[email protected]</column>

		    <column name="address">-</column>

		    <column name="zip">0</column>

		    <column name="city">-</column>

		    <column name="phone">077777777777</column>

		    <column name="comment">	 1 invitation  </column>

		    <column name="places">1</column>

		    <column name="venu">0</column>

		    <column name="representation_id">283</column>

	    </table>

	 </database>

</pma_xml_export>

comment · July 30, 2012

Your stylesheet points to elements that aren't there. Instead of:

ression needs to have a predicate attached to it. Similarly, there is no element named "id", so this:

<xsl:for-each select="clients">





you need something like:

<xsl:for-each select="pma_xml_export/database/table">





I hesitate here, because I don't see the entire document; if it contains data from tables other than the clients table, then the table element in the above exp

<COL><DATA><xsl:value-of select="id"/></DATA></COL>





points again at nothing. You need either:

<COL><DATA><xsl:value-of select="column[1]"/></DATA></COL>





(assuming the order is known in advance) or:

<COL><DATA><xsl:value-of select="column[@name='id']"/></DATA></COL>

etc. for all the other columns.

dysong3 · August 1, 2012

Great. Thanks very much. We're almost there. The import worked. Of the 900 or so records, nearly half were imported, while the rest were excluded for having errors. However there was no indication as to what the errors were. I find this strange as the original XML document seems to me to be well structured and with regular data.

The second problem is that the data is not inserted into these tables :

and

I imagine this is because the primary key "_kp_adresses_id" is automatically inserted during the import and the key needs to exist in the tables "email" and "téléphone" which is not the case for the moment. What's the best way around this? Create fields in the table where the import works and then dispatch the data to correct tables once it's in the database, or can an XML import handle the insertion of a key field to make related records?

Would it be possible to PM you the XML file if you feel you could resolve these issues? I don't feel I should publish them here on the forum.

comment · August 1, 2012

Of the 900 or so records, nearly half were imported, while the rest were excluded for having errors.

Perhaps you have set up some validation that these records couldn't pass?

The second problem is that the data is not inserted into these tables :

I think you mean fields, not tables? The most likely reason is that the corresponding xsl:value-of element is pointing at nothing. Once again, i would recommend you get familiar with at least the basics of XPath syntax; otherwise you're just shooting in the dark.

Would it be possible to PM you the XML file if you feel you could resolve these issues?

I'm afraid that sounds too much like work... ;)

dysong3 · August 1, 2012

I think you mean fields, not tables?

I mean fields in tables other than the one where data is being directly imported into. "nom" and "email::email" are fields in different tables.

I'm afraid that sounds too much like work...

No worries. Thanks for all the help you have been so far. I'll probably have a few more questions down the line.

comment · August 1, 2012

I mean fields in tables other than the one where data is being directly imported into.

Oh, now I see. It's not possible to import into more than one table at a time. That's a Filemaker limitation - nothing to do with XML. Perhaps you could use the unique(?) client id provided by the XML source in order to temporary link the addresses to their parent records, before you populate your own.

Sign In

XML and Style Sheet

Recommended Posts

"... you mean these fans?"

comment

Lee Smith

"... you mean these fans?"

comment

"... you mean these fans?"

comment

"... you mean these fans?"

comment

"... you mean these fans?"

comment

"... you mean these fans?"

dysong3

comment

dysong3

comment

dysong3

comment

dysong3

comment

dysong3

comment

Create an account or sign in to comment

Create an account

Sign in

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information