XSLT for FM14 impoer

[Bjorn Hernes]

Hi.

Am a beginner, so please bear with me ... The purchaser of our products have gone EDI XML, and I would like to be able to extract some information from their order XMLs, into a FMP database.  Have searched the net and found xslt templates that I have tried to tweak, but for some reason I am not getting any data into the FMP file ... only empty fields.   Here is the source xml ordre_4500644900_20150925093113.xml - and here is an example (only testing a couple fields to see if it works)  nytest2_header.xsl 

I am sure there is something very simple I am missing in the reference side of things.  Does it matter if the source file uses ns0 before each node?  Do I need to declare this in the xsl?

ordre_4500644900_20150925093113.xml

nytest2_header.xsl

[comment]

Does it matter if the source file uses ns0 before each node?

Yes, it matters very much. It places all the prefixed nodes in a namespace associated with the prefix. In order to address these nodes in your stylesheet, you must declare this namespace ("http://www.ean-nor.no/schemas/eannor" in your example), assign it a prefix, and use that prefix when selecting them. Otherwise you'd be selecting non-existing nodes.

There are other issues with your stylesheet:

• You try to select "Order/OrderHeading/OrderNumber" but the document has "OrderHeader", not OrderHeading;
• XML is case-sensitive: <row> is not the same thing as <ROW> (and Filemaker's XML schema requires <ROW>);
• Description has no "name" attribute;
• It's not clear if you're trying to import into Orders or into LineItems: you create a record for each type, with one field only - when you have defined two fields in the METADATA section. That just doesn't make sense.

Here's an example that will create a record for each line item (at least I believe it will; seeing an example with two or more items would be helpful), with fields for the parent order's number and the item's description:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns0="http://www.ean-nor.no/schemas/eannor"
exclude-result-prefixes="ns0">

<xsl:template match="/ns0:Order">
  <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <METADATA>
      <FIELD NAME="OrderNumber" TYPE="NUMBER"/>
      <FIELD NAME="Description" TYPE="TEXT"/>
    </METADATA>
    <RESULTSET>
      <xsl:variable name="order-num" select="ns0:OrderHeader/ns0:OrderNumber" />
      <xsl:for-each select="ns0:OrderDetails/ns0:BaseItemDetails">
        <ROW>
          <COL><DATA><xsl:value-of select="$order-num"/></DATA></COL>
          <COL><DATA><xsl:value-of select="ns0:Description"/></DATA></COL>
        </ROW>
      </xsl:for-each>
    </RESULTSET>
  </FMPXMLRESULT>
</xsl:template>
  
</xsl:stylesheet>

 

Edited September 28, 2015 by comment

[ggt667]

Yes, it matters very much. It places all the prefixed nodes in a namespace associated with the prefix. In order to address these nodes in your stylesheet, you must declare this namespace ("http://www.ean-nor.no/schemas/eannor" in your example), assign it a prefix, and use that prefix when selecting them.

... or write full paths. But using the namespace feature does save you from long node paths/names.

[comment]

... or write full paths.

What exactly do you mean by "full paths"?

Edited September 28, 2015 by comment

[Bjorn Hernes]

Thanks for the help, I was able to make it work using your example.  Struggled a bit until i realised I was not able to create more than one "xsl:for-each" definition (eg. inserting info from different "trees" in the xml), so I ended up using the xls variable option and values.  The end xls is attached.  This way I can extract the info I want from the xmls into a system that i can export to Excel (as MacExcel does not support xml.).  Alternatively I could turn it into xhtml ... using a different xsl.  If I want to import records from a number of xml files I have to do it one by one, or can I use a script perhaps?

nytest5_header.xsl

[comment]

I was not able to create more than one "xsl:for-each" definition

I don't see why you would need to use more than one xsl:for-each instruction. Since you're importing line items, you want to create a ROW for each ns0:OrderDetails/ns0:BaseItemDetails, not for each ns0:OrderHeader of which there's only one. The way you have it now, your won't be able to import an order with two or more items correctly.

 

If I want to import records from a number of xml files I have to do it one by one, or can I use a script perhaps?

You can use a script if you know the names of the files to import.

[ggt667]

What exactly do you mean by "full paths"?

https://developer.mozilla.org/en-US/docs/Web/SVG/Namespaces_Crash_Course

I like to compare the xmlns to setting current "working directory" for the tags down the hierarchy from where it's mentioned.

[comment]

I asked what you meant by "full paths". Why are you wasting my time by referring me to a tutorial which I don't need and which doesn't even contain the word "path"?

 

 

Edited September 30, 2015 by comment

[beverly]

 

nytest5_header.xsl

I've looked at your XSLT. It appears that you are trying to bring in "parent-type" information for each "line-item". You do not need additional for-each loops and you do  not need to set variables. Simply use the correct XPath for the 'ancestor::' of the line items:

so instead of 

<xsl:variable name="order-num" select="ns0:OrderHeader/ns0:OrderNumber" />

and 

<COL><DATA><xsl:value-of select="$order-num"/></DATA></COL>

you would set the COL like this:

<COL><DATA><xsl:value-of select="ancestor::ns0:OrderHeader/ns0:OrderNumber"/></DATA></COL>

as this is the ANCESTOR (by path) for the order number for any particular "ns0:OrderDetails/ns0:BaseItemDetails/ns0:LineItemNum" (which really should be your for-each loop to get each line-item). This would allow you to get multiple order in your XML source along with multiple line-items per order.

beverly

but if what you have works...

[comment]

you would set the COL like this:

<COL><DATA><xsl:value-of select="ancestor::ns0:OrderHeader/ns0:OrderNumber"/></DATA></COL>

I am afraid that would not work here because the header is not an ancestor of the line items.

Also, since you need the header field/s to repeat for every line item, I believe it would be better to use variable/s for them, instead of forcing the engine to walk the ladder up and down on every iteration.

[beverly]

comment is correct, as OP did not choose the correct XPath in the xsl:for-each to start. Based on the XML sample this what I would have done. Then the Headers do NOT need to repeat with the use of variables for each item. Shall I create another (more flexible) XSLT? No, I gave my advice and then told OP to use what's been done if it works!! FMP has so many ways to do the same thing, we know!!