This discussion is archived
7 Replies Latest reply: Feb 20, 2013 3:36 PM by vladodias RSS

File Adapter format translation problem

opthomas Newbie
Currently Being Moderated
This is my incoming payload from a legacy application

"66386","9002011319","01-FEB-2013","","2114.11","ITEM","0.12","","85411","2310","0","85","GST","68085"
"66386","9002011319","01-FEB-2013","","2114.11","ITEM","15.00","","85411","2311","0","85","GST","68085"
"66386","9002011319","01-FEB-2013","","2114.11","ITEM","1823.24","","85411","2313","0","85","GST","68085"
"66386","9002011319","01-FEB-2013","","2114.11","TAX","275.75","","","","","","GST",""
"66386","9002013706","01-FEB-2013","","717.60","ITEM","624.00","","85411","2319","0","85","GST","68232"
"66386","9002013706","01-FEB-2013","","717.60","TAX","93.60","","","","","","GST",""
"66386","9002026588","03-FEB-2013","","2706.30","ITEM","2353.30","","85411","2319","0","85","GST","68085"
"66386","9002026588","03-FEB-2013","","2706.30","TAX","353.00","","","","","","GST",""

What we have here is 3 different record sets. Field 2 is the invoice number. What I need to do is translate this into something like

<Container>
<invoiceHeader>
<InvoiceLines>

The first 4 elements in each line are the header details and everything after that is line level detail.

So the resultant xml will look something like this........ Please note I have left A LOT of the detail for xml out of this

<container>
     <invoice>
          <Vendor>66386<>
          <InvoiceNum>9002011319<>
          <InvoiceDate>01-FEB-2013<>
          <Description/>
          <InvoiceAmount>2114.11<>
          <Lines>
               <Line>
                    <LineType>ITEM<LineType>
                    <LineAmount>0.12<LineAmount>
                    <LineDescription/>
                    <Code1>85411<Code1>
                    <Code2>2310<Code2>
                    <Code3>0<Code3>
                    <Code4>85<Code4>
                    <TaxCode>GST<TaxCode>
                    <PONumber>68085<PONumber>
               </Line>
               <Line>
                    <>ITEM<><>15.00<><><><>85411<><>2311<><>0<><>85<><>GST<><>68085<>
               </Line>
               <Line>
                    <>ITEM<><>1823.24<><><><>85411<><>2313<><>0<><>85<><>GST<><>68085<>
               </Line>
               <Line>
                    <>TAX<><>275.75<><><><><><><><><><><><>GST<><><>
               </Line>
          </Lines>
     </invoice>               
     <invoice>
          <>66386<><>9002013706<><>01-FEB-2013<><><><>717.60<>
          <Lines>
               <Line>
                    <>ITEM<><>624.00<><><><>85411<><>2319<><>0<><>85<><>GST<><>68232<>
               </Line>
               <Line>
                    <>TAX<><>93.60<><><><><><><><><><><><>GST<><><>
               </Line>
          </Lines>
     </invoice>
     <invoice>
          <>66386<><>9002026588<><>03-FEB-2013<><><><>2706.30<>
          <Lines>
               <Line>
                    <>ITEM><>2353.30<><><><>85411<><>2319<><>0<><>85<><>GST<><>68085<>
               </Line>
               <Line>               
                    <>TAX<><>353.00<><><><><><><><><><><><>GST<><><>
               </Line>
          </Lines>
     </invoice>
</container>
  • 1. Re: File Adapter format translation problem
    Sanjay Bharatiya Newbie
    Currently Being Moderated
    Hi,

    The resultant XML you have posted is not correct. Please validate it and post the correct one.

    Thanks,
    Sanjay
  • 2. Re: File Adapter format translation problem
    opthomas Newbie
    Currently Being Moderated
    <?xml version="1.0" encoding="windows-1252" ?>
    <container>
    <invoices>
    <invoice>
    <invoiceheader>
    <Vendor>66386</Vendor>
    <InvoiceNum>9002011319</InvoiceNum>
    <InvoiceDate>01-FEB-2013</InvoiceDate>
    <Description/>
    <InvoiceAmount>2114.11</InvoiceAmount>
    </invoiceheader>
    <invoiceLines>
    <line>
    <LineType>ITEM</LineType>
    <LineAmount>0.12</LineAmount>
    <LineDescription/>
    <Code1>85411</Code1>
    <Code2>2310</Code2>
    <Code3>0</Code3>
    <Code4>85</Code4>
    <TaxType>GST</TaxType>
    <PONumber>68085</PONumber>
    </line>
    <line>
    <LineType>ITEM</LineType>
    <LineAmount>15.00</LineAmount>
    <LineDescription/>
    <Code1>85411</Code1>
    <Code2>2311</Code2>
    <Code3>0</Code3>
    <Code4>85</Code4>
    <TaxType>GST</TaxType>
    <PONumber>68085</PONumber>
    </line>
    <line>
    <LineType>ITEM</LineType>
    <LineAmount>1823.24</LineAmount>
    <LineDescription/>
    <Code1>85411</Code1>
    <Code2>2313</Code2>
    <Code3>0</Code3>
    <Code4>85</Code4>
    <TaxType>GST</TaxType>
    <PONumber>68085</PONumber>
    </line>
    <line>
    <LineType>TAX</LineType>
    <LineAmount>275.75</LineAmount>
    <LineDescription/>
    <Code1/>
    <Code2/>
    <Code3/>
    <Code4/>
    <TaxType>GST</TaxType>
    <PONumber/>
    </line>
    </invoiceLines>
    </invoice>
    <invoice>
    <invoiceheader>
    <Vendor>66386</Vendor>
    <InvoiceNum>9002013706</InvoiceNum>
    <InvoiceDate>01-FEB-2013</InvoiceDate>
    <Description/>
    <InvoiceAmount>717.60</InvoiceAmount>
    </invoiceheader>
    <invoicelines>
    <line>
    <LineType>ITEM</LineType>
    <LineAmount>624.00</LineAmount>
    <LineDescription/>
    <Code1>85411</Code1>
    <Code2>2319</Code2>
    <Code3>0</Code3>
    <Code4>85</Code4>
    <TaxType>GST</TaxType>
    <PONumber>68232</PONumber>
    </line>
    <line>
    <LineType>TAX</LineType>
    <LineAmount>93.60</LineAmount>
    <LineDescription/>
    <Code1/>
    <Code2/>
    <Code3/>
    <Code4/>
    <TaxType>GST</TaxType>
    <PONumber/>
    </line>
    </invoicelines>
    </invoice>
    <invoice>
    <invoiceheader>
    <Vendor>66386</Vendor>
    <InvoiceNum>9002026588</InvoiceNum>
    <InvoiceDate>03-FEB-2013</InvoiceDate>
    <Description/>
    <InvoiceAmount>2706.30</InvoiceAmount>
    </invoiceheader>
    <invoicelines>
    <line>
    <LineType>ITEM</LineType>
    <LineAmount>2353.30</LineAmount>
    <LineDescription/>
    <Code1>85411</Code1>
    <Code2>2319</Code2>
    <Code3>0</Code3>
    <Code4>85</Code4>
    <TaxType>GST</TaxType>
    <PONumber>68085</PONumber>
    </line>
    <line>
    <LineType>TAX</LineType>
    <LineAmount>353.00</LineAmount>
    <LineDescription/>
    <Code1/>
    <Code2/>
    <Code3/>
    <Code4/>
    <TaxType>GST</TaxType>
    <PONumber/>
    </line>
    </invoicelines>
    </invoice>
    </invoices>
    </container>
  • 3. Re: File Adapter format translation problem
    vladodias Guru
    Currently Being Moderated
    Hi,

    I suggest you do the transformation in two steps, firstly use the native translation (fileAdapter) to translate it to a simpler flat structure, repeating the invoice number and invoice details. Then you can use a xslt to create the final structure, breaking down by invoice number. I reckon it's going to be hard to go from native to the master/detail structure in only one step.

    Cheers,
    Vlad
  • 4. Re: File Adapter format translation problem
    opthomas Newbie
    Currently Being Moderated
    Ok... But just a little confused on how to get the uniqueness at the header level
  • 5. Re: File Adapter format translation problem
    vladodias Guru
    Currently Being Moderated
    InvoiceNum looks like a good identifier, you can use distinct-values on InvoiceNum and then get all the lines for the invoice...
  • 6. Re: File Adapter format translation problem
    opthomas Newbie
    Currently Being Moderated
    Sorry Vlad ...... Having a mental block on this one. Do you have some example code you could provide?
  • 7. Re: File Adapter format translation problem
    vladodias Guru
    Currently Being Moderated
    Say you got a structure like bellow with repetitive ids...
    <root>
    <entry><id>1</id><detail>1detail_a</detail></entry>
    <entry><id>1</id><detail>1detail_b</detail></entry>
    <entry><id>2</id><detail>2detail_c</detail></entry>
    <entry><id>2</id><detail>2detail_d</detail></entry>
    </root>
    Now you want to eliminate the duplicates to achieve something like this...
    <root>
    <entry>
    <id>1</id>
    <details>
    <detail>1detail_a</detail>
    <detail>1detail_b</detail>
    </details>
    </entry>
    <entry>
    <id>2</id>
    <details>
    <detail>2detail_c</detail>
    <detail>2detail_d</detail>
    </details>
    </entry>
    </root>
    To do it you need a transformation like this...
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/">
    <xsl:variable name="root" select="/"/>
    <root>
    <xsl:for-each select="distinct-values(/root/entry/id)">
    <xsl:variable name="id" select="."/>
    <entry>
    <id><xsl:value-of select="$id"/></id>
    <details>
    <xsl:for-each select="$root/root/entry[id = $id]/detail">
    <detail><xsl:value-of select="."/></detail>
    </xsl:for-each>
    </details>
    </entry>
    </xsl:for-each>
    </root>
    </xsl:template>
    </xsl:stylesheet>
    Cheers,
    Vlad

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points