Jump to content

XSLT for FM14 impoer


Bjorn Hernes

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

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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... :)

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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!! :)

 

Link to comment
Share on other sites

This topic is 3100 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
×
×
  • Create New...

Important Information

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