Oracle Apps
XSD Registration Process:
XSD Registration Process:
XSD
is schema definition file and its needs to be registered in Oracle database.
This is one time process and will be required to be re-executed only if the XSD
is modified. Following are the steps for XSD Registration
1. XSD file
is available in the UNIX directory. This file is read using UTL_FILE and
inserted into the table as LOB object using DBMS_LOB package.
---Insert a Empty CLOB record with XSD
details
INSERT INTO testxml_sources
(
source_id
,source_name
,xml_schema_name
,xml_schema
,start_date
,end_date
)
VALUES (l_source_id
,p_source_name
,p_schema_url
,EMPTY_CLOB()
,SYSDATE
,NULL
)
RETURNING
--Return the LOB locator
xml_schema INTO l_xml_schema_definition;
---Create a
BFILE Pointer for the XSD file residing in <UNIX DIR>
l_src_file := BFILENAME(<UNIX DIR>,
<XSD_FILE_NAME>);
--Open the file
DBMS_LOB.FILEOPEN(l_src_file,
DBMS_LOB.FILE_READONLY);
-- Read the
file into the l_xml_schema_definition from l_src_file
DBMS_LOB.LOADFROMFILE
( dest_lob => l_xml_schema_definition
, src_lob => l_src_file
, amount => DBMS_LOB.LOBMAXSIZE
);
DBMS_LOB.CLOSE (l_src_file );
2. If a new
XSD File comes in because of any changes in XSD definition, the record for the
old XSD in the table is end dated and a new record is inserted into the table
with Latest XSD file. This makes sure that Old XSD records retrieval is
possible from database for any purpose.
UPDATE testxml_sources
SET
end_date = TRUNC(SYSDATE)
WHERE
source_name = p_source_name
AND
xml_schema_name = p_schema_url
AND
end_date IS NULL
AND
SYSDATE BETWEEN start_date
AND
NVL(end_date,SYSDATE+1);
3. Now the
XSD in database is registered. Incase any Old XSD schema definition is present
its deleted and new XSD is registered. DBMS_XMLSCHEMA function used for
registration creates a XSD record in standard table
--Get the XSD from Database
SELECT xml_schema
,xml_schema_name
INTO
l_schema
,l_schema_name
FROM
testxml_sources
WHERE source_name = p_source_name
AND
SYSDATE BETWEEN start_date
AND NVL(end_date,SYSDATE+1);
--Delete Old
definition
DBMS_XMLSCHEMA.DELETESCHEMA(l_schema_name,
DBMS_XMLSCHEMA.DELETE_CASCADE);
--Register New Definition
DBMS_XMLSCHEMA.REGISTERSCHEMA(schemaURL =>l_schema_name
,schemaDoc => l_schema);
XML Data file
Processing:
XML
file holds the data that is to be loaded and validated. XML file contains the
name of XSD that will be by default used for validation of the XML file.
However in the current process explained here we have forced the XSD to be used
for XSD validation. This process is three stages
1.
Importing the File in Database:
XML Data file resides in the UNIX BOX in
predefined path. This file is first imported
as CLOB object into the XML Data file
storage table.
---Insert
a Empty CLOB record with XML File details
INSERT INTO testxml_data_files
(source_id
,file_id
,xml_file
,load_date
,file_name
)
VALUES
(l_source_id
,l_file_id
,EMPTY_CLOB()
,p_xml_file_name
);
RETURNING
--Return the LOB locator
xml_file INTO l_xml_file;
---Create a
BFILE Pointer for the XML file residing in <UNIX DIR>
l_src_file := BFILENAME(<UNIX
DIR>,<XML FILE>);
--Open the file
DBMS_LOB.FILEOPEN(l_src_file,
DBMS_LOB.FILE_READONLY);
-- Read the file into the l_xml_file from
l_src_file
DBMS_LOB.LOADFROMFILE ( dest_lob => l_xml_file
, src_lob => l_src_file
, amount =>
DBMS_LOB.LOBMAXSIZE
);
DBMS_LOB.CLOSE (l_src_file );
2.
Validate the XML Data file:
The XML Data file is validated against the
XSD file using xmltype.schemavalidate
function. This function by default will use
the XSD specified inside the XML for
validation, but here we have forced it to
use the XSD passed. Incase of any
validation failure of XML against the XSD,
it will throw an Exception.
--Declare
following variables
l_xml_data
testxml_data_files.xml_file%TYPE;
l_xml_schema_name testxml_sources.xml_schema_name%TYPE;
l_xml_type_0 XMLTYPE;
l_xml_type XMLTYPE;
--Fetch the Xml file details from the table
SELECT sdf.xml_file
,sxs.xml_schema_name
INTO l_xml_data
,l_xml_schema_name
FROM testxml_data_files sdf
,testxml_sources sxs
where
sxs.source_id = sdf.source_id
AND sxs.xsd_version = sdf.xsd_version
and sxs.source_name = p_source_name
and sdf.file_id = p_file_id
AND SYSDATE
BETWEEN sxs.start_date
AND NVL(sxs.end_date,SYSDATE+1);
--Cast the CLOB
into XML Type data type
l_xml_type_0 := XMLTYPE.createxml(l_xml_data);
--Specify the Name of XSD to be used for validation
l_xml_type :=
l_xml_type_0.createschemabasedxml(l_xml_schema_name);
l_xml_type.setschemavalidated(1);
--Validate the XML Data file
xmltype.schemavalidate(l_xml_type);
3.
Load the XML Data into the staging
Table.
The data contained in the XML File once
validated is loaded into the Staging table
using standard XML Functions. The PLSQL
code used here is specific to the
structure of XML file.
--Declare
variables
l_xml_data testxml_data_files.xml_file%TYPE;
l_xml_schema_name testxml_sources.xml_schema_name%TYPE;
l_xml_type XMLTYPE;
--Fetch
the Data file record in CLOB object
SELECT
sdf.xml_file
,sxs.xml_schema_name
,sxs.source_id
INTO l_xml_data
,l_xml_schema_name
,l_source_id
FROM testxml_data_files sdf
,testxml_sources sxs
WHERE
sxs.source_id = sdf.source_id
AND sxs.source_name = p_source_name
AND sdf.file_id = p_file_id
AND SYSDATE BETWEEN sxs.start_date
AND
NVL(sxs.end_date,SYSDATE+1);
--Cast
CLOB data into an XML type
l_xml_type
:=XMLTYPE.CREATEXML(l_xml_data);
First FOR Loop is opened for the Parameters
which are attributes of the File and
not the actual data of the file. These
attributes of the XML File can be used to take
decisions related to processing of the
file.
E.g. the attribute xsdversion defines the
version of XSD to be used for validating
the XML File
FOR
l_invoice_rec IN
(
SELECT EXTRACTVALUE(VALUE(XMLD),
'/Invoice /@title') title
,
EXTRACTVALUE(VALUE(XMLD), '/Invoice/@xsdversion') xsdversion
,
EXTRACTVALUE(VALUE(XMLD), '/Invoice/@purpose') purpose ,
EXTRACTVALUE(VALUE(XMLD), '/Invoice/@create_date') create_date
,
EXTRACTVALUE(VALUE(XMLD), '/Invoice/@create_time') create_time
,
EXTRACTVALUE(VALUE(XMLD), '/Invoice/@receiver_name') receiver_name
,
ROWNUM
rn
FROM
TABLE(XMLSEQUENCE(l_xml_type.EXTRACT(‘Invoice
'))) XMLD)
LOOP
--Open
loop of processing data for Invoice Header
FOR
l_header_rec IN
(SELECT
EXTRACTVALUE(VALUE(XMLD),
'/header/transaction_date') transaction_date
,
EXTRACTVALUE(VALUE(XMLD), '/header/Invoicenum’) Invoicenum
,
EXTRACTVALUE(VALUE(XMLD), '/header/Customer ') Customer,
,
EXTRACTVALUE(VALUE(XMLD), '/header/Shipto ') Shipto,
,
EXTRACTVALUE(VALUE(XMLD), '/header/TotAmount ') TotAmount,
,
ROWNUM
rn
FROM
TABLE(XMLSEQUENCE(l_xml_type.EXTRACT('Invoice/header')))
XMLD)
LOOP
--Open the Loop
for Invoice Line data
FOR l_line_rec IN
( SELECT
EXTRACTVALUE(VALUE(XMLD), '/line
/item') item,
,EXTRACTVALUE(VALUE(XMLD),
'/line/price') price,
,EXTRACT(VALUE(XMLD),'/transaction/amount') amount
,ROWNUM
rn
FROM
TABLE(XMLSEQUENCE(l_xml_type.EXTRACT(‘Invoice/Header/line’))) XMLD)
LOOP
INSERT INTO INOVICE_LINE_STG
(line_id
,transaction_date
,invoicenum
,customer
,shipto
,item
,price
,amount)
VALUES
(line_id.nextval
, l_header_rec.transaction_date
, l_header_rec.Invoicenum
,l_header_rec.Customer
,l_header_rec.Shipto
,l_line_rec.item
,l_line_rec.price
,l_line_rec.amount);
END LOOP;
END LOOP;
END LOOP;
Though
in above step the Invoice Header and Line data is loaded into the same table,
with use of some PLSQL Code this data can be loaded into two different tables
based on Business requirement.
