. Get Rowdata from an XML file in BIRT Report

 

You are trying to reproduce a report, with BIRT, of this kind:

Scenario:

  • A list of items (product categories).
  • Each item (or category) have a fixed number of tables (the table of the product_id vs sold_sold quantity, and a table with some information about the category)
  • Each table inside a category has a variable number of rows.

Data structure:

You read data from an XML, it is something like this:

<list>
     <item>
          <table1>
              <row>
                  <column1>item 1 table 1 row 1 col 1</column1>
                  <column2>item 1 table 1 row 1 col 2</column2>
              </row>
              <row>
                  <column1>item 1 table 1 row 2 col 1</column1>
                  <column2>item 1 table 1 row 2 col 2</column2>
              </row>
          </table1>
          <table2>
              <row>
                  <columnX>item 1 table 2 row 1 col 1</columnX>
                  <columnY>item 1 table 2 row 1 col 2</columnY>
                  <columnZ>item 1 table 2 row 1 col </columnZ>
              </row>
              <row>
                  <columnX>item 1 table 2 row 2 col 1</columnX>
                  <columnY>item 1 table 2 row 2 col 2</columnY>
                  <columnZ>item 1 table 2 row 2 col 3</columnZ>
              </row>
          </table2>
    </item>
    <item>
    .
    .
    </item>

Desidered result:

+---------------------------+---------------------------+
|  column 1                 |  column 2                 |
+---------------------------+---------------------------+
|item 1 table 1 row 1 col 1 |item 1 table 1 row 1 col 2 |
|item 1 table 1 row 2 col 1 |item 1 table 1 row 2 col 2 |
.
|item M table 1 row N col 1 |item M table 1 row N col 2 |
+---------------------------+---------------------------+

+---------------------------+---------------------------+---------------------------+
|  column X                 |  column Y                 |  column Z                 |
+---------------------------+---------------------------+---------------------------+
|item 1 table 2 row 1 col 1 |item 1 table 2 row 1 col 2 |item 1 table 2 row 1 col 3 |
|item 1 table 2 row 2 col 1 |item 1 table 2 row 2 col 2 |item 1 table 2 row 2 col 3 |
.
|item M table 2 row N col 1 |item M table 2 row N col 2 |item M table 2 row N col 3 |
+---------------------------+---------------------------+---------------------------+

Using esProc as Datasource:

Here is the esProc SPL script


A
1 =file("./nestedTable.xml").read()
2 =xml(A1,"list/item/table1")
3 =A2.new(#:ItemID,row)
4 =A3.news(row;ItemID,row.column1:column1,row.column2:column2)
5 =xml(A1,"list/item/table2")
6 =A5.new(#:ItemID,row)
7 =A6.news(row;ItemID,row.columnX:columnX,row.columnY:columnY,row.columnZ:columnZ)


A2:

imagepng

A3:

imagepng

A4:

imagepng

A6:

imagepng

The report can be designed in the same way as you would if you were retrieving the data from a database. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.

For more examples, procedure XML files refer to the following XML data parsing and calculation.

The sample data can be downloaded, nestedTable.xml.zip. If you have any questions or comments please leave them below.