45 Building a Report with an XML Pluggable Data Source

In this chapter, you will learn about reports that include data from an XML pluggable data source (PDS), which is provided with Oracle Reports. By following the steps in this chapter, you can generate the report output shown in Figure 45-1.

Figure 45-1 Report output using an XML PDS

Description of ''Figure 45-1 Report output using an XML PDS''

Concepts

The information you must publish is often derived from data in various corporate data sources. These data sources may be SQL-based (relational databases) or non-SQL-based, such as XML. Often, you must combine data from one or more of these data sources to publish meaningful information. For example, you may need to combine data that exists in a relational database with data from a multidimensional database to compare trends and performance.
Oracle Reports enables you to leverage capabilities, such as aggregation, summarization, formatting, and scheduling, on data from any data source. You can leverage the pluggable data source (PDS) architecture to connect to your own data source, as well as to the data sources available with Oracle Reports (XML, JDBC, and Text).
Valid XML files have a document type definition (DTD) or XML schema and strictly adhere to it. XML files can come from any source. However, Oracle provides you with a number of utilities and methods to convert different types of data to XML data files and their DTD or schema.

The elements in your XML data file (data source) must follow the same sequence and format of elements specified in the DTD or XML schema file. For example, suppose your DTD or XML schema defines two elements: WAREHOUSE_ID first, and PRODUCT_ID second. In this scenario, WAREHOUSE_ID must come before PRODUCT_ID in your XML data file, too. The names of the elements do not need to match. For example, given the following XML data file:

<WAREHOUSE>
  <INVENTORY>
    <WAREHOUSE_ID>3</WAREHOUSE_ID>
    <PRODUCT_ID>2340</PRODUCT_ID>
    <QUANTITY_ON_HAND>69</QUANTITY_ON_HAND>
    <PRODUCT_NAME>Chemicals - SW</PRODUCT_NAME>
  </INVENTORY>
  <INVENTORY>
    <WAREHOUSE_ID>3</WAREHOUSE_ID>
    <PRODUCT_ID>2365</PRODUCT_ID>
    <QUANTITY_ON_HAND>73</QUANTITY_ON_HAND>
    <PRODUCT_NAME>Chemicals - TCS</PRODUCT_NAME>
  </INVENTORY>
</WAREHOUSE>

A valid XML schema with suffix xsd for the above XML is as follows:

</xsd:schema>
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
<xsd:element name="WAREHOUSE" >
<xsd:complexType>
  <xsd:sequence>
    <xsd:element name="INVENTORY" minOccurs="0" maxOccurs="unbounded"
    <xsd:complexType>
      <xsd:sequence>
      <xsd:element name="WAREHOUSE_ID" type="xsd:integer"/>
      <xsd:element name="PRODUCT_ID" type="xsd:integer"/>
      <xsd:element name="QUANTITY_ON_HAND" type="xsd:integer"/>
      <xsd:element name="PRODUCT_NAME" type="xsd:string"/>
      </xsd:sequence>
    </xsd:complexType>
    </xsd:element>
  </xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

The XML PDS implementation supports only two-dimensional listing of records. Oracle Reports expects the XML data file to be in simple table format with rows and columns. Oracle Reports iterates through the XML sequence at one level below the topmost element in the XML. If there are sequences at lower levels (nested elements), they are not handled. Thus, to generate a tabular report, you must "flatten" your XML file into simple row-column format, as shown in the following examples.

Example 45-1 shows an XML data file that includes G_DEPTNO elements with nested G_EMPNO elements. Oracle Reports parses all the departments, but not all the employees within each department. For each department, only one employee record would be shown.

Example 45-2 shows how you can modify this XML data file to "flatten" the data. In the example, you will see that the G_DEPTNO elements include the G_EMPNO data, without nesting. Now, Oracle Reports parses all the departments, including all the employees within each department.

Example 45-3 and Example 45-4 show the corresponding XML schema files for "nested" and "flattened" XML data, respectively.

Example 45-1 "Nested" XML Data File

<?xml version="1.0" encoding="WINDOWS-1252"?>
<EMP>
    <G_DEPTNO>
      <DEPTNO>10</DEPTNO>
        <G_EMPNO>
          <EMPNO>7782</EMPNO>
          <ENAME>CLARK</ENAME>
          <JOB>MANAGER</JOB>
          <MGR>7839</MGR>
          <HIREDATE>09-JUN-81</HIREDATE>
          <SAL>2450</SAL>
          <COMM></COMM>
        </G_EMPNO>
        <G_EMPNO>
          <EMPNO>7839</EMPNO>
          <ENAME>MIKEb</ENAME>
          <JOB>BOSS</JOB>
          <MGR></MGR>
          <HIREDATE></HIREDATE>
          <SAL></SAL>
          <COMM></COMM>
        </G_EMPNO>    
    </G_DEPTNO>
    <G_DEPTNO>
      <DEPTNO>20</DEPTNO>
        <G_EMPNO>
          <EMPNO>7369</EMPNO>
          <ENAME>SMITH</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7902</MGR>
          <HIREDATE>17-DEC-80</HIREDATE>
          <SAL>800</SAL>
          <COMM></COMM>
        </G_EMPNO>
        <G_EMPNO>
          <EMPNO>7876</EMPNO>
          <ENAME>ADAMS</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7788</MGR>
          <HIREDATE>12-JAN-83</HIREDATE>
          <SAL>1100</SAL>
          <COMM></COMM>
        </G_EMPNO>      
    </G_DEPTNO>
</EMP>

Example 45-2 "Flattened" XML Data File

<?xml version="1.0" encoding="WINDOWS-1252"?>
 
<EMP>
    <G_DEPTNO>
      <DEPTNO>10</DEPTNO>
      <EMPNO>7782</EMPNO>
      <ENAME>CLARK</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>09-JUN-81</HIREDATE>
      <SAL>2450</SAL>
      <COMM></COMM>
    </G_DEPTNO>
    <G_DEPTNO>
      <DEPTNO>10</DEPTNO>
      <EMPNO>7839</EMPNO>
      <ENAME>MIKEb</ENAME>
      <JOB>BOSS</JOB>
      <MGR></MGR>
      <HIREDATE></HIREDATE>
      <SAL></SAL>
      <COMM></COMM>
    </G_DEPTNO>
    <G_DEPTNO>
      <DEPTNO>20</DEPTNO>
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>17-DEC-80</HIREDATE>
      <SAL>800</SAL>
      <COMM></COMM>
    </G_DEPTNO>
    <G_DEPTNO>
      <DEPTNO>20</DEPTNO>
       <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7788</MGR>
      <HIREDATE>12-JAN-83</HIREDATE>
      <SAL>1100</SAL>
      <COMM></COMM>
    </G_DEPTNO>
</EMP>

Example 45-3 Corresponding "Nested" XML Schema File

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
  <xsd:element name="EMP">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="DEPTNO" type="xsd:string"/>
              <xsd:element name="G_EMPNO" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="EMPNO" type="xsd:string"/>
                    <xsd:element name="ENAME" type="xsd:string"/>
                    <xsd:element name="JOB" type="xsd:string"/>
                    <xsd:element name="MGR" type="xsd:string"/>
                    <xsd:element name="HIREDATE" type="xsd:string"/>
                    <xsd:element name="SAL" type="xsd:string"/>
                    <xsd:element name="COMM" type="xsd:string"/>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Example 45-4 Corresponding "Flattened" XML Schema File

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
  <xsd:element name="EMP">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="DEPTNO" type="xsd:string"/>
              <xsd:element name="EMPNO" type="xsd:string"/>
              <xsd:element name="ENAME" type="xsd:string"/>
              <xsd:element name="JOB" type="xsd:string"/>
              <xsd:element name="MGR" type="xsd:string"/>
              <xsd:element name="HIREDATE" type="xsd:string"/>
              <xsd:element name="SAL" type="xsd:string"/>
              <xsd:element name="COMM" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

For more information on pluggable data sources, refer to the Oracle Reports online Help. If your data source cannot use an existing PDS, the PDS API enables you to write your own PDS and incorporate it into Oracle Reports to access your own unique data sources. The API is documented in Oracle Reports Java API Reference on Oracle Technology Network.

Example Scenario

In this example, you have an international business with warehouses in the United States and overseas. These warehouses are running a decentralized management system that stores the operational data locally at each site. The inventory of the warehouses are managed by the local managers. However, for planning purposes, a team at corporate headquarters needs to access the inventory data (in SQL), including the most recent data, of every warehouse. The warehouse data is only available as an XML stream. You will learn how to combine data from a local database (that is, the warehouse data) and data from an XML feed to create a Web report. You will use static XML files that are provided.

As you build this example report, you will:

Create a Report Manually with SQL and XML Queries:
- Create a SQL query for your new report.
- Create an XML query to access your XML data source using the Data Wizard.
- Create a data link between two queries, a SQL query and an XML query.
- Create a layout for your report using the Report Wizard.
- Apply alternating row colors to your report using format triggers and procedures.
- Filter your XML data using a group filter to sort your XML data.
Run Your Report to Paper.

To see a sample report that uses an XML PDS, open the examples folder named xml_pds, then open inventory_report.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 45-1.

Table 45-1 Example report files

File	Description
`xml_pds\inventory_report.pdf`	The final PDF version of the paper report.
`xml_pds\inventory_report.rdf`	The final RDF version of the paper report.
`xml_pds\xmlpds_sql.txt`	The various SQL statements you will use in this report.
`xml_pds\warehouse_inventory.xml`	The XML data source for the query in your report.
`xml_pds\warehouse_inventory.xsd`	The XML data stream for your report.