Saturday, September 26, 2015

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;
/


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect