sflynn

XML and XSLT ALMOST WORKING PLEASE HELP

16 posts in this topic

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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. :)

Share this post


Link to post
Share on other sites

Posted (edited)

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

Edited by ggt667

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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" />

Share this post


Link to post
Share on other sites

Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Posted (edited)

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>bill@bubba.ie</DATA>
      </COL>
      <COL>
        <DATA>bill@bubba.ie</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>bill@bubba.ie</DATA>
      </COL>
      <COL>
        <DATA>bill@bubba.ie</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 by ggt667

Share this post


Link to post
Share on other sites

Posted (edited)

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 by comment

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Posted (edited)

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 by ggt667

Share this post


Link to post
Share on other sites

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

  • Similar Content

    • By Andreas T.
      I need some help in determining if I am missing something crucial here or if there is a limitation or bug in the function BE_ApplyXSLT from the BaseElements plugin. I am using version 3.3.4 (which is the latest) on Mac OS X 10.11.6 using FileMaker 15 Adv. and 16 Adv.
      I am unable to get any output from the transformation, nor any error.
      According to the documentation, the command shall return either 0 when successful or an error. Link
      In my testing it only returns blank, and gives no error even when no output file is generated. It should not return blank in cany case according to the docs. (I suspect there may be a documentation error and that it actually returns blank when there is no error.)
      No error is found using Get(LastError). BE_GetLastError returns 0.
      The function format is this:
      BE_ApplyXSLT ( xmlPath ; xslt ; outputPath ) Here are the input parameters I have used:
      BE_ApplyXSLT (      "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/ABC_nota_108.xml" ;      BE_ReadTextFromFile ( "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/HTMLvisning/Stylesheet_Full.xsl" );     "/var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/output.html" ) I have verified that the files exist and that both the XML and XSLT are valid. All files are in UTF-8. I can transform the XML with this XSLT using other
      methods without errors. I have also tried having the XSLT in a variable.
      As I understand it, BaseElements uses the libxml2 engine. I have tried doing the transform from the command line directly and it works as it should:
      xsltproc -o /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/output.html /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/HTMLvisning/Stylesheet_Full.xsl /var/folders/y6/1yf76qcs5y91h_w4j27hxp980000gp/T/S10.2/ABC_nota_108.xml It also works fine using the XMLLib OSAX in AppleScript. Unfortunately, I need Windows support also so I would like to get the BaseElements function to work.
      I hope somone can shed some light on what may be going on.
    • By blueworld4
      I am having some trouble getting my XSLT file to work.  I have followed many of the posts on here and have referenced
      https://www.w3schools.com/xml/xsl_intro.asp
      https://www.w3schools.com/xml/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog
      I seem to be able to get the examples to work and have had success with the example files for metars.cache.xml weather meter readings, but can't see why my own isn't working properly when it looks the same to me.  Any guidance would be gratefully received.  I am not new to Filemaker but this is my first attempt at importing and exporting data to/from our solution in XML which meets the UK Government standards.  My XSLT seems only to match fields but not actually import and records.  I have attempted referencing the template match to root with  <xsl:template match="/Message"> but this causes an "Invalid Document Structure" error in Filemaker, which doesn't seem to be a problem with the w3schools try xslt page.  I have reverted back to the format in the meters.xslt example file and now hope that somebody can help!!
      metars.cache.xml
      meters.xslt
      ILR-99999999-1617-20160229-144401-01.xml
      ILRLearningDeliveryImport.xslt
    • By jeffwest2
      Trying to set up mirror sync for the first time using FM15 Pro (Not advanced). 
      I have got past the set up of where the database is, the issue is when I try and set up the , as I don't have Advanced I have to import the MirrorSync.xml file directly, when I click continue I get an error that says, 'XML/XSL information is not enough to proceed with import/export', no matter where I put this file it fails.
      Any body had this? and what did you do to get round it?, be great if I could get rid of this sooner rather than later.