Loading xml elements from multiple xml files into oracle table.

How to transform xml data from multiple xml files into oracle database tables.

      1.       Extracting xml file names from the directory into .dat file. This .dat file will be referred

             by control file.

      2.       Processing xml files in the dat file through sql loader with the following command.

                   SQL >  host sqlldr control = ‘control file name.ctl’

      3.       We can querry the result set from the table “xml_files”.

            SQL>  SELECT     replace(file_name,'''','')

                                      ,    EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()')

                                      ,     EXTRACTVALUE (xmldata, '//ShipperID/text()')

                            FROM     xml_files ;

       4.       Processing xml data elements from xml type table “xml_type” into base                                          table"xxrp_acct_header”.

        5.       Execute the following procedure “accountHEADER” to load required xml elements into              “xxrp_acct_header” with the following command and also empty the table “xml_files”

          exec accountHEADER;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The following syntax should be followed while extracting xml file names into .dat file. Filename “'xml_files.dat'”

                          "      Xmlfilename1.xml,1,’xmlfilename1.xml'

                                  Xmlfilename2.xml,2,’xmlfilename2.xml'

                                  Xmlfilename3.xml,3,’xmlfilename3.xml'    ”

The .dat file will be referenced by control file which locks as follows. File name “xml.ctl”

                           “          load data

                                       infile 'xml_files.dat'

                                       into table XML_FILES append

                                       fields terminated by ','

                                        (

                                        xml_filler filler char

                                        ,xmldata lobfile(xml_filler) terminated by EOF

                                         ,file_id

                                         ,file_name

                                         )               ”

Create the following tables must be created.

                             SQL>  create table XML_FILES(

                                         XMLDATA     sys.XMLTYPE        NOT NULL,

                                         FILE_ID          NUMBER (10)        NOT NULL,

                                         FILE_NAME  VARCHAR2 (255)   NOT NULL

                                         );

                              SQL> create table xxrp_acct_header (

                                         file_name                    varchar2(100),

                                         AirwayBillNumber     varchar2(100),

                                         ShipperID                    varchar2(100)

                                         );

Compile and Execute the following procedure.

“set SERVEROUTPUT ON

/

CREATE OR REPLACE PROCEDURE accountHEADER AS

BEGIN

insert into xxrp_acct_header (file_name,AirwayBillNumber,ShipperID)

SELECT replace(file_name,'''',''), EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()'),

EXTRACTVALUE (xmldata, '//ShipperID/text()')

FROM xml_files ;

delete from xml_files where file_name is not null;

COMMIT;

END accountHEADER;

/

show errors

/

exec accountHEADER;

/”

SELECT XMLTYPE(t.response_xml).EXTRACT('//PieceNumber/text()').getStringVal() as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;

OR

SELECT
EXTRACTVALUE(xmltype(t.response_xml), '//PieceNumber/text()') as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;