Jump to content
_Mark__

XLST for import to Filemaker

Recommended Posts

Hi all,

I'm completely new to XML and I'm making an XLST for an XML feed into my Filemaker database.

Surprisingly, I got the basics down rather fast, but I'm really stumped on a few fields.

See attached XLST, sample dataset and Filemaker test database.

Referencing the XSLT metadata, the import works perfectly fine for the nodes named 'titleId' down through 'dealType' as well as the nodes under 'licensingWindow'. However I can't seem to get the values of a few other nodes. The trouble for me is that the nodes are nested in as child nodes that aren't named uniquely and I just can't figure out how to get their values.

Example:

<titleListingTextSets>
            <listingTexts>
                <listingText>
                    <name>SYNOPSIS75</name>
                    <value>Mickey continues to feel mounting pressure from the network.</value>
                </listingText>
                <listingText>
                    <name>SYNOPSIS234</name>
                    <value>Mickey continues to feel mounting pressure from the network as an affiliates dinner is fast approaching and they need something to sell to advertisers.</value>
                </listingText>
                <listingText>

What I'm looking for is the value (text beginning with the word Mickey) of the field 'value' on the node under the ones whose name is 'name' containing the values 'SYNOPSIS75' and 'SYNOPSIS234'. Those values seem to be the only way to uniquely identify the 'value' fields. That's what I'm not getting. I had one try that did correctly identify and pull over the value data but it messed with the overall for-each I need for importing a number of records and thus I only got the data for a single record.

I'm guessing there is an easy fix for this, but I'm just not familiar enough with XML to know it yet.

I'd really appreciate it if someone could point me toward the right technique to use on this.

You'll see in my XLST some of my failed attempts, if you'd like to see what I was trying.

Thanks in advance,

Mark

NO_SYNOPSES_OR_LICENSE.xslt

XML_TEST.fmp12

Sample_Data.xml

Edited by _Mark__

Share this post


Link to post
Share on other sites

Those data you are asking for is really stored in the XML as a related table. Are you sure you would like to flatten them?

A separate table, with a separate XSLT, and a separate import would probably to do you good; here are my thoughts; this is not a piece of code that actually runs, it's just typed off the top of my head to give you the gist of how I would have done this.

<xsl:template match="/titleListingTextSets/listingTexts/listingText">

<xsl:value-of select="../../titleId"/><!-- Or similar key-->

<xsl:value-of select="name"/>

<xsl:value-of select="value"/>

or similar would probably be a better option in your solution; unless you really want to flatten the data.

 

As pr example below( note this will be like importing "invoice rows" in relation to your existing data ):

$ cat ~/titleListingTextSets_listingTexts_ListingText.xslt
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/">
        <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
            <METADATA>
                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="titleId" TYPE="NUMBER"/>
                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="name" TYPE="TEXT"/>
                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="value" TYPE="TEXT"/>
            </METADATA>
            <RESULTSET FOUND="{count(unscheduledTitles/title/titleListingTextSets/listingTexts/listingText)}">
                <xsl:for-each select="unscheduledTitles/title/titleListingTextSets/listingTexts">
                    <ROW MODID="1" RECORDID="1">
                        <COL>
                            <DATA>
                                <xsl:value-of select="../../titleId"/>
                            </DATA>
                        </COL>
                        <COL>
                            <DATA>
                                <xsl:value-of select="listingText/name"/>
                            </DATA>
                        </COL>
                        <COL>
                            <DATA>
                                <xsl:value-of select="listingText/value"/>
                            </DATA>
                        </COL>
                    </ROW>
                </xsl:for-each>
            </RESULTSET>
        </FMPXMLRESULT>
    </xsl:template>
</xsl:stylesheet>
xsltproc ~/titleListingTextSets_listingTexts_ListingText.xslt ~/Sample_Data.xml  | tidy -i -xml -wrap 0

 

Edited by ggt667

Share this post


Link to post
Share on other sites

Hi ggt667,

Thanks for your reply. I had no idea that data was from another table, but looking at it now, it makes sense. But yes, flattening the data is what I need to do as my database is structured very different than the source for a variety of reasons. I'll take a look at your suggestions and let you know when I find a solution.

Thanks again for looking into this. Much appreciated.

-Mark

Share this post


Link to post
Share on other sites

As there are no attributes attached to that subtable, it's quite hard to extract those data as is; you will most likely have to composite data from the XSLT I gave you to generate the keys you need. Or if you are lucky you can use the XSLT I gave you and reimport the data to the same table if there is an appropriate primary key there somewhere.

Or you can put values from name and value in one field.

<COL>
  <DATA>
    <xsl:value-of select="listingText/name"/><xsl:text> </xsl:text><xsl:value-of select="listingText/value"/>
  </DATA>
</COL>

 

Edited by ggt667

Share this post


Link to post
Share on other sites

I just tested your XSLT and titleID is populating just fine, but name and value aren't. Is there something I'm doing wrong, like do I need to add anything else to your XSLT?

I'm attaching another XSLT I did based a for-each and this strategy you mentioned:

<xsl:value-of select="name"/>

As you suggested, I think if I can get this XSLT composited somehow with either yours or maybe my original XSLT that I posted previously, that's the fix. Is this possible? Again, I wish I knew more about this stuff but my job requires me to jump into the deep end.

SYNOPSIS_DATA_BUT_ONLY_ONE_RECORD.xslt

Edit: It should be mentioned that this XSLT doesn't 'loop', so it only imports one record. So the fix would be to get all records and nodes for the original metadata set in the native table and then 'loop' somehow to grab the 'value' nodes from the related table for each record. (Nice way for me to get to know XML for the first time...;))

Edited by _Mark__

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 DarioDN
      Dear all, I've just create a script to export my records in multiple xml file (one xml for each record) applying also an XSL stylesheet.
      So, if I use the style.xsl taking from my computer ("source" button when filemaker ask me to select the file) it works. If I put the same file online, it doesn't work giving me a SAXParseException error: internal error in NetAccess (occurred in an unknown entity, at line 0, column 0).
      In the same folder where I have the style.xsl there are other xsl files that works, but they are used to "import" process (from XML to FILEMAKER, while in this case I'm working to an export process, from FILEMAKER to XML, I don't know if this could be the problem)
      Someone could help me to understand the error? Thank you
      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp" > <xsl:output omit-xml-declaration="yes" indent="yes"/> <xsl:strip-space elements="*"/> <xsl:template match="fmp:FMPXMLRESULT"> <xsl:for-each select="fmp:RESULTSET/fmp:ROW"> <xsl:text>&#xA;</xsl:text> <xsl:text disable-output-escaping="yes">&lt;!--</xsl:text> <xsl:value-of select="fmp:COL[1]/fmp:DATA"/>.<xsl:value-of select="fmp:COL[2]/fmp:DATA"/>.<xsl:value-of select="fmp:COL[3]/fmp:DATA"/><xsl:text disable-output-escaping="yes">--&gt;</xsl:text> <problem display_name="Checkboxes" > <choiceresponse> <label><xsl:value-of select="fmp:COL[4]/fmp:DATA"/></label> <checkboxgroup> <choice correct="{fmp:COL[6]/fmp:DATA}"> <xsl:value-of select="fmp:COL[5]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[7]/fmp:DATA"/> </choicehint> </choice> <choice correct="{fmp:COL[9]/fmp:DATA}"> <xsl:value-of select="fmp:COL[8]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[10]/fmp:DATA"/></choicehint> </choice> <choice correct="{fmp:COL[12]/fmp:DATA}"> <xsl:value-of select="fmp:COL[11]/fmp:DATA"/> <choicehint selected="true"><xsl:value-of select="fmp:COL[13]/fmp:DATA"/></choicehint> </choice> </checkboxgroup> </choiceresponse> </problem> <xsl:text>&#xA;</xsl:text> </xsl:for-each> </xsl:template> </xsl:stylesheet>  
    • By Pavlk
      I've attached two files. The first one is INVOICE, that is generated by every establishment when you purchase, and is in xml format.
      The second one is XSLT file, that is provided by government fiscal institution and is used to "translate" xml.
      PROBLEM DESCRIPTION: when I try to decode xml using attached xslt scheme on any online xml-converter website the output looks fine. BUT when importing to Filemaker, I receive an error: "XML parsing error: invalid document structure".
       
      I would appreciate any help with the issue. Thanks.
      XML input.xml
      XSLT scheme.xslt
    • 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
×

Important Information

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