5 Replies Latest reply: Jun 11, 2012 9:11 PM by vladodias RSS

    Converting XML to CSV and send via B2B

    Venkatesh Ramasamy
      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
          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
            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
              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
                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
                  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.