Load multiple XML files

Wed, 08 Jun 2011 07:23:46 GMT

Hi Everybody,

I want to load all xml files from a directory (later from ftp server). This is the script I got with the wizard :

Directory;
Directory;
// Start of [110511010000_emn9report.xml] LOAD statements
'REPORT/HISTORY/data':
LOAD data%Table,
    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY
FROM donnees\elec_villept\110511010000_emn9report.xml (XmlSimple, Table is [REPORT/HISTORY/data]);
'REPORT/HISTORY/data':
LOAD date,
    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY
FROM donnees\elec_villept\110511010000_emn9report.xml (XmlSimple, Table is [REPORT/HISTORY/data]);
HISTORY:
LOAD Name,
    Var,
    %Key_REPORT_BE57120470E6A47F,    // Key to parent table: REPORT
    %Key_HISTORY_578652BE74995C98    // Key for this table: REPORT/HISTORY
FROM donnees\elec_villept\110511010000_emn9report.xml (XmlSimple, Table is [REPORT/HISTORY]);
REPORT:
LOAD TITRE,
    [EMN_Param/TBoxMS16/name] as name,
    [EMN_Param/TBoxMS16/Id] as Id,
    [EMN_Param/TBoxMS16/Status] as Status,
    [EMN_Param/TBoxMS16/RSSI] as RSSI,
    [EMN_Param/TBoxMS16/Caliber] as Caliber,
    %Key_REPORT_BE57120470E6A47F    // Key for this table: REPORT
FROM donnees\elec_villept\110511010000_emn9report.xml (XmlSimple, Table is [REPORT]);
// End of [110511010000_emn9report.xml] LOAD statements

and this is what I added to get what I want :

table_donnees_villepinte:
LOAD Distinct
          date(daystart(date)) as Date,
          MakeTime(Hour(Time(date))) as Heure,
          num((data%Table/200),'0,000') as Index,
          num(month(date)) as Num_Mois
RESIDENT donnees WHERE Minute(time(date)) = 0 and (%Key_HISTORY_578652BE74995C98 = 882 or %Key_HISTORY_578652BE74995C98 = 880);
join (table_donnees_villepinte) load Id as Num_Compteur Resident REPORT;
DROP Table 'REPORT/HISTORY/data';
DROP Table HISTORY;
DROP Table REPORT;

The problem is that if I modify 110511010000_emn9report.xml by *.xml (files 110511010000_emn09report.xml, 110511010000_emn10report.xml, ... are present)

I got :

I know that this is logic (because of join statement ) but I don't know how to avoid this problem of "double line" with different "Num_Compteur"

Should add the clause "Where" or sould I implement a function like "for each file in directory...." with VB ?

topic Load multiple XML files in QlikView

Load multiple XML files