XML with "column headers" as Metadata

[#1]

It's been a long time since I've had to do XML parsing. I have XML data from a Cognos report that has a metadata section with items containing the equivalent of table header names. I feel close to solving this, but am probably missing something simple and am getting sleep deprived. My current attempts are around numbering the header metadata with RowNo() and then trying to match the Items back since all metadata header items are represented in my rows of data.

Any suggestions on how to read this data?

[Gysbert_Wassenaar]

Try this:

item:

LOAD name,

    type,

    length,

    precision,

    RowNo() as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/metadata/item]);

LET vMax = Peek('ValueRowNo',-1,item);

'dataset/data/row/value':

LOAD value%Table,

    if(rowno()>1 and peek('ValueRowNo')< $(vMax) ,peek('ValueRowNo')+1,1) as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/data/row/value]);

[#3]

That's pretty close but I need to keep the <row></row> groupings together. Is there some way when reading the row/values to increment a counter for each row?  That should let me group on that row counter.

Rick

[Gysbert_Wassenaar]

item:

LOAD name,

    type,

    length,

    precision,

    RowNo() as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/metadata/item]);

LET vMax = Peek('ValueRowNo',-1,item);

'dataset/data/row/value':

LOAD value%Table,

    if(rowno()>1 and peek('ValueRowNo')< $(vMax) ,peek('ValueRowNo')+1,1) as ValueRowNo,

    ceil(RowNo()/$(vMax)) as Row

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/data/row/value]);