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>

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

Gettysburger:

Have you attempted ODBC? That might be easier...

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

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

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

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

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

Quintech: ODBC works with any operating system. See, for example,

http://www.actualtechnologies.com/