XLS List into CR separated data

[alexinmux]

Hello all,

Im a newbie with XML and XML import into filemaker. But I've suceccfully found some sample and got them to work with my own data. But unfortunatly there is a small part of my data which got not imported :-(

 

This is a sample of the source XML:

<?xml version="1.0" encoding="utf-8"?>
<App Titel="Basic Cooking">
	<Rezept xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="rz2_app.xsd" verwenden="ja" timestamp="15.03.2013 09:59:31" id="7742-1142_161_2">
		<Metadaten>
			<Buchtitel>Basic cooking</Buchtitel>
			<Reihentitel>224 GU Basic cooking</Reihentitel>
			<ISBN>9783774211421</ISBN>
			<Autoren>
				<Liste>
					<Wert>Sälzer</Wert>
					<Wert>Sabine</Wert>
					<Wert>Dickhaut</Wert>
					<Wert>Sebastian</Wert>
				</Liste>
			</Autoren>
			<Fotograf>Bonisolli, Barbara</Fotograf>
			<Seitennummer>161</Seitennummer>
			<Rezeptnummer>2</Rezeptnummer>
		</Metadaten>
	</Rezept>
</App>

this are the field definitions used in my xls-File:

 

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

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

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

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

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

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

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

 

with this part I get most Data from the file into my database:

<xsl:for-each select="/App/Rezept">
	<ROW MODID="0" xmlns="http://www.filemaker.com/fmpxmlresult">
		<xsl:attribute name="RECORDID">
		<xsl:value-of select="position()" />
		</xsl:attribute>
		<COL><DATA><xsl:value-of select="Metadaten/Buchtitel"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/Reihentitel"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/ISBN"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/Autoren/Liste/Wert"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/Fotograf"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/Seitennummer"/></DATA></COL>
		<COL><DATA><xsl:value-of select="Metadaten/Rezeptnummer"/></DATA></COL>
	</ROW>
</xsl:for-each>

Now I get only the first value from "Autoren/Liste/Werte"

 

How can I transform the data so that the field "Autoren" in Filemaker contains all four names separated by returns.

 

I already know that the transformed data has to look like this:

 

<COL><DATA>Sälzer

Sabine

Dickhaut

Sebastian</DATA></COL>

 

but I have absolutly no idea how to do that :-(

 

reagrds

Michael

 

 

[comment]

Instead of :

<COL><DATA><xsl:value-of select="Metadaten/Autoren/Liste/Wert"/></DATA></COL>

try:

<COL><DATA><xsl:for-each select="Metadaten/Autoren/Liste/Wert">
<xsl:value-of select="."/>
<xsl:if test="position()!=last()"><xsl:value-of select="'&#x000D;'"/></xsl:if>
</xsl:for-each></DATA></COL>

[alexinmux]

comment, you're my hero. It's working like a charm.

 

Looks like xsl is yet an other programming language i have to learn :-)

 

Now I can put my hands at two further XSLs. After that the complete import of 100 records in three tables should be done in seconds and not in 10 minutes as it do now (parsing the XML with applescript and the "XML Tools.osax" from satimage :-)))

[comment]

parsing the XML with applescript

 

Ew. Filemaker's XML/XSL capabilities are one of its most powerful features - and probably the most under-utilized. Welcome to the camp of the enlightened.