AnsweredAssumed Answered

Style sheet for Export to xml from related tables

Question asked by LewisVolk on Sep 28, 2018
Latest reply on Oct 1, 2018 by beverly

I have a simple Fm file (Orders) with two tables Orders and Items. They are related on a field named Key in both tables. Orders can have more than one item.  I need the exported .xml output to be.

<Document>

                <OrderHeader>

                                <OrderNumber> OR-1001</OrderNumber>

                                <Customer>Ace Inc. </Customer>

                                <OrderLines>

                                                <ItemNumber>wiget101</ItemNumber>

                                                <ItemPrice>1.01</ItemPrice>

                                </OrderLines>

                                <OrderLines>

                                                <ItemNumber>Wiget202</ItemNumber>

                                                <ItemPrice>2.02</ItemPrice>

                                </OrderLines>

                </OrderHeader>

</Document>

 

Using the below style sheet. I get the OrderHeader information Twice (See below).  I’ve found enough information to make think I need another “<xsl:for-each“  around the Orderlines tags. I might also have to use some kind of xpaths (./ or../) to pull the correct fields. But I’m stuck there. Any help the with correct statements needed would be much appreciated!

 

TIA Lewis

 

STYLE SHEET

       <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

       <xsl:strip-space elements="*"/>

       <xsl:template match="fmp:FMPXMLRESULT">

              <Document>

                     <xsl:for-each select="fmp:RESULTSET/fmp:ROW">

                           <OrderHeader>

                                  <OrderNumber>

                                         <xsl:value-of select="fmp:COL[1]"/>

                                  </OrderNumber>

                                  <Customer>

                                         <xsl:value-of select="fmp:COL[2]/fmp:DATA"/>

                                  </Customer>

                                  <OrderLines>

                                         <ItemNumber>

                                                <xsl:value-of select="fmp:COL[3]/fmp:DATA"/>

                                         </ItemNumber>

                                         <ItemPrice>

                                                <xsl:value-of select="fmp:COL[4]/fmp:DATA"/>

                                         </ItemPrice>

                                  </OrderLines>

                           </OrderHeader>

                     </xsl:for-each>

              </Document>

 

OUT PUT

       <OrderHeader>

              <OrderNumber>OR-1001</OrderNumber>

              <Customer>Ace Inc</Customer>

              <OrderLines>

                     <ItemNumber>wiget101</ItemNumber>

                     <ItemPrice>1.01</ItemPrice>

              </OrderLines>

       </OrderHeader>

       <OrderHeader>

              <OrderNumber>OR-1001</OrderNumber>

              <Customer>Ace Inc</Customer>

              <OrderLines>

                     <ItemNumber>Wiget202</ItemNumber>

                     <ItemPrice>2.02</ItemPrice>

              </OrderLines>

     </OrderHeader>

Outcomes