Join XML Tables without automatic generated Field

[AxNoxy]

Hi there everyone,

I'm having an issue loading many XML's and joining them. The load process generates fields to be the keys of the many tables that my XML(many of them in fact)  must have. But, as I'm loading multiple XML the generated field may change the name for each new XML.

Scenario

MasterTable:

LOAD
Name          AS TableName,
%Key_DataSource_8E3A82EF69BDD2B6    AS MyTableKey
FROM  [lib://TestObtenArchivo (MYUSER)/Tipo 6.txt]
(xmlSimple, table is [Evaluacion/DataSource);

LEFT JOIN(MasterTable)

LOAD
Name          AS FieldName,
Value           AS FieldValue
%Key_DataSource_8E3A82EF69BDD2B6    AS MyTableKey, (This  field may change in the next load of a XML)
%Key_CharacteristicName_E36D3558627ED51C AS MyFieldKey (This  field may change in the next load of a XML)
FROM  [lib://TestObtenArchivo (MYUSER)/Tipo 6.txt]
(xmlSimple, table is [Evaluacion/DataSource/ListaCharacteristicName/CharacteristicName]);

Already known that those fields are generated automatically by each XML to make the correct join, but, in this case I need to LOAD a lot of XML where we have the next dis vantages.

• The tables names are in the first load.
• The field names and values are in the second load.
• The Key between them its generated dynamically (In load I cant catch the new name of the keyfield_########).
• We can't use @FieldPosition to get the position of the key and rename it.
• We can't travel to upper levels, using the child node ([Evaluacion/DataSource/ListaCharacteristicName/CharacteristicName]) as a base table to call on the LOAD [Evaluacion/DataSource].
• The fields names in the second load can be repeated (Any table loaded can have a field with the same name).

Pros:

• Already have a xlsx file with the Tables and Fields correctly sorted.

At this point I cant find a solution for this, it will be very appreciated if I can find a guidance on this. @Gysbert_Wassenaar @Michael_Tarallo 

So much thanks in advance seriously.

[AxNoxy]

@rwunderlich  mentioned you because I saw some of your responses on other similar posts, hope you having a couple of minutes.

Thanks in advance!

[Gysbert_Wassenaar]

I think you'll have to use a loop to iterate through the individual files with a For Each loop using the FileList function to get the list of files. In each iteration you load the data from the xml file, join the two tables. Then drop the key field from the resulting table. Next concatenate all the records to a third table and drop the MasterTable.

[AxNoxy]

Hi there Gysbert, first, thank you for you response, and about it In having the struggling joining the two tables in the loop, this because the key generated changes their name on every new XML key_6262727 , key_662828 and go on. Still ya en found the solution.

Hope you having a nice day.