Oracle Apps XML Interface design Considerations

Oracle Apps

Terminologies:

We need to understand the XML keywords that will be used across this article

1.    XML Data file: Data file with .xml extension with all the data that should be loaded into the Tables.

2.    XSD: Its schema definition file. This file defines the structure which XML Data file should have. Also it defines the range of values for data fields in XML Data file, against which the data is validated.

Normally in PLSQL interface the validations on the data are performed using PLSQL code. In XML interface the XSD file can be used to perform following validations on XML Data file

1.    Verifies the structure of the XML File

2.    Checks if mandatory values are missing

3.    Checks if the values for a particular field are within range of valid values.

3.    XML schema URL:  This is the value of parameter in XML Data file header definition which indicates the name of XSD that should be used for validating the XML Data file.

Interface design considerations:

1.    Structure for the XML data file should be decided and accordingly XSD file should be developed. XSD file will define the exact structure of XML file and range of valid values for various data fields in the XML.

2.    Location of the XSD and XML File to be processed needs to be decided. In current case the XSD and XML Data file resides in predefined directories in UNIX Box.

3.    Usually XSD file is received and its registration process is done at the start. New XSD file registration is required only if there is modification in structure of XML data or range of values for any field in XML file. Incase a new XSD file is available then the Old XSD record in database is end dated and new XSD file record is made active.

4.    Attributes in XML File such as ‘xsdversion’ can be used to identify the XSD version to be used for validating the XML File.

5.    Archiving for XML and XSD files can be carried out by moving them to backup directories once processing is complete.

6.    Frequency at which the XML Data file will have to be processed will depend on Business requirements and can be monthly or daily.

7.    XML and XSD are stored in database as LOB objects before any processing for historical records.

8.    Data Extraction in done by using Oracle XML DB packages available with release 10g of Oracle database.

9.    The next article explains the process for loading the XML data into one single Table. Using some modifications the data can be loaded into Multiple or Parent-Child tables. Single XML Data file can contain the Header as well as Line record.

Process Diagram: