You have data from a standard database. But you need to join additional data from an XML source.

For example, cities table is from a database:

CID           NAME          POPULATION    STATEID
-----         -----         -----         -----
1             New York      8084316.0     32
2             Los Angeles   3798981.0     5
3             Chicago       2886251.0     13
4             Houston       2009834.0     43
5             Philadelphia  1492231.0     38
6             Phoenix       1371960.0     3

state table is from an xml file:

<?xml version="1.0" encoding="utf-8"?>
<data> 
  <state> 
    <STATEID>1</STATEID>  
    <NAME>"Alabama"</NAME>  
    <ABBR>"AL"</ABBR> 
  </state>  
  <state> 
    <STATEID>2</STATEID>  
    <NAME>"Alaska"</NAME>  
    <ABBR>"AK"</ABBR> 
  </state> 
</data>

The output you want:

STATE         POPULATION
-----         -----
Alabama       431422.0
Alaska        278700.0

Solution:

1) You can use a Scripted Data Source and use java to parse the XML.

1. Define an XML Datasource or a scripted Datasource if the XML file isn’t supported by original XML Datasource.
2. Define XML dataset or scripted dataset of the previous Datasource.
3. Define dataset from database Datasource.
4. Define joint dataset with two datasets.
5. create a crosstab from the joint dataset, with the new dataset columns as the data cube groups.
6. Define the output column.

2) You can use esProc as Data Source and use SPL to join Database with XML Datasource.

Here is the SPL script.

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.

If you have any questions or comments please leave them below.