Jump to content
sflynn

XML and XSLT ALMOST WORKING PLEASE HELP

Recommended Posts

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


  • Who Viewed the Topic

  • Similar Content

    • By Olypatchmaster
      Our network person and I have done a bunch of troubleshooting and came up with this summary and a few questions.
      Ever since we installed FileMaker 16 in our two-machine setup, Zulu has been failing. In the Zulu error log from C:\Program Files\360Works\Applications\logs:
      Oct 2, 2017 8:44:59 AM PDT com.prosc.mirror.config.server.FileMakerServerInfo getHostAddressForProperties
      INFO: Will use 127.0.0.1:16020 for direct WPE address.
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FmXmlRequest doRequest
      INFO: Starting request: http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FmXmlRequest setProductVersion
      WARNING: Null product version; assuming 12
      Oct 2, 2017 8:45:05 AM PDT com.prosc.fmpjdbc.FileMakerException <init>
      INFO: com.prosc.fmpjdbc.FileMakerException: Error 959: XML Web Publishing is not enabled - Run the FileMaker Server deployment assistant and make sure that the XML web publishing checkbox is selected () username: domain\user / requestUrl:http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view

       I verified that the URL does indeed return an error code of 959. Playing around with it, I found that if you use the DNS name of the machine instead of the localhost IP:port (https://wpe.domain.edu/fmi/xml/FMPXMLRESULT.xml?-db=DatabaseName&-lay=ProscNoSuchTable&-view), the request returns an error 105 (expected response) instead. 
       
      I poked around on the internet trying to find out more about this, and discovered the -dbnames command; again, it works fine on the main https port (https://wpe.domain.edu/fmi/xml/FMPXMLRESULT.xml?-dbnames), but returns a 959 on the Custom Web Publishing port of 16020 (http://127.0.0.1:16020/fmi/xml/FMPXMLRESULT.xml?-dbnames). So it’s like XML is enabled on port 443, but not for the Custom Web Publishing port 16020. How is that possible?
       
      For reasons we don’t fully understand and haven’t figure out how to correct, both the FileMaker Master and the Worker machines ended up with the Web Publishing Engine. So I tried the port 16020 request on the Master server, and it works! Which is great, except we want it to work on the Worker machine, not the Database server which ended up with a WPE.
       
      Some questions:
      1)      We know XML is enabled on the main (port 443) site, what do we need to do to make work on the localhost:16020 site?
      a. I hope this involves moving the ‘Web Server’ listed in the Admin Console to the Worker machine, but if not any info on what might be going on there would be great.
      2)      If we can’t get the Worker machine to realize XML is enabled on port 16020, can we change the FM config so Zulu doesn’t use port 16020 ‘for direct WPE address.’?
      3)      If we can’t do that, are there any issues with installing it on the Master, other than the obvious issue that we don’t want the WPE on that machine?
      Thanks for any insight anyone might have.
    • By fmdataweb
      I'm doing an export of a table with 5 fields to an Excel file with an XSL stylesheet to control the formatting of the Excel data. So far it's working well - I just need to add some additional formatting for a few of the cells. I'm looking for help with examples/syntax for setting the following attributes:
      - making a cell bold
      - changing the font size of a cell
      - making all columns set to auto resize so they fit the contents of each column (same as double clicking the divider between 2 columns)
      All fields I'm exporting are text fields.
      Thanks,
      Steve
    • By Jeff M
      Hello,
      I've done quite a bit with FileMaker in the past, but have never jumped into the XML arena. Now I have a need to do so, and I'm really needing some help with something I'm sure is quite basic...but still foreign to me. I have an external data source which export XML in a very specific format (see attached). I need to import that into FileMaker so I can manipulate the data, do some various lookups, etc. Once I've made my changes in the data, I need to export in the exact format it was when imported so the external data source can properly import.
      Not sure if it makes sense to import into 2 tables (workflows, workflow steps) or just a single table. I'm open either way. Once the data is in FileMaker I can get all my calculations and lookups and scripts going and make the data what I need it to be, but then I'll need to export it.
      Can someone point me in the right direction?
      Thanks so much in advance!
      Jeff
      workflows_export.xml
    • 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.
×

Important Information

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