Newbies Gettysburger Posted December 27, 2004 Newbies Posted December 27, 2004 I am trying to get info from my online mySQL database into FMP7. I have exported the data as an XML file. A snippet looks like this: <?xml version="1.0" encoding="iso-8859-1"?> <virtualantietam_com>
QuinTech Posted December 27, 2004 Posted December 27, 2004 Hi, Gettysburger, and welcome to FM Forums! Once you get the hang of XML, you'll see it's pretty easy to use. And the example you posted is extremely easy -- again, once you get the hang of the XML format. Unfortunately, the example files FM provides are not the easiest to understand. Try the following for your example: <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT NAME="Virtual Antietam" VERSION="1" BUILD="20041227"/> <DATABASE NAME="" RECORDS="count(/virtualantietam_com/vaMonuments)" DATEFORMAT="yyyymmdd" TIMEFORMAT="" LAYOUT=""/> <METADATA> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="number" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="name" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="regiment" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="state" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="unitType" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="army" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="corps" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="division" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="brigade" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="commander" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="location" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="date" TYPE="TEXT" /> <FIELD MAXREPEAT="1" EMPTYOK="YES" NAME="text" TYPE="TEXT" /> </METADATA> <RESULTSET> <xsl:attribute name="FOUND" > <xsl:value-of select="count(/virtualantietam_com/vaMonuments)" /> </xsl:attribute> <xsl:for-each select="/virtualantietam_com/vaMonuments"> <ROW MODID="1" RECORDID="1"> <xsl:attribute name="RECORDID"> <xsl:value-of select="position()" /> </xsl:attribute> <COL> <DATA> <xsl:value-of select="monNumber" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monName" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monRegiment" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monState" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monUnitType" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monArmy" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monCorps" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monDivision" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monBrigade" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monCommander" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monLocation" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monDate" /> </DATA> </COL> <COL> <DATA> <xsl:value-of select="monText" /> </DATA> </COL> </ROW> </xsl:for-each> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> and let us know if that works out. Just a quick glance at the syntax, and you can begin to see how logical it is, even if you don't understand everything about it right off the bat. HTH, Jerry
transpower Posted December 27, 2004 Posted December 27, 2004 Gettysburger: Have you attempted ODBC? That might be easier...
Newbies Gettysburger Posted December 27, 2004 Author Newbies Posted December 27, 2004 Wow. I tried it on my little example and it worked. Thanks a million. Unbelievable. I'll drop a note when I get the whole bunch of data in there. Thanks again. Stephen
Newbies Gettysburger Posted December 27, 2004 Author Newbies Posted December 27, 2004 ODBC...I've heard of it but have no idea what it is. Pardon my ignorance. This database stuff is way down on my list of things I understand. I managed to get that mySQL database working after learning just enough CFML, Javascript and FileMaker to get it going, but I'll give it a look see. Thanks. Stephen
QuinTech Posted December 27, 2004 Posted December 27, 2004 OK, i have a little extra time now, so by way of explaining the example above: Ignore everything for now but the <METADATA> and <RESULTSET> elements. The <METADATA> element is fairly trivial, but essential. The MAXREPEAT attribute is, as far as i can tell, useless, since the FMPXMLRESULT can't recognize repeating fields anyhow. EMPTYOK could be YES or NO; YES is easiest to deal with. And you can also have TYPE equal to NUMBER or DATE or several other things. As for the RESULTSET, a bit of inspection shows pretty clearly what's going on here. First you define an attribute of the RESULTSET called FOUND which is equal to the found count. The function "count(/virtualantietam_com/vaMonuments)" works because it is counting all <vaMonuments> elements inside of all <virtualantietam_com> elements. You could also write "count(/*/vaMonuments)" for the same effect, as * is a wildcard in this syntax. The ROW element simply defines what, in FM terms, would be called a record; and the COL element assigns data from the XML set to what FM would call fields. Thus, the node <COL> <DATA> <xsl:value-of select="monNumber"/> </DATA> </COL> in the first position inside the row element simply states in FM terms, "The value of the first field will be determined by the value of <monNumber> in the corresponding XML data." There is a lot more to XML, but it all follows the same logical structure. Reading the W3C's specs on XPATH and especially on XSLT will help a lot. They're easily searchable, and only moderately difficult to follow. J
QuinTech Posted December 27, 2004 Posted December 27, 2004 Transpower... i am completely ignorant of the Mac world, but i am under the impression ODBC only works in a Micro$oft context. Am i mistaken about that? J
Newbies Gettysburger Posted December 27, 2004 Author Newbies Posted December 27, 2004 It worked!!! Thanks so much. I am FREAKING out. Merry Christmas to all. I've been going nuts trying to get this to work and now it seems so simple...the story of my programming life. Thanks, Stephen
transpower Posted December 28, 2004 Posted December 28, 2004 Quintech: ODBC works with any operating system. See, for example, http://www.actualtechnologies.com/
Recommended Posts
This topic is 7271 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now