Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

martinpedersen
Not applicable

Load from XML-file

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.

Tags (3)
1 Solution

Accepted Solutions
flipside
Not applicable

Re: Load from XML-file

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> 

<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> 

</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

2 Replies
flipside
Not applicable

Re: Load from XML-file

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> 

<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> 

</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
Not applicable

Re: Load from XML-file

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