cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Split XML file based on dynamic Column Values

Former Member

on ‎08-10-2016 9:03 AM

0 Kudos

Hi Experts ,

I have a requirement where I need to split XML data into multiple CSV file and send to SFTP locations based on column value in the XML tag.

Below is an example of what I am looking for . Please note that the file needs to be split based on the first column value .Can you suggest how to do the same as I am new to PI . Please note that the column values will be dynamic.

Input XML file ( ignoring tags )

  • 111222,PO123,C100000,Test Store,1/1/15,AB1000,Test Soft Toy
  • 111222,PO123,C100000,Test Store,1/1/15,AB1001,Test Soft Toy1
  • 111222,PO123,C100000,Test Store,1/1/15,AB1002,Test Soft Toy2
  • 222111,PO321,C111000,My Store,1/10/15,AB1000,Test Soft Toy
  • 222111,PO321,C111000,My Store,1/10/15,AB1001,Test Soft Toy1
  • 222111,PO321,C111000,My Store,1/10/15,AB1002,Test Soft Toy2

  The expected output  should be

CSV1:

  • 111222,PO123,C100000,Test Store,1/1/15,AB1000,Test Soft Toy
  • 111222,PO123,C100000,Test Store,1/1/15,AB1001,Test Soft Toy1
  • 111222,PO123,C100000,Test Store,1/1/15,AB1002,Test Soft Toy2

  CSV2:

  • 222111,PO321,C111000,My Store,1/10/15,AB1000,Test Soft Toy
  • 222111,PO321,C111000,My Store,1/10/15,AB1001,Test Soft Toy1
  • 222111,PO321,C111000,My Store,1/10/15,AB1002,Test Soft Toy2
Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
‎08-11-2016 1:18 PM
0 Kudos

Can anyone suggest some code for this using Java Mapping ( DOM method ) ?

Show replies
Show replies
anupam_ghosh2
Active Contributor
‎08-11-2016 1:53 PM
0 Kudos

Hi Abhishek,

                  Can you please provide exact sample input you are getting and the output filename with content.

Regards

Anupam

Former Member
‎08-11-2016 2:17 PM
0 Kudos

Input sample XML payload data is below . Filename is shown in previous comments .

<?xml version="1.0" encoding="utf-8"?>

<ns:MT_Inventory xmlns:ns="http://test.com/xi/TEST">

    <row>

        <SELLER_ID>Seller ID</SELLER_ID>

        <SKU>SKU</SKU>

        <SLAVE_CODE>Slave Code</SLAVE_CODE>

        <QUANTITY>Quantity</QUANTITY>

        <MRP>MRP</MRP>

        <MOP>MOP</MOP>

    </row>

    <row>

        <SELLER_ID>100117</SELLER_ID>

        <SKU>1001371001</SKU>

        <SLAVE_CODE>1501</SLAVE_CODE>

        <QUANTITY>27.000</QUANTITY>

        <MRP>4645</MRP>

        <MOP>4645</MOP>

    </row>

    <row>

        <SELLER_ID>100117</SELLER_ID>

        <SKU>1015817001</SKU>

        <SLAVE_CODE>1501</SLAVE_CODE>

        <QUANTITY>3.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100117</SELLER_ID>

        <SKU>1015817005</SKU>

        <SLAVE_CODE>1501</SLAVE_CODE>

        <QUANTITY>11.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100117</SELLER_ID>

        <SKU>1032548012</SKU>

        <SLAVE_CODE>1501</SLAVE_CODE>

        <QUANTITY>2.000</QUANTITY>

        <MRP>895</MRP>

        <MOP>895</MOP>

    </row>

    <row>

        <SELLER_ID>Seller ID</SELLER_ID>

        <SKU>SKU</SKU>

        <SLAVE_CODE>Slave Code</SLAVE_CODE>

        <QUANTITY>Quantity</QUANTITY>

        <MRP>MRP</MRP>

        <MOP>MOP</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1001371001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>551.000</QUANTITY>

        <MRP>4645</MRP>

        <MOP>4645</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1001371901</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP></MRP>

        <MOP></MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1004509223</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1004509224</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1004509227</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>27.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817002</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>97.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817003</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>44.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817004</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>48.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817005</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>64.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817006</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>17.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817007</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>5005.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1015817049</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>15.000</QUANTITY>

        <MRP>3495</MRP>

        <MOP>3495</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1032548012</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>128.000</QUANTITY>

        <MRP>895</MRP>

        <MOP>895</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1183252001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>3395</MRP>

        <MOP>3395</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1183252002</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>3395</MRP>

        <MOP>3395</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1183252005</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>3395</MRP>

        <MOP>3395</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1539899008</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>1095</MRP>

        <MOP>1095</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1572797001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>9.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1572797005</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>29.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1572797008</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>30.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1573193004</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>9.000</QUANTITY>

        <MRP>995</MRP>

        <MOP>995</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1574348004</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>10.000</QUANTITY>

        <MRP>695</MRP>

        <MOP>695</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1574511010</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>5.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1574791001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>2.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1574791002</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>2.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1687187002</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>2.000</QUANTITY>

        <MRP>500</MRP>

        <MOP>500</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1740631003</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP></MRP>

        <MOP></MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1806148001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>20.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1806148002</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>19.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1806148003</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>9.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1806148004</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>9.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100116</SELLER_ID>

        <SKU>1811171001</SKU>

        <SLAVE_CODE>1508</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>Seller ID</SELLER_ID>

        <SKU>SKU</SKU>

        <SLAVE_CODE>Slave Code</SLAVE_CODE>

        <QUANTITY>Quantity</QUANTITY>

        <MRP>MRP</MRP>

        <MOP>MOP</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1032548012</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>19.000</QUANTITY>

        <MRP>895</MRP>

        <MOP>895</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1572797001</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>208.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1572797002</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>10.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1572797004</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>8.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1573083002</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1573083003</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>10.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1573083005</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>1.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1574511010</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>500.000</QUANTITY>

        <MRP>1695</MRP>

        <MOP>1695</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1623087001</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>10.000</QUANTITY>

        <MRP>2595</MRP>

        <MOP>2595</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1623087002</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>8.000</QUANTITY>

        <MRP>2595</MRP>

        <MOP>2595</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1806148001</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>12031.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1806148002</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>1009.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1806148003</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>1022.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1806148004</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>1000.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

    <row>

        <SELLER_ID>100118</SELLER_ID>

        <SKU>1806148005</SKU>

        <SLAVE_CODE>DC36</SLAVE_CODE>

        <QUANTITY>20.000</QUANTITY>

        <MRP>100</MRP>

        <MOP>100</MOP>

    </row>

</ns:MT_Inventory>

PriyankaAnagani
Active Contributor
‎08-11-2016 2:23 PM
0 Kudos

Hi Abhishek,

It should work. Can you post the screenshots of "Display queue"?

---Priyanka

anupam_ghosh2
Active Contributor
‎08-11-2016 3:27 PM
0 Kudos

Hi Abhishek,

                  How many different sender id you are expecting here ?

Can this number go up in future?

Regards

Anupam

Former Member
‎08-11-2016 3:30 PM
0 Kudos

Yes , seller id count needs to be dynamic .

anupam_ghosh2
Active Contributor
‎08-11-2016 3:49 PM
0 Kudos

The count of receiver communication channel cannot be dynamic. You need to specify a max number of receiver component. Do you have a max number of number of different seller id?

Regards

Anupam

PriyankaAnagani
Active Contributor
‎08-10-2016 3:26 PM
0 Kudos

Hi Abshishek,

Please try with the below mapping.

Seller_ID-->RemoveContext-->Sort-->CollapseContext-->Concat with Date-->MT_Inventory_Test

---Priyanka

Show replies
Show replies
Former Member
‎08-11-2016 1:18 PM
0 Kudos

Hi Priyanka ,

I tried , but this is still not working .

former_member190293
Active Contributor
‎08-10-2016 9:18 AM
0 Kudos

Hi Abhishek!

Just use split mapping to create separate message for each of your unique keys. Each of your messages will be put to separate file at the receiver side.

Regards, Evgeniy.

Show replies
Show replies
Former Member
‎08-10-2016 11:44 AM
0 Kudos

Yes , I understood this part . I am attaching my Message Mapping .

Issue is now I am not able to generate separate File names . Its taking the first unique value ( say ,Seller ID) in the file name and hence everytime it is replacing the files . Screen shots attached . Please suggest where the issue can be in the File Name part

FileName mapping -

Overall structure

former_member190293
Active Contributor
‎08-10-2016 11:52 AM
0 Kudos

Hi Abhishek!

I see that you save file name to variable. And what are your further steps?

Regards, Evgeniy.

Former Member
‎08-10-2016 1:14 PM
0 Kudos

As shown in the screen shot , I am passing the file name in the variable with combination of Seller Id (Unique Id) and time stamp . Nothing else has been done in this part .

former_member190293
Active Contributor
‎08-10-2016 3:08 PM
0 Kudos

Ok. And how do you use this variable to set the file name?

Do you put it to Dynamic Configuration or save as element in target payload? Or what else?

Regards, Evgeniy.

Ask a Question