topic Re: Read all columns from an XML (Load/Select * from...) in App Development

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

HTaher — Mon, 24 Jul 2023 08:35:24 GMT

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

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

marcus_sommer — Mon, 24 Jul 2023 10:45:26 GMT

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.

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

HTaher — Mon, 28 Aug 2023 13:33:24 GMT

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?

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

marcus_sommer — Mon, 28 Aug 2023 13:39:35 GMT

Maybe in this way:

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

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

xml: load $(f) from xml;

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

HTaher — Mon, 28 Aug 2023 13:51:54 GMT

Thank you very much.

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

Greetings