Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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