Solved: Re: Load from XML-file - Qlik Community

martinpedersen · ‎2014-12-11

I got some data in some XML-files that i would like to load into Qlikview. The files are to many in size, frequency and volume to allow me to change the format of them. So i need to find a way to read into this files as they are.

The basic structure of these files are as follows.

File1.xml:

<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>
<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>
<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>
<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>

When I create the load script for File1 as:

Table1:

LOAD Field1, Field2, Field3 From File1.xml (XmlSimple, Table is

);

I only get the first row from the XML-file - and i need to find a way get all the rows in one table.

In the File Wizard i can only select the "Table" as table - and it shows that each field contains 1 value (but in the file the table are repeated multiple times with different field values).

Hope someone can help on how to configure my script to allow me to load all the rows from the file.

flipside · ‎2014-12-11

Hi Martin,

The XML is poorly formed. You need a root element in the XML ...

<head>

<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>

</head>

If you cannot get the xml files amended, then you will need to parse the rows as a normal text to strip out the elements, or another option if the files aren't too big is to output the text with a new root ...

Raw:
LOAD @1 as '<head>'
FROM
[XMLBAD.xml]
(txt, codepage is 1252, no labels, delimiter is ' ', msq);

Load '</head>' as '<head>' autogenerate 1;

store Raw into XMLFIXED.xml (txt);

The new file will load as xml, but if the files are too big this could be a lengthy process.

flipside

View solution in original post

flipside · ‎2014-12-11

Hi Martin,

The XML is poorly formed. You need a root element in the XML ...

<head>

<Table><Field1>Value</Field1><Field2>Value</Field2><Field3>Value</Field3></Table>

</head>

If you cannot get the xml files amended, then you will need to parse the rows as a normal text to strip out the elements, or another option if the files aren't too big is to output the text with a new root ...

Raw:
LOAD @1 as '<head>'
FROM
[XMLBAD.xml]
(txt, codepage is 1252, no labels, delimiter is ' ', msq);

Load '</head>' as '<head>' autogenerate 1;

store Raw into XMLFIXED.xml (txt);

The new file will load as xml, but if the files are too big this could be a lengthy process.

flipside

martinpedersen · ‎2014-12-11

Hi Flipside,

Thank you very much. It looks like something i could work with.

I know the XML-file are rubish, but it is unfortunately the only way I can get this third party data.

/Martin