Export XML using XSL Help

[Devin]

I'm needing to export XML to a customer format.

I've built a staging Table to get the needed data out.

Need some guidance on how/were to start on building out the XSL. I've been scouring the web for help, but I'm still pretty confused.

I've attached the sample data coming out of FM and a sample xml of the format I'm needing to match.

Any help would be greatly appreciated.  

FMdata.xml

ShipAck.xml

[comment]

If I understand correctly, you want to do:

<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 method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/fmp:FMPXMLRESULT">
  <ShipTrackOrderRequest>
    <VendNum>xxxxxxxx</VendNum>
      <OrderList>
      <xsl:for-each select="fmp:RESULTSET/fmp:ROW">
        <Order>
          <OrderHeader>
            <OrderRef>
              <xsl:value-of select="fmp:COL[3]/fmp:DATA"/>
            </OrderRef>
            <RefType>
              <xsl:value-of select="fmp:COL[5]/fmp:DATA"/>
            </RefType>
            <VendorOrderRef>
              <xsl:value-of select="fmp:COL[10]/fmp:DATA"/>
            </VendorOrderRef>
            <ShipDate>
              <xsl:value-of select="fmp:COL[7]/fmp:DATA"/>
            </ShipDate>
            <ShipMethod>
              <xsl:value-of select="fmp:COL[8]/fmp:DATA"/>            
            </ShipMethod>
            <TrackingNumber>
              <xsl:value-of select="fmp:COL[9]/fmp:DATA"/>            
            </TrackingNumber>
            <ShipAmount>
              <xsl:value-of select="fmp:COL[6]/fmp:DATA"/>            
            </ShipAmount>
                </OrderHeader>
          <OrderDetails>
            <LineNum>
              <xsl:value-of select="fmp:COL[2]/fmp:DATA"/>              
            </LineNum>
            <ViaPart>
              <xsl:value-of select="fmp:COL[11]/fmp:DATA"/>                           
            </ViaPart>
            <CustPart>
              <xsl:value-of select="fmp:COL[1]/fmp:DATA"/>              
            </CustPart>
            <QtyShipped>
              <xsl:value-of select="fmp:COL[4]/fmp:DATA"/>              
            </QtyShipped>
          </OrderDetails>
        </Order>
      </xsl:for-each>
    </OrderList>
  </ShipTrackOrderRequest>
</xsl:template>

</xsl:stylesheet>

This is assuming the VendNum is supposed to be hard-coded into the stylesheet - at least I didn't see any field that could reasonably be considered as the source for this.

 

2 hours ago, Devin said:

I've built a staging Table to get the needed data out.

I don't think that should be necessary.

Edited April 25, 2016 by comment

[Devin]

Thanks Comment..

Is the COL[number] dependent on the export order? 

Yes the VendNum was going to be HardCoded as of this time. May change later.

As for the Staging Table not necessary comment. I would think I would need as I'm pulling data from multiple Tables.

[comment]

9 minutes ago, Devin said:

Is the COL[number] dependent on the export order? 

Yes. That is, the field export order - not the order of the records.

 

9 minutes ago, Devin said:

I'm pulling data from multiple Tables.

So? You can include related fields in the export field order.

 

Edited April 25, 2016 by comment

[Devin]

16 minutes ago, comment said:

Yes. That is, the field export order - not the order of the records.

Is There a way not to use the export order and use Field Name? Just seems a better way to know your mapping correctly and that the export order did not change.

 

18 minutes ago, comment said:

So? You can include related fields in the export field order.

You are correct, Never thought of doing it that way. Was was stuck on how I needed to export for excel with Header Names. ( Not using an XSL).

Thanks for stylesheet. This worked.

[comment]

8 minutes ago, Devin said:

Is There a way not to use the export order and use Field Name?

Yes, if you specify FMPDSORESULT as the XML grammar to use for the export. But then your stylesheet will break if you rename a field.

Note that the FMPDSORESULT grammar has been deprecated - whatever that means (it has been deprecated since version 7).

 

Edited April 25, 2016 by comment

[Devin]

I've got a new problem with getting the data out as xml.  The customer has changed the xml and they have added <LineDetail> so that all the line items are now nested in the <Order>

Ive attached as new xml for review.  Any help would be great.

Thanks

ShipAck.xml

[comment]

This is a significant change in requirements - and I don't even know how your file is structured.

I suggest you take a look at the attached demo. It shows how to export records from the parent (Orders) table while including the related child (Line Items) data. Adapting this to your situation should not be too difficult.

 

Compressed file.zip

[Devin]

That is exactly what I'm needing..

I posted a screen shot of the export to show the tables that I'm grabbing from.

Can you break down what this is doing

xsl:value-of select="../../fmp:COL[3]/fmp:DATA[$i]

[comment]

When you export from the parent table, the child fields are exported as COLs with multiple DATA elements (the parent fields will only ever have one DATA per COL).

The stylesheet iterates over the DATA elements of the first child COL and for each one of these it creates a line item and then gets the corresponding DATA values from the other child COLs. The instruction:

xsl:value-of select="../../fmp:COL[3]/fmp:DATA[$i]

gets the value of the $i-th DATA in COL #3. Since COL #3 is also the first child COL, you could have written this one (and only this one) as:

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

 

 

Edited August 5, 2016 by comment

[Devin]

I've tried to mimic what you were doing.

But has you can see it's not even coming close based on the files I've uploaded. What am I doing wrong?

 

 

 

Shipments.xsl

Shipments.xml

[comment]

I see that:

1. you have only one LineDetail per Order;
2. you have multiple Orders with the same OrderID.

So I think it's safe to say you're not exporting from the parent Orders table.

[Devin]

Oh dang.. I was on the wrong layout when I was exporting..

 

Thank so much for your help..