XML Parsing - Loading the XML data into Staging Table with SQL *Loader

--> XML Parsing

DECLARE

  -- below variables are used in XML parsing or for api dbms_xmlparser

  L_BFILE bfile;                              -- this holds the binary file (from respective path)

  L_CLOB CLOB;                                -- this is to read the data from bfile into clob type

  l_parser dbms_xmlparser.parser;             -- this variable for parsing clob value to XMLparser

  MY_DOC DBMS_XMLDOM.DOMDOCUMENT;             -- this variable shall hold XML parsed data

  CURRENT_ITEM_LIST DBMS_XMLDOM.DOMNODELIST;  -- this represents the XML group of nodes (1 or  repeating) and all it sub tags

  CURRENT_ITEM DBMS_XMLDOM.DOMNODE;           -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags

  XMLELEM XMLDOM.DOMELEMENT;                  -- This will read the root element information.

  NSPACE VARCHAR2(50);                        -- This will read name space of root element information.

 

  --- below variables are to be passed as parameters for DBMS_LOB.LOADCLOBFROMFILE

  L_DEST_OFFSET  NUMBER :=1;

  L_SRC_OFFSET   NUMBER :=1;

  -- L_BFILE_CSID   NUMBER := 0;

  L_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;

  L_WARNING      NUMBER; 

 

  --> invoice partner information

  L_FLAG                VARCHAR2(1000);

  L_UNIQUE_REFERENCE    VARCHAR2(1000);

  L_CONTACT_NAME        VARCHAR2(1000);

  L_ORGANIZATION_NAME    VARCHAR2(1000);

  L_ADDRESS_INFO        VARCHAR2(1000);

  L_COUNTRY                VARCHAR2(1000);

 

BEGIN

  -- DBMS_OUTPUT.PUT_LINE('begin :');

 

  -- Point to the XML file called '12939_04112014_045006.xml'which is located in the directory_path pointed at USER_TEMP in table all_directories

  L_BFILE := BFILENAME('ECX_UTL_XSLT_DIR_OBJ', '12939_04112014_045006.xml');

 

  -- creating a temporary space for clob variable with out cache so that it will not have any performance impact

  DBMS_LOB.CREATETEMPORARY(L_CLOB, cache=>false);

 

  -- Open the XML file in read mode

  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);

 

  -- Transfer the file contents to CLOB variable

  --DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));

  DBMS_LOB.LOADCLOBFROMFILE(

                            dest_lob         => l_clob,

                            src_bfile         => L_BFILE,

                            AMOUNT             => DBMS_LOB.GETLENGTH(L_BFILE),

                            DEST_OFFSET     => L_DEST_OFFSET,

                            SRC_OFFSET         => L_SRC_OFFSET,

                            BFILE_CSID         => DBMS_LOB.DEFAULT_CSID,

                            LANG_CONTEXT     => L_LANG_CONTEXT,

                            warning         => l_warning

                           );

                          

  --close the bfile instance

  dbms_lob.close(l_bfile);

  -- This ends file manipulation and the file contents are now in a CLOB variable

   

  -- Starting XML processing business

  -- Create a parser.

  L_PARSER := DBMS_XMLPARSER.NEWPARSER;

 

  -- Parse the document and create a new DOM document.

  DBMS_OUTPUT.PUT_LINE('---->---------------------------------------------------------------------------------------');

  DBMS_OUTPUT.PUT_LINE(L_CLOB);

  DBMS_OUTPUT.PUT_LINE('---->---------------------------------------------------------------------------------------');

  DBMS_OUTPUT.PUT_LINE(' ');

  DBMS_OUTPUT.PUT_LINE(' ');

 

  -- parse the xml data from clob variable to XML_parser

  DBMS_XMLPARSER.PARSECLOB(L_PARSER,L_CLOB);

 

  -- The xml parsed CLOB is transferred to a variable called my_doc

  my_doc  := dbms_xmlparser.getdocument(l_parser);

  xmlelem := xmldom.getdocumentelement(my_doc);

  nspace  := xmldom.getnamespace(xmlelem);

  -- DBMS_OUTPUT.PUT_LINE('XML Root element information');

  -- DBMS_OUTPUT.PUT_LINE('Qualified Name: ' || DBMS_XMLDOM.GETQUALIFIEDNAME(XMLELEM));

  -- DBMS_OUTPUT.PUT_LINE('Local Name: ' || DBMS_XMLDOM.GETLOCALNAME(XMLELEM));

  -- DBMS_OUTPUT.PUT_LINE('Namespace: ' || NSPACE);

  -- DBMS_OUTPUT.PUT_LINE('Expanded Name: ' || DBMS_XMLDOM.GETEXPANDEDNAME(XMLELEM));

 

  -- Free resources associated with the CLOB and Parser now they are no longer needed.

  dbms_lob.freetemporary(l_clob);

  DBMS_XMLPARSER.FREEPARSER(L_PARSER);

  -- dbms_output.put_line('parser free:');

 

      --Now the following statement shall search for a node starting with 'RPS/Search'

      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'RPS/Search');-- note no slash at end

      DBMS_OUTPUT.PUT_LINE('----->> Loop Start');

      DBMS_OUTPUT.PUT_LINE('Count of RPS/Search : '||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node

     

      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1

      LOOP

        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);

        DBMS_OUTPUT.PUT_LINE('--> current_item : '|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'AlertType/text()', L_FLAG);           

        DBMS_OUTPUT.PUT_LINE(' AlertType : '|| L_FLAG);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Comment/text()', L_UNIQUE_REFERENCE);           

        DBMS_OUTPUT.PUT_LINE(' Comment : '|| L_UNIQUE_REFERENCE);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Name/text()', L_CONTACT_NAME);           

        DBMS_OUTPUT.PUT_LINE(' Name : '|| L_CONTACT_NAME);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Company/text()', L_ORGANIZATION_NAME);           

        DBMS_OUTPUT.PUT_LINE(' Company : '|| L_ORGANIZATION_NAME);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Address/text()', L_ADDRESS_INFO);           

        DBMS_OUTPUT.PUT_LINE(' Address : '|| L_ADDRESS_INFO);

        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Country/text()', L_COUNTRY);           

        DBMS_OUTPUT.PUT_LINE(' Country : '|| L_COUNTRY);

       

        INSERT INTO XXAA_XML_SQLLOADER_STG(FLAG,UNIQUE_REFERENCE,CONTACT_NAME,ORGANIZATION_NAME,ADDRESS_INFO,COUNTRY) VALUES(L_FLAG,L_UNIQUE_REFERENCE,L_CONTACT_NAME,L_ORGANIZATION_NAME,L_ADDRESS_INFO,L_COUNTRY);     

      END LOOP;

     

      COMMIT;

     

  DBMS_XMLDOM.FREEDOCUMENT(MY_DOC);

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('sqlerrm :'||SQLERRM);

  dbms_lob.close(l_bfile);

  DBMS_LOB.FREETEMPORARY(L_CLOB);

  DBMS_XMLPARSER.FREEPARSER(L_PARSER);

  DBMS_XMLDOM.FREEDOCUMENT(MY_DOC);

END;

/