XLST for import to Filemaker

[_Mark__]

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 February 12, 2018 by _Mark__

[ggt667]

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 February 13, 2018 by ggt667

[_Mark__]

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

[ggt667]

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 February 13, 2018 by ggt667

[_Mark__]

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 February 13, 2018 by _Mark__