4 Replies Latest reply: Feb 1, 2011 3:16 PM by Mithun Dha RSS

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

Mithun Dha
Currently Being Moderated

posted in PI forum but not many replies, please bear with me as I am in crunch time*

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

  • Re: How to convert hierarchy XML to CSV/Excel format using PI/XI
    Fernand Lesmana
    Currently Being Moderated

    Hi Mithun,

     

    You might try this step below :

     

    1. As for xsd schema fo the xml or create xsd schema base on the sample xml file using tools like xmlspy or others free tools.

    2. import you xsd schema as outbound Data type

    3. apply mapping from source to target (csv).

     

    Configuration

     

    Sender file adapter (without content conversion)

    Receiver file adapter (with content conversion).

     

    so the sender adapter will be monitoring inbound directory and write the result to another directory.

     

    Thank You and Best Regards

     

    Fernand.

Actions