How to convert hierarchy XML to CSV/Excel format u...

Former Member · ‎01-26-2011

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"?>

</Hierarchy>

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

</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 · ‎01-27-2011

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.

Former Member · ‎01-27-2011

Thanks for your feedback Fernad.

I have XSD schema for XML and wondering if there are any tutorials on how to acheive the below!

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."

Sorry, I did not understand what you were trying to explain above. Could you please give me more details if possible?

Thanks.

Mithun

Former Member · ‎01-27-2011

Looking at your input/output, I am guessing that you will need mapping to convert it to a XML structure similar to your output structure... Once you do that, your File content conversion will be much simple...

As to mapping, this appears to be complext mapping... download some software like altova XML spy and see how to do XSLT mapping; ...and you can read in SAP help or SDN how to load your XSLT mapping into XI/PI.

My personal feeling is that you are probably going to better answer in PI forums than here, though... Unless you want to use an ABAP transformation program to transform this XML file into Flat file (without using XI/PI)

Former Member · ‎02-01-2011

Thaks for your time and feedback Chris.

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