Converting XML structured data to Tables

[Sisay_Dinku (Sisay YDG)]

Hi team,

I have the following XML structured data that I need to convert tables.

I’ve made the following attempts and it does not seem to be working

The ultimate goal is to have a table with the following columns

Can anyone help with the steps needed? Please let me know if you’d like more information.

Thank you

[Yoichi]

Hi,

Can you share your xml file as a file? It’s no problem if dummy data.

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi

The structure of the actual XML data looks like the screenshot in the above.
Here is sample data
XMLSample.txt (3.9 KB)
Please let me know if you’d need more information. Thanks!

[Yoichi]

Hi,

Can you try the following sample? I modified the xml file a little because it’s not valid xml.
And this sample assumes there is always an element for each target.

Sample20230816-2L.zip (11.5 KB)

Regards,

[Sisay_Dinku (Sisay YDG)]

@Yoichi

It works for some of the columns

However, Action_Event, Location_Hierarchy, location columns pull from the second line of the XML data. the data need to be loaded from the first line.

Thanks!

[Yoichi]

HI,

Can you clarify which element and attribute you need?

Regards,

[Yoichi]

Hi,

If you need to extract Location_Hierarchy which has type attribute is “Organization_Reference_ID” and location which has type attribute is “Location_ID”, the following will help you. (I cannot get which attribute should extract about Action_Event from the above sample.)

Sample20230816-2L (2).zip (11.9 KB)

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi

It works with the attributes you added as an example. However, for those three columns (Action_Event, Location_Hierarchy, location ), the values in front of descriptors are needed.
From → Original XML structure

From → XML data in Txt
Overall, the underlined ones are the values we would like to extract and populate into our table under respective columns.

I look forward to your suggestions.

[Yoichi]

Hi,

Can you share input xml and expected result as file?

Regards,

[Sisay_Dinku (Sisay YDG)]

Thanks @Yoichi

here is the source XML (the same file I shared yesterday)
XMLSample.txt (3.9 KB)
here is the expected result
ExpectedResult.xlsx (10.4 KB)
Thanks a lot!

[Yoichi]

HI,

Thank you for sharing. How about the following?

Sample20230816-2L (3).zip (11.8 KB)

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi

This is awesome. I appreciate the help!