Jump to content

Export XML using XSL Help


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

Recommended Posts

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

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

  • 3 months later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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]

Screen Shot 2016-08-05 at 10.18.39 AM.png

Link to comment
Share on other sites

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

This topic is 2783 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.