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


2 comments:

Anonymous said...

First of all I would like to say great blog! I had
a quick question that I'd like to ask if you do not mind.

I was interested to know how you center yourself
and clear your mind prior to writing. I've had difficulty clearing my
mind in getting my ideas out. I do enjoy writing but it just seems like the first 10 to 15 minutes are usually wasted simply just trying to figure out how
to begin. Any recommendations or hints? Kudos!

Raju Chinthapatla said...

This is a technical blog, you no need to think about matter, just execute whatever you know, This information may help to others.
Iam doing from the past 8 years....

Post a Comment

Best Blogger TipsGet Flower Effect