Read all columns from an XML (Load/Select * from...)

[HTaher]

Hi Dear Community,

Is there a way to read all columns from an XML file?

I know that Load * or Select * does not work with XML.

I have the problem that I get a XML file where the number of columns change. The number can become larger and larger.

I would like to use a select * to save all rows in a QVD.

Maybe you have an idea how I can solve the problem.

Thanks a lot

Handren

[marcus_sommer]

Just load the xml as txt-file before your origin load and extract the included columns. Qlik has very powerful string-functions like subfield/substringcount/textbeween/keepchar/... which could be also applied within several internal loops in preceding loads.

The general approach would be cleaning --> extracting --> final cleaning --> string-aggregating the results as field-list for the final load. If the xml is rather large and quite nested it's a bit tedious but surely possible. More simple would be if the source-system output another file with the meta-data to the data.

[HTaher]

I now have a CSV file from the source system with all possible column names from the XML file.

How can I take the rows from the CSV file as column names in a load script for the XML file?

[marcus_sommer · Accepted solution]

Maybe in this way:

t: load concat('[' & Fieldnames & ']', ', ') as F from csv;

let f = peek('F', 0, 't');

xml: load $(f) from xml;

[HTaher]

Thank you very much.

You have helped me a lot. This is what worked.

Greetings