Creating a relational XML file

Hi guys

I want to create an XML file from 3 files, 1 main file & 2 detail files.
i'd like the output to be as below: (hopefully you get the idea, the detail tables are nested into each record)


I seem to have managed to do it but I have some 300 fields...

I have added tags to each field in the tables, so my urn for example now says "1"
I did the same prep to the detail tables. I then derive a table from the main file, aggregate the urn & iterate the main. in my module I load the details tables & filter to the record i'm on and write in the order I want to a text file (appending as I go) this gives me the output I need.

is there an easier way? (i guess there is but i'm no expert in XML) Having to write all the tag names is manual and time consuming - is there a way to reference the field name? so instead of "&[urn]&" it would become &[urn]&

any help would be appreciated, your software rocks BTW - I've been using it for nearly a year now & just gets better the more i learn

best regards, Richard

Hi Richard,

It's a bit hard to understand your goal from the description.Can you post a sample source file and the corresponding result XML file?

Thank you! :slight_smile:

createXML.morph (16.8 KB)

details.xlsx (11.4 KB)
detailWithTags.xlsx (5.9 KB)
mergerXML.txt (2.6 KB)
mergerXML.xml (2.7 KB)
Hi there, please see examples attached. The idea is to load the source files once on the source tab. from there I unpivot and use the field name for the tag, I then pivot back, as the fields do not come back in the original order the next table just puts it back into it original order. I then create a table with all the unique numbers (this will be a member number) and iterate the name & address file.

write xml just sets up the main tags around the record and detail lines - the result is written to mergerXML.txt . I have added the header and main data tag manually, so the result is the xml file.

is there a better way to do this? Also, the live files are some 30K records with maybe 100K transaction lines, lots of fields as well so big files. I have started loading the source once and just derive from there when I need another copy as it seems a lot of time is used reading/writing, in the writexml module, I pass the name & address file but it will have to open a xls file (the detail table) some 30K times, it will be a big file so im concerned how much time this will take to run.

I would really be grateful for any advice you have, thanks for your time, best regards, Richard

Hi Richard,

Thank you for the well-prepared example. Here is my version of the workflow. It's somewhat simpler, only writes into an XML file once, and doesn't use iterations.

The logic works as follows:

  • Start from the deepest nested XML level
  • Use the "Concatenate text" to concatenate multiple rows or XML elements
  • Append XML subtrees as columns
  • Use the "Export as plain text" to write into an XML file

createXML.morph (10.2 KB)

Tip: Use the Cell Profiler to view multi-line text values. To open the Cell Profiler, right-click a cell and select "Profile". The Cell Profiler can remain open while you click other cells.

Hi there, sorry for the delay... This job doesn't come in for a few weeks yet so i'm just getting the outline of the process in place...

just read through the example you sent me. AWESOME !!! I have another table to merge in there, the way you have set this up is very clear to understand and adjust.

Reading files the way I did I was worried about the time it would take, this way would appear to 'fly' like most of the processes I run in EZ...

thank you so much for your time looking into this, I've said it before, the more I learn ,the more I love this software.

best regards, Richard

1 Like