2 Replies Latest reply: Dec 11, 2014 1:26 PM by Martin Pedersen RSS

    Load from XML-file

    Martin Pedersen

      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 [Table]);
      

       

      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.

        • Re: Load from XML-file
          Dave Riley

          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