Jump to content

XML and XSLT ALMOST WORKING PLEASE HELP

sflynn

By sflynn
in XML/XSL

 Share

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

Recommended Posts

sflynn Enthusiast

sflynn

Posted
Posted

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 Rising Star

beverly

Posted
Posted

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 Proficient

ggt667

Posted
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
sflynn Enthusiast

sflynn

Posted
  • Author
Posted

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 Proficient

ggt667

Posted
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
sflynn Enthusiast

sflynn

Posted
  • Author
Posted

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 Proficient

ggt667

Posted
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
sflynn Enthusiast

sflynn

Posted
  • Author
Posted

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 Proficient

ggt667

Posted
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
sflynn Enthusiast

sflynn

Posted
  • Author
Posted

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 Proficient

ggt667

Posted
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>[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 by ggt667
comment Grand Master

comment

Posted
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
beverly Rising Star

beverly

Posted
Posted

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 Proficient

ggt667

Posted
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
sflynn Enthusiast

sflynn

Posted
  • Author
Posted

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 Proficient

ggt667

Posted
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

This topic is 3104 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
 Share
Go to topic listing
×
×
  • Create New...

Important Information

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

  I accept