Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 7271 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

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>

Posted

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

  • Newbies
Posted

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
Posted

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

Posted

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

  • Newbies
Posted

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.