XML parsing

[lavorarchi]

Hi I was wondering someone will be able to help me with parsing the attached XML (which I had to save it as a txt file to get around the upload restriction on the site)?

The attached XML comprises of a few key sections, but the one I am interested in extracting are within the <cbc:CbcBody> parent. There are children and siblings underneath - which is also something I need to extract. For example <cbc:CbcReports> is repeated multiple times for each reporting country. When I parse using the Parsing tool, it is not picking up either CbCBody or CbcReports components. 

Thank you for your help in advance.

[jamielaird · Accepted solution]

Hi @lavorarchi ,

The exact approach will depend on what you want to extract, but I've attached an example showing the CbcBody and CbcReports elements extracted for the sample XML file you provided.

For this type of task I'd always recommend starting with a clear idea of the structure of your XML file. Sites like https://codebeautify.org/xmlviewer are useful for this.

From here we can see that the currency values (which I assume are something you want to extract) are in the following location:

CbcBody > CbcReports > Summary

The first tool in the workflow extracts the Outer XML for the CbcBody element. The result is one line as there is only one instance of the CbcBody element.

From this, we can extract the CbcReports element, which gives us three lines, one for each CbcReports element:

Next we extract the Summary element from within the CbcBody_OuterXML field which gets us to the more granular numbers.

You might want to extract multiple elements in this way, and then join them back together using the ResCountryCode field (which gives you three values, each relating to the three CbcReports elements).

It's a pretty iterative process and takes a bit of exploration but I hope this gives you some pointers on how to get started.

[lavorarchi]

That is brilliant thank you!