This discussion is archived
5 Replies Latest reply: Jun 11, 2012 7:11 PM by vladodias RSS

Converting XML to CSV and send via B2B

Venkatesh Ramasamy Newbie
Currently Being Moderated
Hello,

How can I convert the Data that is received by the BPEL process thru a Web Service in to a CSV file and send out via B2B.

There is a web Service that receives data , this data needs to be converted in to a CSV and sent out via B2B. I am planning to use a Write File adapter to write the XML file and then use a file adapter to read the XML file and use the Read File Adapter's Wizard to convert to CSV. Then use B2B to transport the file. I am not sure if I am approaching in the correct way. I would appreciate any suggestions and guidance.

Thanks,
Venkatesh
  • 1. Re: Converting XML to CSV and send via B2B
    vladodias Guru
    Currently Being Moderated
    Hi Venkatesh,

    Use a XSLT transformation in your bpel to convert your data to CSV and then use the file adapter to write it.

    Cheers,
    Vlad

    If you think this is helpful, please consider giving points - it is good etiquette to reward an answerer points (5 - helpful; 10 - correct) for their post if they answer your question.
  • 2. Re: Converting XML to CSV and send via B2B
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Vlad,

    Could you please elaborate the steps, I am a newbie, if you could explain more in detail, that will be more helpful.

    Thanks
    Venkatesh
  • 3. Re: Converting XML to CSV and send via B2B
    vladodias Guru
    Currently Being Moderated
    Hi Venkatesh,

    Drag your file adapter to your composite and follow the wizards, choose operation type = Write.

    When you get into Messages click on the icon to "define schema for native format", follow the wizard (you will have to provide a sample csv file so the wizard can "figure out" how to translate)

    In the composite connect your BPEL to the File Adapter, in the BPEL connect an Invoke operation and connect to the file adapter.

    Drag a "Transform" activity before the invoke so you can transform between your bpel input and your file adapter input.

    Deploy it and test it.

    Cheers,
    Vlad

    If you think this is helpful, please consider giving points - it is good etiquette to reward an answerer points (5 - helpful; 10 - correct) for their post if they answer your question.
  • 4. Re: Converting XML to CSV and send via B2B
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Thanks that was really helpful, I now have a challenge of processing one XML which has One Order but multiple line Items.

    For example, an order has multiple items, so I want the output csv to look like the one below, it's the same order only the SKU and the Quantity changes in each row. I think this could be done in translation, however, I am not sure how. any suggestion / ideas will be very helpful.



    PurchaseOrderNumber,RefNum,shiptoName,Attention,address1,address2,Country,PostalCode,City,State,Phone,SKU,qty,ShippingMethod,OrderDate
    3001,648289515,XYZ Company,Mr. ABC Customer,1155 Street1 Suite 315,,United States,77079,Houston,TX,1231231234,*1216,1*,UPSG,11/2/2011
    3001,648289515,XYZ Company,Mr. ABC Customer,1155 Street1 Suite 315,,United States,77079,Houston,TX,1231231234,*1218,2*,UPSG,11/2/2011
    3001,648289515,XYZ Company,Mr. ABC Customer,1155 Street1 Suite 315,,United States,77079,Houston,TX,1231231234,*1123,4*,UPSG,11/2/2011

    Thanks,
    Venkatesh
  • 5. Re: Converting XML to CSV and send via B2B
    vladodias Guru
    Currently Being Moderated
    Hi Venkatesh,

    The trick is to have nested for-each's and create a variable with the "outer" element, so you can access the outer elements from the internal for-each. For example:

    <xsl:for-each select="/ns0:list/ns0:order">
    <xsl:variable name="order" select="."/>
    <xsl:for-each select="$order/ns0:detail">
    <tns:record>
    <tns:poNumber>
    <xsl:value-of select="$order/ns0:number"/>
    </tns:poNumber>
         <tns:sku>
              <xsl:value-of select="ns0:sku"/>
         </tns:sku>

    Cheers,
    Vlad

    If you think this is helpful, please consider giving points - it is good etiquette to reward an answerer points (5 - helpful; 10 - correct) for their post if they answer your question.

Legend

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