XML Parse/Preparation

[MD2050]

Hi-

I am struggling with post-xml parse issues and would really appreciate some direction to resolve the same. 

Below is the XML file I am trying to parse , ingesting the file as-is and using the XML formatter the fields in yellow comes through fine - I then have to then use xml parser for a different field <aud_changes> which which has other information clubbed in ,as can be seen in the snippet below. I am able to parse that too and the 2nd snippet below shows the end result. However what i am struggling is the fields are not aligned in the output, meaning for the 2nd record aud_key=5378961 the <version> column is populated with old and new dates but that information is put in under <key_old> and <key_new> column. Is there any way i can get around this issue? Below is just an example actual data which has 80 column fields and a million rows. Thank you very much. 

[Jean-Balteryx]

Hi @MD2050,

Are you able to provide the XML file so I can test it ?

[MD2050]

Thank you for the reply-

Apologies, I can't send the original file although I attached the sample data with 2 records i mentioned in the original thread. Thanks again. 

[Jean-Balteryx]

Here is a workflow I built. Tell me if I understood correctly and the result suits your need !

[MD2050]

Hello @Jean-Balteryx  -

Thank you very much for sharing the AWF, I was able to achieve what you got before getting to the stage i posted in the excel earlier. Issue I am struggling with is to align the correct fields throughout the data once the "child" values are extracted from the "root".

Not all (60) fields are populated all the time. Say for example for one records <key> , <version> fields are populated along with old and new values and for the second record only <version> field is populated with old and new values then the data pertaining to the 2nd record should correctly align under the <version> fields instead of <key> fields. 

Thanks alot. 

[Jean-Balteryx]

So you would have four fields : key_old_value, key_new_value, version_old_value and version_new_value ?

[MD2050]

Gm @Jean-Balteryx -

Sincere apologies for late reply.

Yes, in the example we are talking ; however actual data set has 60+ different fields and not all the fields have values. And the XML is not structured accordingly , meaning the xml data only has fields which has "changes" associated to it and hence when i 
start to parse the data it is not correctly aligned by the fields. 

Thank you.

[Jean-Balteryx · Accepted solution]

Hi @MD2050,

No problem! I think using the following steps could work :

   - transpose old and new values fields

   - concatenates values from fields containing "key" and "version" values with the transposed one

   - crosstabs the new field

I can't do it right now but I'll do when I can if you need. 

[MD2050]

Thank you @Jean-Balteryx  -

I followed the steps and it worked out perfectly. Thanks MD.

[MD2050]

I do have a follow-up question which may have a simpler solution .. In the current file i have there are around 234 fields (after parsing) in total which has <old> and <new> attributes . I concatenated the old/new fields to convert it as 1 field and then transposed it followed by splitting the concatenated fields using the "Text-to-Column" tool.

However, the number of fields varies meaning there may be 250 fields tomorrow ,so can there be an easier way to dynamically split them as the new fields come through ? I split each field using the tool 234 times as it was a time sensitive deal. I am working on creating a batch  macro with no luck yet - thought there may be a simpler solution. Thanks again.