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.
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>
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>
About the XML pluggable data source
Copyright © 1984, 2005, Oracle. All rights reserved.