How to read an XML file in SAP Data Intelligence?

[amish1980_95]

Hello Team,

I need to read a heavy XML file in SAP Data intelligence v 3.1.

As there are no standard operators to acheive this, what is teh best approach?

Thanks,

Indu K.

[michael_eaton3]

Hello

What do you want to do with it once you've read it? There are operators to read files, and convert data from XML, but then what would you like to do?

Michael

[amish1980_95]

Hello michael.eaton3

Thanks for your answer.

My requirement is to parse the xml and load the data in HANA table.

The XML is deep structure with attributes . I used format converter but attributes and value in deep elements are not being captured in csv.

Regards,

IK.

[shaktik]

Hi Indu ,

I assume you are able to read the file and tried to fetch relevant information form xml file using standard DI operators and it did not meet you needs , I will suggest you to go with custom python logic to retrieve information from xml file .

Thanks
Shakti Kumar

[bengt1]

Hi Indu,

it would be feasible to use a Python Operator in Data Intelligence Pipelines for parsing the XML payload.

With the help of the Python ElementTree XML API library, you can browse through the XML document structure and extract attributes and/or values for all nodes on all levels. Here is the documentation.

The general approach is

1. Import the library (import xml.etree.ElementTree as ET)
2. Create an ElementTree object (e.g. xml_tree = ET.fromstring(<payload of the file>)
3. You can then get values for individual nodes, e.g. with xml_tree.find('.//<name_of_a_node>').text
4. The entire XML document can be parsed for the relevant node contents using nested loops iterating over arrays of nodes of a specific name. These arrays can be populated in Python, e.g. with: node_name_array = xml_tree.findall('.//<top node name>/<first child note name>'), etc.
5. Either flatten the entire paylod content (which may generate high volumes of redundant data for deep hierarchical XML structures), or turn it into a relational structure, e.g. with data from the parent nodes getting loaded into header tables and data from their children nodes loaded into item tables together with a unique reference (e.g. GUIDs generated leveraging the uuid library for Python) to their related parent node IDs/keys.

The library documentation linked above is a nice source for guidance covering various different use cases with many example code snippets.

Implementing code in Python Operators may not be the most convenient approach, but the treatment of nested hierarchical XML structures isn't really trivial and can't easily be provided by standard operators.

Kind Regards,

Bengt

[michal_majer]

Hey Indu,

You could try to use Format Converter operator to convert file from XML to CSV and then process.

https://help.sap.com/viewer/97fce0b6d93e490fadec7e7021e9016e/Cloud/en-US/c5b81c724d0b4000afd1afad214...

Best,

Michal

[amish1980_95]

Hello Michal,

Thank you for your answer.I have tried this approach using format converter but the issue is with attributes and missing nodes in the deep XML structure. With a structure like below, the value of attribute at third level is captured in json through format converter but is not passed into CSV:

<root>

<child attr = "">

<subchild>.....</subchild>

</child>

<child attr = "">

<subchild>.....

<sub-subchild>

<sub-sub-sub child attr ="">

<element1>mm</element1>

<element2>nn</element2>

</sub-sub-sub child>

</sub-subchild>

</subchild>

</child> </root>

In hierarchical structure of the XML.

My requirement is to parse the xml and load the data in HANA table.

Thanks for your help!

[michal_majer]

Hey,

If You want to load data to HANA, json format is enough.

SAP HANA Client operator allows You to insert data in csv or json format.

Good Luck 🙂
MM

[amish1980_95]

Hello MM 🙂

Thans for your answer.

Indeed, with Hana Client, we can insert json file, but the schema of the table in hana client has to be created manually unlike 'table producer' operator which is dynamic.

In my case files are deep, hierarichal and > 44 mb in size.

Please suggest.

Thanks,

IK.