Apex: How to get all child element of an xml? - Parsing xml via XmlStreamReader

Question

I am trying to parse an MSXML which is a response from a web services via XmlStreamReader.

The below code is the response from the web service:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> 
    <Worksheet ss:Name="Report"> 
        <Table> 
            <Row> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project Name</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project Code</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project URI Report Setting</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Entry Date</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Actual Billable Hours (Selected Dates)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Actual Non-Billable Hours (Selected Dates)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Name</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Default Billing Rate</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Default Billing Rate (BC)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Timesheet Start Date</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Timesheet End Date</Data> 
                </Cell> 
            </Row>
        </Table>
    </Worksheet>
</Workbook>

I wanted to get all the cell(s) in every row

Here is my code on how do I parse the response (assume that reportXML is the response):

 class Cell {
      string data { get; set; }
 }

 XmlStreamReader xsr = new XmlStreamReader(reportXML);

 while(xsr.hasNext()) {
      if (xsr.getEventType() == XmlTag.START_ELEMENT) {
           if (xsr.getLocalName() == 'Row') {
                Cell cell = parseCell(xsr);
                system.debug(cell);
           }
      }
      xsr.next();
 }

 Cell parseCell(XmlStreamReader reader) {
      Cell cell = new Cell();

      while(reader.hasNext()) {
           if (reader.getEventType() == XmlTag.END_ELEMENT) {
                break;
           }
           else if (reader.getEventType() == XmlTag.CHARACTERS) {
                cell.data = reader.getText();
           }

           reader.next();
      }

      return cell;
 }

The problem is I am only getting a single cell and not all the cell. Cell:[data=Project Name] is the only data from the debug logs which I think the first column cell only.

Why? and what is the code that bugs the logic for displaying all cell data?

EDIT:

I changed the 'Row' into 'Cell' on the condition for comparing localName. And I am now getting all the cell but how can I check if this belongs to the specific row ?(e.g., row at index 2).

Answer 1

I figure out how can I do it.. I just wanted to share my answer, see full code below:

 class Cell {
      string data { get; set; }
 }

 Integer rowCounter = 0;
 List<Cell> cellDataList = new List<Cell>();
 Map<Integer, List<Cell>> rowDataMap = new Map<Integer, List<Cell>>();

 XmlStreamReader xsr = new XmlStreamReader(reportXML);

 while(xsr.hasNext()) {
      if (xsr.getEventType() == XmlTag.START_ELEMENT) {

           if(xsr.getLocalName() == 'Row') {
                rowCounter++;
                rowDataMap.put(rowCounter, cellDataMap);
                cellDataList = new List<Cell>();
           }
           if (xsr.getLocalName() == 'Cell') {
                Cell cell = parseCell(xsr);
                cellDataList.add(cell);
           }

      }
      xsr.next();
 }

 Cell parseCell(XmlStreamReader reader) {
      Cell cell = new Cell();

      while(reader.hasNext()) {
           if (reader.getEventType() == XmlTag.END_ELEMENT)
                break;

           else if (reader.getEventType() == XmlTag.CHARACTERS)
                cell.data = reader.getText();

           reader.next();
      }
      return cell;
 }

 for (Integer rowKey : rowDataMap.keySet()) {
      if (!rowDataMap.get(rowKey).isEmpty()) {
           system.debug('===============ROW Start====================');
      for (Cell cells : rowDataMap.get(rowKey)) {
                system.debug(cells);
           }
           system.debug('===============ROW End====================');
      }
 }