Reading XML in Oracle -3

I now have a project where I need to write the content of an XML file into an Oracle table. The idea is actually quite simple. An XML file might be seen as a document that had an hierachical structure. It is composed of several seperate subdocuments, that could be seen as several trees.   Each tree is a trunk  with branches, where a brach is translated into a table. The content of one branch is one record. A subdocument may have one or multiple branches and it thus generates one or more records. As we have one or more subdocuments in the document, we end up having one or more records in a table. The records stem from a branch in the subdocument and we may have more than one subdocument in the document.

I created two procedures to read this XML file.

The first procedure is a SQL LDR job that adds the XML document to an Oracle table. The XML document is stored as an object in an Oracle table.

The second procedure retrieves the information from the XML object into records.

Let me go in some detail here.

Reading the XML object

I created a table that may contain the XML objects. Its DDL is:

 CREATE TABLE "SCOTT"."XML_TAB_CONTRACT" 
   (	"XML_DATA" "XMLTYPE", 
	"ID" NUMBER(*,0), 
	"CONTRACT" NUMBER
   )

The XML will be stored in XML_DATA which is a field of XMLTYPE.

The CTL file that actually loads the XML file reads as:

load data
infile *
append
into table XML_TAB_CONTRACT
fields terminated by ';' 
(
 filename FILLER char(52),
 XML_DATA  lobfile(filename) terminated by eof,
 id,
CONTRACT 
)

begindata
C:\Users\Tom\Dropbox\Uitwisseling\Voorbeeld2078a.xml;201608131;2078

And the command to start the loading process is as:SQLLDR CONTROL=C:\Users\Tom\Dropbox\Uitwisseling\xml.ctl LOG=sample.log, BAD=baz.bad, USERID=scott/binvegni, ERRORS=999, LOAD=500000, DISCARD=toss.dsc, DISCARDMAX=5. This starts the SQL LDR process which uses the CTL file to know which xml file must be loaded into which table.

Reading the XML objects.

The code to read the XML object is composed of two loops.
One loop reads the XML file and looks for the branches that must be translated as records into the table. The branches are indicated by “/Message/PayLoad/ShipmentAsn4”. A cursor is defined that retrieves the set of XML objects. After that a loop is created that processes each object, one by one.
The second loop actually translates the branch into a record. In the second loop, we retrieve the actual value one by one and store it as an attribute in an object that is indicated by “r” .

reate or replace PROCEDURE SEARCH_XML
AS
v_waarde integer;
name varchar(100);
flip varchar(10);
Lcntr integer:=0;
docu varchar(9999);
CURSOR c1
IS
SELECT EXTRACT(SYS_MAKEXML(0,"A1"."SYS_NC00002$"),'/Message/PayLoad/ShipmentAsn4') "AAPJE" FROM "SCOTT"."XML_TAB_CONTRACT" "A1"  ;
l_aantal   c1%ROWTYPE;
BEGIN
 dbms_output.enable;
 OPEN c1;
 LOOP
   Lcntr := Lcntr + 1;
   FETCH c1 INTO l_aantal;
   EXIT WHEN c1%NOTFOUND;
    FOR r IN (
    SELECT ExtractValue(Value(p),'/ShipmentAsn4/GlnShipTo/text()') as flip
    FROM TABLE(XMLSequence(Extract(XMLTYPE(''||l_aantal.aapje.getClobVal()||''),'     /totaal/ShipmentAsn4'))) p
   ) LOOP
   v_waarde := adresseq.nextval;
   dbms_output.put_line(Lcntr ||' ' || v_waarde||' '||r.flip );
  END LOOP; --rij in tak XML
  END LOOP; --vrsch XML in tabel

END SEARCH_XML;