7 XML Files
This chapter includes the following sections:
Introduction
Oracle Data Integrator supports XML files integration through the Oracle Data Integrator Driver for XML.
Concepts
The XML concepts map the Oracle Data Integrator concepts as follows: An XML file corresponds to a data server in Oracle Data Integrator. Within this data server, a single schema maps the content of the XML file.The Oracle Data Integrator Driver for XML (XML driver) loads the hierarchical structure of the XML file into a relational schema. This relational schema is a set of tables located in the schema that can be queried or modified using SQL. The XML driver is also able to unload the relational schema back in the XML file.The relational schema is reverse-engineered as a data model in ODI, with tables, columns, and constraints. This model is used like a normal relational data model in ODI. If the modified data within the relational schema needs to be written back to the XML file, the XML driver provides the capability to synchronize the relational schema into the file.
See Oracle Data Integrator Driver for XML Reference for more information on this driver.
Pre/Post Processing Support for XML Driver
You can now customize the way data is fed to the XML driver. You can set up intermediate processing stages to process the data that is retrieved from an external endpoint using Oracle Data Integrator, or to write the data out to an external endpoint.
For detailed information about configuring and implement the pre and post processing stages for XML driver, see Pre/Post Processing Support for XML and Complex File Drivers.
Knowledge Modules
Oracle Data Integrator provides the IKM XML Control Append for handling XML data. This Knowledge Module is a specific XML Knowledge Module. It has a specific option to synchronize the data from the relational schema to the file.
In addition to this KM, you can also use an XML data server as any SQL data server. XML data servers support both the technology-specific KMs sourcing or targeting SQL data servers, as well as the generic KMs. See Generic SQL or the technology chapters for more information on these KMs.
Installation and Configuration
Make sure you have read the information in this section before you start using the XML Knowledge Module:
System Requirements
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.
Technologic Specific Requirements
There are no technology-specific requirements for using XML Files in Oracle Data Integrator.
Supported Data types
Data type Mappings to and from Oracle will be defined for these data types. Supported data types for XML technology are:
-
BIGINT — The int data type is the primary integer data type in SQL Server. An integer datatype with a precision of 19 decimal digits. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. Bigint fits between smallmoney and int in the data type precedence chart. Functions return bigint only if the parameter expression is a bigint data type.
-
BINARY — The data types BINARY and BINARY VARYING ( VARBINARY ) are collectively referred to as binary string types and the values of binary string types are referred to as binary strings. A binary string is a sequence of octets, or bytes. A binary value of NULL appears last (largest) in ascending order. Enables storage of binary data up to 4,096 bytes. Enables your application to store a bit unconstrained by character semantics.
-
BIT — An integer data type that can take a value of 1, 0, or NULL. It can also be used to store boolean values because TRUE is convertible to 1 and FALSE is convertible to 0. Converting to bit promotes any nonzero value to 1. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
-
BLOB — A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data. The length is given in bytes for BLOB unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively. Length is specified in bytes for BLOB.
-
CHAR — The CHAR data type stores character data in a fixed-length field. Character data can be stored as fixed-length or variable-length strings. Fixed-length strings are right-extended with spaces on output; variable-length strings are not extended. Any trailing blank spaces are removed on input, and only restored on output. The default length is 1, and the maximum size is 4,096 bytes.
-
CLOB — A CLOB (character large object) is used to store unicode character-based data, such as large documents in any character set. A CLOB value can be up to 2,147,483,647 ( two giga) characters long. The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively. Length is specified in characters (unicode) for CLOB.
-
DATE — Based on the international ISO-8601:1988 standard date notation: YYYYMMDD where YYYY represents the year in the usual Gregorian calendar, MM is the month between 01 (January) and 12 (December), and DD is the day of the month with a value between 01 and 31. Dates may be represented in any of the following list of base data types. For some data types, a minimum of 4 bytes is required. The list includes: Numeric, String and EBCDIC.
-
DECIMAL — DECIMAL provides an exact numeric in which the precision and scale can be arbitrarily sized. You can specify the precision (the total number of digits, both to the left and the right of the decimal point) and the scale (the number of digits of the fractional component). The amount of storage required is based on the precision.
-
DOUBLE — A double precision floating-point data type used in CREATE TABLE and ALTER TABLE statements. The double data type is a double-precision 64-bit IEEE 754 floating point. The Double data type provides the largest and smallest possible magnitudes for a number. The default value of Double is 0. For decimal values, this data type is generally the default choice.
Note:
This data type should never be used for precise values, such as currency. -
FLOAT — The FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatype is 126 binary, or 38 decimal. A subtype of the NUMBER datatype having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.
-
INTEGER — An INTEGER is an ANSI SQL data type which refers to numeric values which have only an integer portion and no floating point or decimal part. It will only store whole numbers, such as 3, 25, 1987 and so on. The INTEGER data type is usually referred to as NUMBER(38). Its precision can range from 1 to 38. SIMPLE_INTEGER, a subtype of BINARY_INTEGER. Its range is -2147483648 to 2147483648. The SIMPLE_INTEGER cannot store a NULL value.
-
LONGVARBINARY — Variable-length raw-byte data, such as IP addresses. LONG VARBINARY values are not extended to the full width of the column. Stores data up to 32,000,000 bytes. Use the LONG data types only when you need to store data greater than 65,000 bytes, which is the maximum size for VARBINARY and VARCHAR data types. Such data might include unstructured data, online comments or posts, or small log files.
-
LONGVARCHAR — Variable-length strings of letters, numbers, and symbols. LONG VARCHAR values are not extended to the full width of the column. Stores data up to 32,000,000 bytes. Use the LONG data types only when you need to store data greater than 65,000 bytes, which is the maximum size for VARBINARY and VARCHAR data types. Such data might include unstructured data, online comments or posts, or small log files.
-
NCHAR and NVARCHAR2— NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding. The NCHAR datatype stores fixed-length character strings that correspond to the national character set whereas NVARCHAR2 datatype stores variable length character strings. The maximum length of an NCHAR column is 2000 bytes and it can hold up to 2000 characters. The actual data is subject to the maximum byte limit of 2000. The maximum length of an NVARCHAR2 column is 4000 bytes and it can hold up to 4000 characters. The actual data is subject to the maximum byte limit of 4000. The two size constraints must be satisfied simultaneously at run time for both the data types.
-
NCLOB — NCLOB (National Character Large Object) is a data type that can hold up to 4 GB of character data. It's similar to a CLOB, but characters are from the national character set. In 12c, the storage limit is extended to (4*1024*1024*1024-1) * CHUNK size given in the LOB STORAGE clause defining the LOB (database block size by default).
-
NUMBER — The NUMBER data type stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision. The following numbers can be stored in a NUMBER column - positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits, negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits, zero and positive and negative infinity (generated only by importing from an Oracle Version 5 or later version database).
-
NUMERIC — The numeric data types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (that is, is "not a number" or NAN). It comprises of Number data type and Floating Point Numbers. The NUMBER data type stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle Database, up to 38 digits of precision.
-
OBJECT — Object types are abstractions of the real-world entities—for example, purchase orders—that application programs deal with. An object type is a schema object with three kinds of components - a Name, which serves to identify the object type uniquely within that schema, Attributes, which model the structure and state of the real-world entity. Attributes are built-in types or other user-defined types, Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and stored externally. Methods implement operations the application can perform on the real-world entity. An object type is a template. A structured data unit that matches the template is called an object.
-
REAL — The REAL data type is a floating-point number with a binary precision of 63, or 18 decimal. A REAL is a signed approximate numeric value with a mantissa decimal precision 7. Its absolute value is either zero or between 10^-38 and 10^38. Example - 5600E+12. The REAL data type provides 4 bytes of storage for numbers using IEEE floating-point notation.
-
SMALLINT — A SMALLINT, small integer type, is an exact numeric value with precision 5 and scale 0, typically 2 bytes or 16 bits. If signed, the range can be -32,768 to +32,767 (SQL_C_SSHORT or SQL_C_SHORT) or, if unsigned, 0 to 65,535 (SQL_C_USHORT). -32,768 <= n <= 32,767, where n is the value of a SMALLINT. SMALLINT provides 2 bytes of storage.
-
TEXT — The TEXT data type stores any kind of text data. It can contain both single-byte and multibyte characters that the locale supports. The term simple large object refers to an instance of a TEXT or BYTE data type. A TEXT column has a theoretical limit of 231 bytes (two gigabytes) and a practical limit that your available disk storage determines. No more than 195 columns of the same table can be declared as TEXT data types. The same restriction also applies to BYTE data types. You can store, retrieve, update, or delete the value in a TEXT column.
Connectivity Requirements
This section lists the requirements for connecting to XML database.
Oracle Data Integrator Driver for XML
XML files are accessed through the Oracle Data Integrator Driver for XML. This JDBC driver is installed with Oracle Data Integrator and requires no other installed component or configuration.
You must ask the system administrator for the following connection information:
-
The location of the DTD or XSD file associated with your XML file
-
The location of the XML file
Setting up the Topology
Setting up the topology consists in:
Creating an XML Data Server
An XML data server corresponds to one XML file that is accessible to Oracle Data Integrator.
Creation of the Data Server
Create a data server for the XML technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining a File data server:
Creating a Physical Schema for XML
Create an XML physical schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator.
The schema name that you have set on the URL will be preset. Select this schema for both the Data Schema and Work Schema.
Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.
Setting Up an Integration Project
Setting up a Project using the XML database follows the standard procedure. See Creating an Integration Project of the Developing Integration Projects with Oracle Data Integrator.
The recommended knowledge modules to import into your project for getting started with XML are the following:
-
LKM SQL to SQL
-
LKM File to SQL
-
IKM XML Control Append
Creating and Reverse-Engineering a XML File
This section contains the following topics:
Create an XML Model
An XML file model groups a set of datastores. Each datastore typically represents an element in the XML file.
Create an XML Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator. Select the XML technology and the XML logical schema created when configuring the topology.
Reverse-Engineering an XML Model
XML supports standard reverse-engineering, which uses only the abilities of the XML driver.
It is recommended to reference a DTD or XSD file in the dtd or d parameters of the URL to reverse-engineer the structure from a generic description of the XML file structure. Reverse-engineering can use an XML instance file if no XSD or DTD is available. In this case, the relational schema structure will be inferred from the data contained in the XML file.
Standard Reverse-Engineering
To perform a Standard Reverse- Engineering on XML use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.
The standard reverse-engineering process will automatically reverse-engineer the table from the relational schema generated by the XML driver. Note that these tables automatically include:
-
Primary keys (PK columns) to preserve parent-child elements relationships
-
Foreign keys (FK columns) to preserve parent-child elements relationships
-
Order identifier (ORDER columns) to preserve the order of elements in the XML file
These extra columns enable the mapping of the hierarchical XML structure into the relational schema. See XML to SQL Mapping in the Oracle Data Integrator Driver for XML Reference for more information.
Designing a Mapping
You can use XML as a source or a target of a mapping.
The KM choice for a mapping or a check determines the abilities and performances of this mapping or check. The recommendations in this section help in the selection of the KM for different situations concerning an XML data server.
Notes about XML Mappings
Read carefully these notes before working with XML in mappings.
Targeting an XML Structure
When using a datastore of an XML model as a target of a mapping, you must make sure to load the driver-generated columns that are used for preserving the parent-child relationships and the order in the XML hierarchy. For example, if filling records for the region
element into an XML structure as shown in Example 7-1, that correspond to a REGION table in the relational schema, you should load the columns REGION_ID and REGION_NAME of the REGION table. These two columns correspond to XML attributes.
<country COUNTRY_ID="6" COUNTRY_NAME="Australia"> <region REGION_ID="72" REGION_NAME="Queensland"> </country>
In Example 7-1 you must also load the following additional columns that are automatically created by the XML Driver in the REGION table:
-
REGIONPK: This column enables you to identify each
<region>
element. -
REGIONORDER: This column enables you to order the
<region>
elements in the XML file (records are not ordered in a relational schema, whereas XML elements are ordered). -
COUNTRYFK: This columns enables you to put the
<region>
element in relation with the<country>
parent element. This value is equal to the COUNTRY.COUNTRYPK value for the Australia record in the COUNTRY table.
Example 7-1 XML Structure
Synchronizing XML File and Schema
To ensure a perfect synchronization of the data in an XML file and the data in the XML schema, the following commands have to be called:
-
Before using the tables of an XML model, either to read or update data, it is recommended that you use the
SYNCHRONIZE FROM FILE
command on the XML logical schema. This operation reloads the XML hierarchical data in the relational XML schema. The schema is loaded in the built-in or external database storage when first accessed. Subsequent changes made to the file are not automatically synchronized into the schema unless you issue this command. -
After performing changes in the relational schema, you must unload this schema into the XML hierarchical data by calling the
SYNCHRONIZE ALL
orSYNCHRONIZE FROM DATABASE
commands on the XML Logical Schema. The IKM XML Control Append implements this synchronize command.
These commands must be executed in procedures in the packages before (and after) the mappings and procedures manipulating the XML schema.
See Oracle Data Integrator Driver for XML Reference for more information on these commands.
Handling Large XML Files
Large XML files can be handled with high performance with Oracle Data Integrator.
The default driver configuration stores the relational schema in a built-in engine in memory. It is recommended to consider the use of external database storage for handling large XML files.
See Schema Storage for more information on these commands.
Loading Data from and to XML
An XML file can be used as a mapping's source or target. The LKM choice in the Loading Knowledge Module tab that is used to load data between XML files and other types of data servers is essential for the performance of the mapping.
Loading Data from an XML Schema
Use the Generic SQL KMs or the KMs specific to the other technology involved to load data from an XML database to a target or staging area database.
Table 7-2 lists some examples of KMs that you can use to load from an XML source to a staging area:
Table 7-2 KMs to Load from XML to a Staging Area
Staging Area | KM | Notes |
---|---|---|
Microsoft SQL Server |
LKM SQL to MSSQL (BULK) |
Uses SQL Server's bulk loader. |
Oracle |
LKM SQL to Oracle |
Faster than the Generic LKM (Uses Statistics) |
All |
LKM SQL to SQL |
Generic KM to load data between an ANSI SQL-92 source and an ANSI SQL-92 staging area. |
Loading Data to an XML Schema
It is not advised to use an XML schema as a staging area, except if XML is the target of the mapping and you wish to use the target as a staging area. In this case, it might be required to load data to an XML schema.
Use the Generic SQL KMs or the KMs specific to the other technology involved to load data from a source or staging area into an XML schema.
Table 7-3 lists some examples of KMs that you can use to load from a source to an XML staging area.
Table 7-3 KMs to Load to an XML Schema
Source | KM | Notes |
---|---|---|
File |
LKM File to SQL |
Generic KM to load a file in a ANSI SQL-92 staging area. |
All |
LKM SQL to SQL |
Generic KM to load data between an ANSI SQL-92 source and an ANSI SQL-92 staging area. |
Integrating Data in XML
XML can be used as a target of a mapping. The data integration strategies in XML concern loading from the staging area to XML. The IKM choice in the Integration Knowledge Module tab determines the performances and possibilities for integrating.
The IKM XML Control Append integrates data into the XML schema and has an option to synchronize the data to the file. In addition to this KM, you can also use the Generic SQL KMs or the KMs specific to the other technology involved. Note that if using generic or technology-specific KMs, you must manually perform the synchronize operation to write the changes made in the schema to the XML file.
Table 7-4 lists some examples of KMs that you can use to integrate data:
-
From a staging area to an XML target
-
From an XML staging area to an XML target. Note that in this case the staging area is on the XML target.
Table 7-4 KMs to Integrate Data in an XML File
Mode | Staging Area | KM | Notes |
---|---|---|---|
Update |
XML |
IKM SQL Incremental Update |
Generic KM |
Append |
XML |
IKM SQL Control Append |
Generic KM |
Append |
All RDBMS |
IKM SQL to SQL Append |
Generic KM |
Troubleshooting
This section provides information on how to troubleshoot problems that you might encounter when using XML in Oracle Data Integrator. It contains the following topics:
Detect the Errors Coming from XML
Errors appear often in Oracle Data Integrator in the following way:
java.sql.SQLException: No suitable driver
at ...
at ...
...
the java.sql.SQLExceptioncode
simply indicates that a query was made through the JDBC driver, which has returned an error. This error is frequently a database or driver error, and must be interpreted in this direction.
Only the part of text in bold must first be taken in account. It must be searched in the XML driver documentation. If it contains a specific error code, like here, the error can be immediately identified.
If such an error is identified in the execution log, it is necessary to analyze the SQL code send to the database to find the source of the error. The code is displayed in the description tab of the task in error.
Common Errors
This section describes the most common errors with XML along with the principal causes. It contains the following topics:
-
No suitable driver
The JDBC URL is incorrect. Check that the URL syntax is valid.
-
File <XML file> is already locked by another instance of the XML driver
.The XML file is locked by another user/application. Close all application that might be using the XML file. If such an application has crashed, then remove the .lck file remaining in the XML file's directory.
-
The DTD file "xxxxxxx.dtd" doesn't exist
This exception may occur when trying to load an XML file by the command LOAD FILE. The error message can have two causes:
-
The path of the DTD file is incorrect.
-
The corresponding XML file was already opened by another schema (during connection for instance).
-
-
Table not found: S0002 Table not found: <table name> in statement [<SQL statement>]
The table you are trying to access does not exist in the schema.
-
Column not found: S0022 Column not found: <column name> in statement [<SQL statement>]
The column you are trying to access does not exist in the tables specified in the statement.