How to convert org hierarchy XML to CSV/Excel format using PI/XI

[Former Member]

Hi All,

I am working with SAP PI/XI for the first time and need some help file conversion of complex hierarchy XML to CSV file using PI. I have done plenty of research on SDN and could find much related to what I want to do.

Here is the example of how my input XML would look like:

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

<PartyMaster type="OrgHierarchy" name="Compass Group Owned">

<PartyMasterHierarchy>

<Hierarchy level="3" nodeName="Bon Appetit Sector" type="Sector" ID="A00000" refID="A00000">

<Hierarchy level="4" nodeName="BA Division" type="Division" ID="AB0000" refID="AB0000" >

<Hierarchy level="5" nodeName="BA East Coast Region" type="Region" ID="ABE000" refID="ABE000">

<Hierarchy level="6" nodeName="Engstrom, John RVP" type="District" ID="ABE01" refID="ABE01" >

</Hierarchy>

</Hierarchy>

</Hierarchy>

</Hierarchy>

<Hierarchy level="3" nodeName="Levy Sector" type="Sector" ID="K00000" refID="K00000".

<Hierarchy level="4" nodeName="Levy Sector Division" type="Division" ID="K90000" refID="K90000">

<Hierarchy level="5" nodeName="Levy Sector Region" type="Region" ID="K99000" refID="K99000">

<Hierarchy level="6" nodeName="TBA Total" type="District" ID="K9901" refID="K9901"> </Hierarchy>

</Hierarchy>

</Hierarchy>

</Hierarchy>

</PartyMasterHierarchy>

</PartyMaster>

I would like to get CSV output in the format below

DISPLAY_NAME, DOCUMENT_DESCRIPTION, PARENT, EXTERNAL_ID, ORG_UNIT_TYPE

Bon Appetit Sector, Bon Appetit Sector,constant value, A00000, constant value

Levy Sector, Levy Sector, constant value, K00000, constant value

BA Division, BA Division, A00000, AB0000, constant value

Levy Sector Division, Levy Sector Division, K00000, K90000, constant value

BA East Coast Region, BA East Coast Region, AB0000, ABE000, constant value

Levy Sector Region, Levy Sector Region, K90000, K99000, constant value

Engstrom John RVP, Engstrom John RVP, ABE000, ABE01, constant value

TBA Total ,TBA Total ,K99000, K9901, constant value

1) How do I go about this? I think I have to configure an adapter but which adapter would give me the desired result?

2) How do I tell PI when to pick the file from and where to place it after conversion? Could this be done in adapter configuration?

Any help will be greatly appreciated.

Thanks.

--Mithun

[Former Member]

Moderator message - Cross post locked Rob

[baskar_gopalakrishnan2]

>>> 1) How do I go about this? I think I have to configure an adapter but which adapter would give me the desired result?

Use File adapter at the sender side to pick up the xml and use file adapter at the receiver side (message protocol file content conversion) to convert xml format to csv.

Basically you have to configure in the file adapter (FCC) on the receiver side to get desired csv structure from XML.

>>> 2) How do I tell PI when to pick the file from and where to place it after conversion? Could this be done in adapter configuration?

Configure sender file adapter to pick up the file from the specified directory. Configure receiver file (fcc adapter) to drop the csv file after conversion.

For sender adapter

http://help.sap.com/saphelp_nw04/helpdata/en/e3/94007075cae04f930cc4c034e411e1/content.htm

For receiver adapter in our case FCC (FILE content Conversion) follow the link

http://help.sap.com/saphelp_NW04/helpdata/en/d2/bab440c97f3716e10000000a155106/content.htm

Hope answered your question.

Mapping area: simple suggestion. Keep input and output message same structure and do one to one mapping. In the receiver fcc adapter configure for csv structure following instruction given in the above second link

Note: FCC adapter does not support nested structure more than 2. So in your case you can achieve using conversion agent tool. Otherwise you might have to achieve through some graphical mapping.