XML and XSLT ALMOST WORKING PLEASE HELP

sflynn · April 29, 2017

Hi everyone. Could someone help me? I am so close to having this working and we've spent the last two days trying everything, and we're stumped. We built this xslt template and it's associated xml file... and filemaker opens up like it's all good, allows me to map the fields, all good, then we hit import and it only does one file and there is no data in any of the fields. These two files are the short sanitized version of the data, the original xml has 600 records... but this is exactly the structure, and I'm wondering if anyone can tell us what we are missing.? Again, no data arrives in the record, and it only does one record even thou we have 600. (I know this one will only be a single record).

Any guidance from the group would be so appreciated. Thanks.

Steve.user-export-short.xslt

SteveXML RecordShort.xml

beverly · April 29, 2017

1. the elements in the xml source do NOT match the elements "called" in the XSLT

what is current/id/@value ?

when the source has data/post/id

learn about the basics of XSLT and XPATH:

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

2. to "loop" the xsl:for-each is used around the ROW elements in your XSLT and the select needs to be a valid XPATH, such as data/post

so that you will get one row/record imported with everly post element in your XML source

<xsl:for-each select="data/post">
  <ROW>
    <COL><DATA><xsl:value-of select="./id"/></DATA></COL>
    .....
  </ROW>
</xsl:for-each>

beverly

ggt667 · April 29, 2017

If you are on a mac this is how to test your stuff:

xsltproc Steve.user-export-short.xslt SteveXML\ RecordShort.xml | tidy -i -xml -wrap 0

Usually you want to call 1 template for the FMPXMLRESULT and 1 for each record.

Edited April 29, 2017 by ggt667

sflynn · April 29, 2017

Thanks you two. I'll take those tips and try and get to the next step. I'll let you know. Thanks as always for your input.

ggt667 · April 29, 2017

I changed the template and filled in your data. My example method has been in use since FileMaker 5.5

Edited May 1, 2017 by ggt667

sflynn · April 29, 2017

Thank you so much. I do a bit of dbase stuff, and some coding, and this xml thing is proving trickier then I had thought it would be. Nice to have a challenge. i'll take a look at that template. steve

ggt667 · April 29, 2017

My guess is that this is very close to what you are looking for: $cat steve2fmpxmlresult.xslt

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <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="M/d/yyyy" LAYOUT="" NAME="" RECORDS="{count(/data/post)}" TIMEFORMAT="h:mm:ss a" />
      <METADATA>
        <FIELD NAME="id" TYPE="NUMBER" />
        <FIELD NAME="UserLogin" TYPE="TEXT" />
        <FIELD NAME="UserEmail" TYPE="TEXT" />
        <FIELD NAME="FirstName" TYPE="TEXT" />
        <FIELD NAME="LastName" TYPE="TEXT" />
        <FIELD NAME="UserRegistered" TYPE="TEXT" />
        <FIELD NAME="UserNicename" TYPE="TEXT" />
        <FIELD NAME="Description" TYPE="TEXT" />
        <FIELD NAME="UserRole" TYPE="TEXT" />
        <FIELD NAME="AreyouVATRegistered" TYPE="TEXT" />
        <FIELD NAME="VATNumber" TYPE="TEXT" />
        <FIELD NAME="TaxStatus" TYPE="TEXT" />
        <FIELD NAME="HQID" TYPE="NUMBER" />
        <FIELD NAME="UserUpdated" TYPE="TEXT" />
        <FIELD NAME="DefaultFrameSize" TYPE="TEXT" />
        <FIELD NAME="DefaultFrameRate" TYPE="TEXT" />
        <FIELD NAME="DefaultVideoFormat" TYPE="TEXT" />
        <FIELD NAME="DefaultCodec" TYPE="TEXT" />
        <FIELD NAME="DefaultColorSpace" TYPE="TEXT" />
      </METADATA>
      <RESULTSET FOUND="{count(/data/post)}">
        <xsl:apply-templates select="/data/post" />
      </RESULTSET>
    </FMPXMLRESULT>
  </xsl:template>
  <xsl:template match="*[name() = 'post']">
    <ROW xmlns="http://www.filemaker.com/fmpxmlresult" MODID="0" RECORDID="{position()}">
      <COL><DATA><xsl:value-of select="id" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserLogin" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserEmail" /></DATA></COL>
      <COL><DATA><xsl:value-of select="FirstName" /></DATA></COL>
      <COL><DATA><xsl:value-of select="LastName" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserRegistered" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserNicename" /></DATA></COL>
      <COL><DATA><xsl:value-of select="Description" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserRole" /></DATA></COL>
      <COL><DATA><xsl:value-of select="AreyouVATRegistered" /></DATA></COL>
      <COL><DATA><xsl:value-of select="VATNumber" /></DATA></COL>
      <COL><DATA><xsl:value-of select="TaxStatus" /></DATA></COL>
      <COL><DATA><xsl:value-of select="HQID" /></DATA></COL>
      <COL><DATA><xsl:value-of select="UserUpdated" /></DATA></COL>
      <COL><DATA><xsl:value-of select="DefaultFrameSize" /></DATA></COL>
      <COL><DATA><xsl:value-of select="DefaultFrameRate" /></DATA></COL>
      <COL><DATA><xsl:value-of select="DefaultVideoFormat" /></DATA></COL>
      <COL><DATA><xsl:value-of select="DefaultCodec" /></DATA></COL>
      <COL><DATA><xsl:value-of select="DefaultColorSpace" /></DATA></COL>
    </ROW>
  </xsl:template>
</xsl:stylesheet>

Edited April 30, 2017 by ggt667

sflynn · April 29, 2017

So I have a question, do you have to tell the system how many records are in the file?

<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="1" TIMEFORMAT="h:mm:ss a" />

ggt667 · April 29, 2017

Not really, that part is more for you not going mad in this case. Now that part is updated. You should also put there layout name and database name

Edited April 29, 2017 by ggt667

sflynn · April 29, 2017

Great, because we're trying to set this up as a feed from another site, and I don't know how we'd write the code to count the records before it was ready for FM.

ggt667 · April 29, 2017

With the xslt above this is the output: $ xsltproc steve2fmpxmlresult.xslt SteveXML\ RecordShort.xml

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE>
  <PRODUCT BUILD="" NAME="" VERSION=""/>
  <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="2" TIMEFORMAT="h:mm:ss a"/>
  <METADATA>
    <FIELD NAME="id" TYPE="NUMBER"/>
    <FIELD NAME="UserLogin" TYPE="TEXT"/>
    <FIELD NAME="UserEmail" TYPE="TEXT"/>
    <FIELD NAME="FirstName" TYPE="TEXT"/>
    <FIELD NAME="LastName" TYPE="TEXT"/>
    <FIELD NAME="UserRegistered" TYPE="TEXT"/>
    <FIELD NAME="UserNicename" TYPE="TEXT"/>
    <FIELD NAME="Description" TYPE="TEXT"/>
    <FIELD NAME="UserRole" TYPE="TEXT"/>
    <FIELD NAME="AreyouVATRegistered" TYPE="TEXT"/>
    <FIELD NAME="VATNumber" TYPE="TEXT"/>
    <FIELD NAME="TaxStatus" TYPE="TEXT"/>
    <FIELD NAME="HQID" TYPE="NUMBER"/>
    <FIELD NAME="UserUpdated" TYPE="TEXT"/>
    <FIELD NAME="DefaultFrameSize" TYPE="TEXT"/>
    <FIELD NAME="DefaultFrameRate" TYPE="TEXT"/>
    <FIELD NAME="DefaultVideoFormat" TYPE="TEXT"/>
    <FIELD NAME="DefaultCodec" TYPE="TEXT"/>
    <FIELD NAME="DefaultColorSpace" TYPE="TEXT"/>
  </METADATA>
  <RESULTSET FOUND="2">
    <ROW MODID="0" RECORDID="1">
      <COL>
        <DATA>29234</DATA>
      </COL>
      <COL>
        <DATA>[email protected]</DATA>
      </COL>
      <COL>
        <DATA>[email protected]</DATA>
      </COL>
      <COL>
        <DATA>bill</DATA>
      </COL>
      <COL>
        <DATA>jones</DATA>
      </COL>
      <COL>
        <DATA>2017-01-09</DATA>
      </COL>
      <COL>
        <DATA>bubbabill-ie</DATA>
      </COL>
      <COL>
        <DATA>We are a full service shrimp company </DATA>
      </COL>
      <COL>
        <DATA>flyer|bbp_participant</DATA>
      </COL>
      <COL>
        <DATA>1</DATA>
      </COL>
      <COL>
        <DATA>1234567890</DATA>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA>29234</DATA>
      </COL>
      <COL>
        <DATA>20170428</DATA>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
    </ROW>
    <ROW MODID="0" RECORDID="2">
      <COL>
        <DATA>29235</DATA>
      </COL>
      <COL>
        <DATA>[email protected]</DATA>
      </COL>
      <COL>
        <DATA>[email protected]</DATA>
      </COL>
      <COL>
        <DATA>bill</DATA>
      </COL>
      <COL>
        <DATA>jones</DATA>
      </COL>
      <COL>
        <DATA>2017-01-09</DATA>
      </COL>
      <COL>
        <DATA>bubbabill-ie</DATA>
      </COL>
      <COL>
        <DATA>We are a full shrimp service company </DATA>
      </COL>
      <COL>
        <DATA>flyer|bbp_participant</DATA>
      </COL>
      <COL>
        <DATA>1</DATA>
      </COL>
      <COL>
        <DATA>1234567890</DATA>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA>29234</DATA>
      </COL>
      <COL>
        <DATA>20170428</DATA>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
      <COL>
        <DATA/>
      </COL>
    </ROW>
  </RESULTSET>
</FMPXMLRESULT>

Edited April 29, 2017 by ggt667

comment · April 29, 2017

3 hours ago, sflynn said:

I'm wondering if anyone can tell us what we are missing.?

Beverly already pointed out your major mistakes. FWIW, here's a skeleton stylesheet showing the absolute minimum you need in order to import the first three fields - with some comments added for clarity:

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

<!-- match the root element -->
<xsl:template match="/data">
	<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
		<!-- define fields -->
		<METADATA>
			<FIELD NAME="id" TYPE="NUMBER"/>
			<FIELD NAME="UserLogin"/>
			<FIELD NAME="UserEmail"/>
			
			<!-- continue to add more fields here -->
			
		</METADATA>
		<!-- get the data -->
		<RESULTSET>
			<!-- create a ROW for each record -->
			<xsl:for-each select="post">
				<ROW>
					<!-- get the fields data -->
					<COL><DATA><xsl:value-of select="id"/></DATA></COL>
					<COL><DATA><xsl:value-of select="UserLogin"/></DATA></COL>
					<COL><DATA><xsl:value-of select="UserEmail"/></DATA></COL>
					
					<!-- continue to add more fields here -->
					
				</ROW>
			</xsl:for-each>
		</RESULTSET>
	</FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Now, since it seems you want to import all the fields from the given XML - and import them as is, without processing them in any way - you could simply tell your stylesheet to do exactly that and save yourself the need to enumerate them explicitly, twice:

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

<!-- match the root element -->
<xsl:template match="/data">
	<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
		<!-- define fields -->
		<METADATA>
			<!-- create a field for each element in the first post -->
			<xsl:for-each select="post[1]/*">
				<FIELD NAME="{name()}"/>
			</xsl:for-each>
		</METADATA>
		<!-- get the data -->
		<RESULTSET>
			<!-- create a ROW for each record -->
			<xsl:for-each select="post">
				<ROW>
					<!-- get the fields data -->
					<xsl:for-each select="*">
						<COL><DATA><xsl:value-of select="."/></DATA></COL>
					</xsl:for-each>
				</ROW>
			</xsl:for-each>
		</RESULTSET>
	</FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

--- IMPORTANT ADDITION ---

The "absolute minimum" part refers to Filemaker version 11 and above. Your profile shows version 8.5. If you're really using version 10 or older, you will need to add a few nodes to the stylesheet:

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

<!-- match the root element -->
<xsl:template match="/data">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <ERRORCODE>0</ERRORCODE>
        <PRODUCT BUILD="" NAME="" VERSION=""/>
        <DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>
        <!-- define fields -->
        <METADATA>
            <!-- create a field for each element in the first post -->
            <xsl:for-each select="post[1]/*">
                <FIELD NAME="{name()}" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
            </xsl:for-each>
        </METADATA>
        <!-- get the data -->
        <RESULTSET FOUND="">
            <!-- create a ROW for each record -->
            <xsl:for-each select="post">
                <ROW MODID="" RECORDID="">
                    <!-- get the fields data -->
                    <xsl:for-each select="*">
                        <COL><DATA><xsl:value-of select="."/></DATA></COL>
                    </xsl:for-each>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Edited April 29, 2017 by comment

beverly · April 30, 2017

IMPORT does NOT require to know how many records are being imported (not for tab, not for csv, not for XML).

Thanks for the alternatives, guys. OP still needs to learn the XPATH and XSLT to understand what we've advised.

beverly

ggt667 · May 1, 2017

If you are to use the latter universal method mentioned; notice it has 3 for-eaches and you have a load you should make 2 versions of the xslt, one that uses for-each and one that uses apply-templates and see which one uses more resources. IIRC for-each uses more resources by a factor of 6 or so.

Also I'm not sure if making everything TEXT is purpose full. Even though all data using comma separator should use "."-for decimal separator on the data layer, using TEXT may be an adventurous path to follow.

Edited May 1, 2017 by ggt667

sflynn · May 1, 2017

Thanks to you all. I will get a chance to dig on this today and try the imports. I REALLY APPRECIATE Your counsel! Thanks. Steve.

ggt667 · May 1, 2017

The only problems you may have with XSLT these days after you get your XSLT up and running is that some sources are async, this will make FileMaker hemorrhage. One way to get around this is to use nginx as a reverse proxy possibly powered by your own express / sinatra / kitura or similar library.

Alternatively you can do a batch download using curl/crontab or similar to your local server or volume.

Edited May 2, 2017 by ggt667

Sign In

XML and XSLT ALMOST WORKING PLEASE HELP

Recommended Posts

sflynn

beverly

ggt667

sflynn

ggt667

sflynn

ggt667

sflynn

ggt667

sflynn

ggt667

comment

beverly

ggt667

sflynn

ggt667

Create an account or sign in to comment

Create an account

Sign in

Similar Content

Heads-up on XSLT in FileMaker 2024

Export Records: 'Specify XML and XSL Options' should have 'XSL style sheet Builder' as another option

Scrutinizer Central - an API for real-time developer intelligence

The PubMed Horcrux: Nine XSL to import one XML. Can the import be recursively scripted or somehow automated?

zulu on linux

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information