Reading non unique XML nodes

[#1]

Hi all, I have an XML document where the "cell values" are not unique.  Please see below.  My question is, how can I create a Qlik table that with proper associations?  I'd need the table to look like:

Value, Frequency, Distribution

0, 18593, 92.965

U, 710, 3.550

Using the standard XML table file load, all values contained in the node "cell" get thrown into 1 field called value.....

Thanks!

-Dan

Raw XML

________________

<?xml version='1.0' encoding='utf-8'?>

<table title='Values' subject='Attribute = First Lien Submission 01 2014(76).Foreclosure Status' username='WIN7DEMO-VM\dscadmin'>

  <columns>

             <column name='VALUE' title='Value' description='The data value' width=''/>

             <column name='FREQUENCY_COUNT' title='Frequency' description='The number of times the data value occurs in the attribute' width=''/>

            <column name='_distribution' title='Dist %' description='A measure of how much of the attribute contains the data value' width=''/>

  </columns>

  <rows>

       <row>

                <cell value='0'/>

                <cell value='18593'/>

                <cell value='92.965'/>

  </row>

  <row>

                 <cell value='U'/>

                 <cell value='710'/>

                 <cell value='3.550'/>

  </row>

</rows>

</table>

[#2]

Also, as a note, I do not have control of the XML format or schema.  Thanks!

[rbecher · Accepted solution]

Hi Dan,

if the sequence is constant you can numer the rows and then use a generic load (with table consolidation):

GenTable: 
Generic LOAD 
    Ceil(RecNo()/3) as ID, 
    Pick(If(Mod(RecNo(),3)=0, 3, Mod(RecNo(),3)), 'Value', 'Frequency', 'Distribution') as field,
    value
FROM [RawXML.xml] (XmlSimple, Table is [table/rows/row/cell]);

ResultTable:
NoConcatenate LOAD ID Resident [GenTable.Value];

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'GenTable.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
NEXT i

DROP TABLE TableList;

- Ralf