Script to load data from XML file

[#1]

Hello Everybody,

I have the following xml file :

<REPORT TITRE="AAA">
     <Param>
          <Box name="azerty" Id="17" />
     </Param>
     <HISTORY Name="xyz4" Var="Var1">
          <data date="04/05/11 01:00:00">76994</data>
           <data date="04/05/11 02:00:00">76995</data>
          <data date="04/05/11 03:00:00">76996</data>
              ...
     </HISTORY>
     <HISTORY Name="xyz4" Var="Var2">
          <data date="04/05/11 01:00:00">76997</data>
           <data date="04/05/11 02:00:00">76998</data>
          <data date="04/05/11 03:00:00">76999</data>
              ...
     </HISTORY>
               ...
</REPORT>

My question is to know, how to get a table with fields date time data and Id where Var = Var1?

I tried many things like this :

LOAD date,
     data,
     Id
FROM [file.xml] (XmlSimple, Table is [REPORT])
WHERE Var='Var1';

Thank you,

Jérémy

[rbecher]

Something like this:

data:

LOAD data%Table as data,

    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY/data]);

JOIN LOAD date,

    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY/data]);

INNER JOIN LOAD

          %Key_REPORT_BE57120470E6A47F,

    %Key_HISTORY_578652BE74995C98    // Key for this table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY])

Where Var='Var1';

INNER JOIN LOAD [Param/Box/Id] as Id,

    %Key_REPORT_BE57120470E6A47F    // Key for this table: REPORT

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT]);

// End of [test.xml] LOAD statements

Drop Field %Key_HISTORY_578652BE74995C98, %Key_REPORT_BE57120470E6A47F;

- Ralf

[ToniKautto]

You should load by using the [Table Files...] from the script editor, and them select file type XML. Here you will see what table your XML contains, which for the sample code means the table with Var is REPORT/HISTORY and not REPORT. In the Load as specified below you can add the WHERE clause for Var;

HISTORY:

LOAD Name,

    Var,

    %Key_REPORT_BE57120470E6A47F,    // Key to parent table: REPORT

    %Key_HISTORY_578652BE74995C98    // Key for this table: REPORT/HISTORY

FROM data.xml (XmlSimple, Table is [REPORT/HISTORY]);

[#4]

As a followup to this - I prefer to be able to not have to auto-generate the script - is there a way to get the long hex number without having to generate it from the script? Also will this number change depending on the content of the xml?