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.
LOAD Name AS TableName, %Key_DataSource_8E3A82EF69BDD2B6 AS MyTableKey FROM [lib://TestObtenArchivo (MYUSER)/Tipo 6.txt] (xmlSimple, table is [Evaluacion/DataSource);
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).
Already have a xlsx file with the Tables and Fields correctly sorted.
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.
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.