A script-enabled browser is required for this page to function properly.

About the XML format

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. Refer to the Oracle Technology Network (http://www.oracle.com/technology/index.html) for more information.

Sequence of XML Elements

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:

<?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>

XML PDS Implementation in Oracle Reports

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 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 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 3 and Example 4 show the corresponding XML schema files for "nested" and "flattened" XML data, respectively.

Example 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 2. "Flattened" XML Data File

"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 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 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>

See also

About the XML pluggable data source