add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, August 7

XML Parsing -reading xml file in oracle pl sql example

Please find the below stand alone script followed by sample XML file that I am using for this script.

Steps to be performed.

  1. create a XML file with name OMX_Cxml.xml (by copying the XML file data provided at the end of this post).
  2. Place the xml file in any server path ( in this example I have placed the file in USER_TEMP directory) directory namd and path can be seen in all_directories table
  3. Execute the below script and understand how am i reading each node and attribute value also How am i handling the loops for repeating nodes .
  4. Modify the script as per your xml file.


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 vairable 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
  CURRENT_ITEM_LIST1 DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or  repeating) and all it sub tags
  CURRENT_ITEM1 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. In our file rooot element is :<cXML ..>
  NSPACE VARCHAR2(50);                        --This will read name sapce of root element information. In our file rooot element is :<cXML ..> and no namespaces are defined in file
  --- 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;
  -- Declare local variables the correspond to the XML data
  -- in more serious implementation , consider using RECORD type
  ---Header information
  from_credential     VARCHAR2(1000);
  from_identity       VARCHAR2(1000);
  from_sharedsecret   VARCHAR2(1000);
  to_credential       VARCHAR2(1000);
  to_identity         VARCHAR2(1000);
  to_sharedsecret     VARCHAR2(1000);
  sender_credential   VARCHAR2(1000);
  sender_identity     VARCHAR2(1000);
  sender_sharedsecret VARCHAR2(1000);
  SENDER_USERAGENT    VARCHAR2(1000);
  ------------------
  ---Request information
  L_DEPLOYMENTMODE     VARCHAR2(1000);
  L_INVOICEID          VARCHAR2(1000);
  L_PURPOSE            VARCHAR2(1000);
  L_OPERATION          VARCHAR2(1000);
  L_INVOICEDATE        VARCHAR2(1000);
  L_ISACCOUNTINGINLINE VARCHAR2(1000);
  L_PAYINNUMBEROFDAYS  NUMBER;
  L_PERCENTAGERATE     NUMBER;
  L_EXTRINSIC_NAME     VARCHAR2(1000);
  L_EXTRINSIC_value    VARCHAR2(1000);
  --invoice partner information
  L_CONTACT_ROLE                VARCHAR2(1000);
  L_CONTACT_NAME                VARCHAR2(1000);
  L_CONTACT_STREET1             VARCHAR2(1000);
  L_CONTACT_STREET2             VARCHAR2(1000);
  L_CONTACT_CITY                VARCHAR2(1000);
  L_CONTACT_STATE               VARCHAR2(1000);
  L_CONTACT_POSTALCODE          VARCHAR2(1000);
  L_CONTACT_COUNTRY             VARCHAR2(1000);
  L_CONTACT_FROM_NAME           VARCHAR2(1000);
  L_CONTACT_FROM_STREET1        VARCHAR2(1000);
  L_CONTACT_FROM_STREET2        VARCHAR2(1000);
  L_CONTACT_FROM_CITY           VARCHAR2(1000);
  L_CONTACT_FROM_STATE          VARCHAR2(1000);
  L_CONTACT_FROM_POSTALCODE     VARCHAR2(1000);
  L_CONTACT_FROM_COUNTRY        VARCHAR2(1000);
  L_CONTACT_SOLDTO_NAME         VARCHAR2(1000);
  L_CONTACT_SOLDTO_STREET1      VARCHAR2(1000);
  L_CONTACT_SOLDTO_STREET2      VARCHAR2(1000);
  L_CONTACT_SOLDTO_CITY         VARCHAR2(1000);
  L_CONTACT_SOLDTO_STATE        VARCHAR2(1000);
  L_CONTACT_SOLDTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_SOLDTO_COUNTRY      VARCHAR2(1000);
  L_CONTACT_BILLTO_NAME         VARCHAR2(1000);
  L_CONTACT_BILLTO_STREET1      VARCHAR2(1000);
  L_CONTACT_BILLTO_STREET2      VARCHAR2(1000);
  L_CONTACT_BILLTO_CITY         VARCHAR2(1000);
  L_CONTACT_BILLTO_STATE        VARCHAR2(1000);
  L_CONTACT_BILLTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_BILLTO_COUNTRY      VARCHAR2(1000);
  L_CONTACT_RMTTO_NAME          VARCHAR2(1000);
  L_CONTACT_RMTTO_STREET1       VARCHAR2(1000);
  L_CONTACT_RMTTO_STREET2       VARCHAR2(1000);
  L_CONTACT_RMTTO_CITY          VARCHAR2(1000);
  L_CONTACT_RMTTO_STATE         VARCHAR2(1000);
  L_CONTACT_RMTTO_POSTALCODE    VARCHAR2(1000);
  L_CONTACT_RMTTO_COUNTRY       VARCHAR2(1000);
  L_CONTACT_SHIPROLE            VARCHAR2(1000);
  L_CONTACT_SHIPFROM_NAME       VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STREET1    VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STREET2    VARCHAR2(1000);
  L_CONTACT_SHIPFROM_CITY       VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STATE      VARCHAR2(1000);
  L_CONTACT_SHIPFROM_POSTALCODE VARCHAR2(1000);
  L_CONTACT_SHIPFROM_COUNTRY    VARCHAR2(1000);
  L_CONTACT_SHIPTO_NAME         VARCHAR2(1000);
  L_CONTACT_SHIPTO_STREET1      VARCHAR2(1000);
  L_CONTACT_SHIPTO_STREET2      VARCHAR2(1000);
  L_CONTACT_SHIPTO_CITY         VARCHAR2(1000);
  L_CONTACT_SHIPTO_STATE        VARCHAR2(1000);
  L_CONTACT_SHIPTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_SHIPTO_COUNTRY      VARCHAR2(1000);
  L_ORDER_ID                    NUMBER;
  L_PAYLOADID                   VARCHAR2(1000);
  L_INVOICELINENUMBER           NUMBER;
  L_QUANTITY                    NUMBER;
  L_UNITOFMEASURE               VARCHAR2(100);
  L_CURRENCY                    VARCHAR2(100);
  L_UNITPRICE                   VARCHAR2(100);
  L_INV_DET_ITEM_REF_LINENUM    NUMBER;
  L_SUPPLIERPARTID              VARCHAR2(100);
  L_DESCRIPTION                 VARCHAR2(1000);
  L_MANUFACTURERPARTID          VARCHAR2(1000);
  L_MANUFACTURERNAME            VARCHAR2(1000);
  L_SUBTOTALAMOUNT_CUR          VARCHAR2(1000);
  L_SUBTOTALAMOUNT              NUMBER;
  L_GROSSAMOUNT_CUR             VARCHAR2(1000);
  L_GROSSAMOUNT                 NUMBER;
  L_NETAMOUNT_CUR               VARCHAR2(1000);
  L_NETAMOUNT                   NUMBER;
  L_SRY_SUBTOTAL_CUR            VARCHAR2(1000);
  L_SRY_SUBTOTAL_AMOUNT         NUMBER;
  L_SRY_TAX_CUR                 VARCHAR2(1000);
  L_SRY_TAX_AMOUNT              NUMBER;
  L_SRY_TAX_PURPOSE             VARCHAR2(1000);
  L_SRY_TAX_CATEGORY            VARCHAR2(1000);
  L_SRY_TAX_RATE                VARCHAR2(1000);
  L_SRY_TAXABLE_CUR             VARCHAR2(1000);
  L_SRY_TAXABLE_AMOUNT          NUMBER;
  L_SRY_TAX_CUR1                VARCHAR2(1000);
  L_SRY_TAX_AMOUNT1             NUMBER;
  L_SRY_GROSS_CUR               VARCHAR2(1000);
  L_SRY_GROSS_AMOUNT            NUMBER;
  L_SRY_NET_CUR                 VARCHAR2(1000);
  L_SRY_NET_AMOUNT              NUMBER;
  L_SRY_DUE_CUR                 VARCHAR2(1000);
  L_SRY_DUE_AMOUNT              NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('begin :');
  -- Point to the XML file called 'OMX_Cxml.xml'which is located in the directory_path pointed at USER_TEMP in table all_directories
  L_BFILE := BFILENAME('USER_TEMP', 'OMX_Cxml.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);
  -- tranfer 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 vairable
  -- This entry is important to deal with DATE format in the XML data
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
  -- Starting XML processing business
  -- Create a parser.
  L_PARSER := DBMS_XMLPARSER.NEWPARSER;
  -- Parse the document and create a new DOM document.
  -- temporarily I am prting the XML data for testing
  --DBMS_OUTPUT.PUT_LINE('parser : l_clob---'||L_CLOB);
  -- parse the xml data from clob variabl to XML_parser
  --DBMS_XMLPARSER.PARSECLOB(L_PARSER,L_CLOB);
  DBMS_XMLPARSER.PARSECLOB(L_PARSER, REGEXP_REPLACE(L_CLOB,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));
  -- The xml parsed CLOB is transfered 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:');
  -- The following statement shall search for a node starting with 'cXML/Header'
  -- Then it shall get the no of entries in this node. In our example the 'cXML/Header' contains only one
  -- entry, but a big one, because it contains from to and sender XML (there is ony one <Header> </Header>
  current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Header');
  -- note no slash at end
  DBMS_OUTPUT.PUT_LINE('#############     header loop begin #################');
  dbms_output.put_line('Count of cXML/Header : '||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 -- it should loop only once because the Header is only one instance
  
    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    --#############-------------Header/From----------------##############
    dbms_output.put_line('---------------From----------------');
    dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);
    dbms_output.put_line('From/Credential/@domain: '||from_credential);
    dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity);
    dbms_output.put_line('From/Credential/Identity:'||from_identity);
    dbms_xslprocessor.valueof(current_item,'From/Credential/SharedSecret/text()',from_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('From/Credential/SharedSecret:'||FROM_SHAREDSECRET);
    --#############-------------Header/To----------------##############
    dbms_output.put_line('---------------To----------------');
    dbms_xslprocessor.valueof(current_item,'To/Credential/@domain',to_credential);
    dbms_output.put_line('From/Credential/@domain: '||to_credential);
    dbms_xslprocessor.valueof(current_item,'To/Credential/Identity/text()',to_identity);
    dbms_output.put_line('To/Credential/Identity:'||to_identity);
    dbms_xslprocessor.valueof(current_item,'To/Credential/SharedSecret/text()',to_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('To/Credential/SharedSecret:'||TO_SHAREDSECRET);
    --#############-------------Header/Sender----------------##############
    dbms_output.put_line('---------------Sender----------------');
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/@domain',Sender_credential);
    dbms_output.put_line('Sender/Credential/@domain: '||Sender_credential);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/Identity/text()',sender_identity);
    dbms_output.put_line('Sender/Credential/Identity:'||sender_identity);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/SharedSecret/text()',sender_sharedsecret);
    dbms_output.put_line('Sender/Credential/SharedSecret:'||sender_sharedsecret);
    dbms_xslprocessor.valueof(current_item,'Sender/UserAgent/text()',sender_UserAgent);
    DBMS_OUTPUT.PUT_LINE('Sender/UserAgent:'||SENDER_USERAGENT);
  END LOOP;
  --Now the following statement shall search for a node starting with 'cXML/Request'
  -- Then it shall get the no of entries in this node. In our example the 'cXML/Request' contains only one
  -- entry, but a big one, because it contains from to and sender XML (there is ony one <Request> </Request>
  current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Request');
  -- note no slash at end
  DBMS_OUTPUT.PUT_LINE('#############     Request loop begin      #################');
  dbms_output.put_line('Count of cXML/Request : '||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 -- it should loop only once because the Request is only one instance
  
    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@deploymentMode',L_DEPLOYMENTMODE);
    DBMS_OUTPUT.PUT_LINE('Request/@domain: '||l_deploymentMode);
    CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest');
    -- note no slash at end
    DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailRequest loop begin      #################');
    dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest: '||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));
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceID',L_INVOICEID);
      dbms_output.put_line('InvoiceDetailRequestHeader/@invoiceID:'||L_INVOICEID );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@purpose',L_PURPOSE);
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@purpose:'||L_PURPOSE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@operation',L_OPERATION );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@operation:'||L_OPERATION );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceDate',L_INVOICEDATE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@invoiceDate:'||L_INVOICEDATE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailLineIndicator/@isAccountingInLine',L_ISACCOUNTINGINLINE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailLineIndicator/@isAccountingInLine:'||L_ISACCOUNTINGINLINE );
      --- retriving the InvoiceDetailPaymentTerm and Extrinsic details before re-initialinf current item list for retriving InvoicePartner & InvoiceDetailShipping
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@payInNumberOfDays',l_payInNumberOfDays );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@payInNumberOfDays:'||l_payInNumberOfDays );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@percentageRate',l_percentageRate );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_percentageRate );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/@name',l_Extrinsic_name );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/Extrinsic/@name'||L_EXTRINSIC_NAME );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/text()',l_Extrinsic_value );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_Extrinsic_value );
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner');
      -- note no slash at end
    
      DBMS_OUTPUT.PUT_LINE('#############     InvoicePartner loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||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,'Contact/@role',L_CONTACT_ROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_ROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/Name/text()', L_CONTACT_NAME);                         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=2]/text()', L_CONTACT_STREET2);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/City/text()', L_CONTACT_CITY);           
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/State/text()', L_CONTACT_STATE);         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Country/text()', L_CONTACT_COUNTRY);     
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_ROLE              = 'from' THEN
          L_CONTACT_FROM_NAME         := L_CONTACT_NAME;
          L_CONTACT_FROM_CITY         := L_CONTACT_CITY;
          L_CONTACT_FROM_STATE        := L_CONTACT_STATE;
          L_CONTACT_FROM_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_FROM_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_FROM_Street1      := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'soldTo' THEN
          L_CONTACT_SOLDTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_SOLDTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_SOLDTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_SOLDTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SOLDTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SOLDTO_Street1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'remitTo' THEN
          L_CONTACT_RMTTO_NAME        := L_CONTACT_NAME;
          L_CONTACT_RMTTO_CITY        := L_CONTACT_CITY;
          L_CONTACT_RMTTO_STATE       := L_CONTACT_STATE;
          L_CONTACT_RMTTO_POSTALCODE  := L_CONTACT_POSTALCODE;
          L_CONTACT_RMTTO_COUNTRY     := L_CONTACT_COUNTRY ;
          L_CONTACT_RMTTO_Street1     := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'billTo' THEN
          L_CONTACT_billTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_BILLTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_BILLTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_BILLTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_BILLTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_BILLTO_Street1    := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoiceDetailShipping/Contact');
      -- note no slash at end
     DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailShipping loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||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,'@role',L_CONTACT_SHIPROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_SHIPROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Name/text()', L_CONTACT_NAME);                         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=2]/text()', L_CONTACT_Street2);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/City/text()', L_CONTACT_CITY);           
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/State/text()', L_CONTACT_STATE);         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Country/text()', L_CONTACT_COUNTRY);     
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_SHIPROLE            = 'shipFrom' THEN
          L_CONTACT_SHIPFROM_NAME       := L_CONTACT_NAME;
          L_CONTACT_SHIPFROM_CITY       := L_CONTACT_CITY;
          L_CONTACT_SHIPFROM_STATE      := L_CONTACT_STATE;
          L_CONTACT_SHIPFROM_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPFROM_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPFROM_STREET1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_SHIPROLE         = 'shipTo' THEN
          L_CONTACT_SHIPTO_NAME         := L_CONTACT_NAME;
          L_CONTACT_SHIPTO_CITY         := L_CONTACT_CITY;
          L_CONTACT_SHIPTO_STATE        := L_CONTACT_STATE;
          L_CONTACT_SHIPTO_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPTO_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPTO_STREET1      := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailOrder loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder'||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,'InvoiceDetailOrderInfo/OrderReference/@orderID',L_ORDER_ID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/@orderID: '||L_ORDER_ID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID', L_PAYLOADID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID: '||L_PAYLOADID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@invoiceLineNumber', L_INVOICELINENUMBER );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@invoiceLineNumber: '||L_INVOICELINENUMBER );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@quantity', L_QUANTITY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@quantity: '||L_QUANTITY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitOfMeasure/text()', L_UNITOFMEASURE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitOfMeasure/text(): '||L_UNITOFMEASURE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/@currency', L_CURRENCY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/@currency: '||L_CURRENCY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/text()', L_UNITPRICE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/text(): '||L_UNITPRICE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber', L_INV_DET_ITEM_REF_LINENUM );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber: '||L_INV_DET_ITEM_REF_LINENUM );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text()', L_SUPPLIERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text(): '||L_SUPPLIERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/Description/text()', L_DESCRIPTION );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/Description/text(): '||L_DESCRIPTION );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text()', L_MANUFACTURERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text(): '||L_MANUFACTURERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text()', L_MANUFACTURERNAME );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text(): '||L_MANUFACTURERNAME );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/@currency', L_SUBTOTALAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/@currency: '||L_SUBTOTALAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/text()', L_SUBTOTALAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/text(): '||L_SUBTOTALAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/@currency', L_GROSSAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/@currency: '||L_GROSSAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/text()', L_GROSSAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/text(): '||L_GROSSAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/@currency', L_NETAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/@currency: '||L_NETAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/text()', L_NETAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/text(): '||L_NETAMOUNT );
      end LOOP;
    
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailSummary loop begin      #################');
      dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary'||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,'SubtotalAmount/Money/@currency', L_SRY_SUBTOTAL_CUR );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/@currency: '||L_SRY_SUBTOTAL_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/text()', L_SRY_SUBTOTAL_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/text(): '||L_SRY_SUBTOTAL_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/@currency', L_SRY_TAX_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/@currency: '||L_SRY_TAX_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/text()', L_SRY_TAX_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/text(): '||L_SRY_TAX_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@purpose', L_SRY_TAX_PURPOSE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@purpose: '||L_SRY_TAX_PURPOSE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@category', L_SRY_TAX_CATEGORY );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@category: '||L_SRY_TAX_CATEGORY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@percentageRate', L_SRY_TAX_RATE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@percentageRate: '||L_SRY_TAX_RATE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/@currency', L_SRY_TAXABLE_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/@currency: '||L_SRY_TAXABLE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/text()', L_SRY_TAXABLE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/text(): '||L_SRY_TAXABLE_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/@currency', L_SRY_TAX_CUR1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/@currency: '||L_SRY_TAX_CUR1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/text()', L_SRY_TAX_AMOUNT1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/text(): '||L_SRY_TAX_AMOUNT1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/@currency', L_SRY_GROSS_CUR );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/@currency: '||L_SRY_GROSS_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/text()', L_SRY_GROSS_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/text(): '||L_SRY_GROSS_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/@currency', L_SRY_NET_CUR );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/@currency: '||L_SRY_NET_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/text()', L_SRY_NET_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/text(): '||L_SRY_NET_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/@currency', L_SRY_DUE_CUR );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/@currency: '||L_SRY_DUE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/text()', L_SRY_DUE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/text(): '||L_SRY_DUE_AMOUNT );
      END LOOP;
    END LOOP;
  END LOOP;
  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;
/


-------------------XML File---------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd"><cXML version="1.2.009" payloadID="1405088578064.1105606342@qa-is82-real01.officemax.omx.com" timestamp="2014-07-11T12:00:00-00:00" xml:lang="en-US">
  <Header>
    <From>
      <Credential domain="DUNS">
        <Identity>178923231</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
    </From>
    <To>
      <Credential domain="DUNS">
        <Identity>119851215</Identity>
      </Credential>
    </To>
    <Sender>
      <Credential domain="OracleSN">
        <Identity>oraclesn</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
      <UserAgent>Oracle Supplier Network</UserAgent>
    </Sender>
  </Header>
  <Request deploymentMode="test">
    <InvoiceDetailRequest>
      <InvoiceDetailRequestHeader invoiceID="755055Jun2414" purpose="standard" operation="new" invoiceDate="2014-06-24T12:00:00-00:00">
        <InvoiceDetailHeaderIndicator/>
        <InvoiceDetailLineIndicator isAccountingInLine="yes"/>
        <InvoicePartner>
          <Contact role="from">
            <Name xml:lang="en-US">Laura Sebesta</Name>
            <PostalAddress>
              <Street>800 W. Bryn Mawr Ave.</Street>
              <Street>AMNE / AMSE cXML Profile</Street>
              <City>Itasca</City>
              <State>IL</State>
              <PostalCode>60143</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="soldTo">
            <Name xml:lang="en-US">INTERACTIVE DATA          </Name>
            <PostalAddress>
              <Street>ACCOUNTS PAYABLE          </Street>
              <Street>32 CROSBY DR              </Street>
              <City>BEDFORD            </City>
              <State>MA</State>
              <PostalCode>01730    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="remitTo">
            <Name xml:lang="en-US">Wachovia Bank</Name>
            <PostalAddress>
              <Street>P.O. Box 101705</Street>
              <City>Atlanta</City>
              <State>GA</State>
              <PostalCode>303921705</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <IdReference identifier="069984151" domain="accountID"/>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="billTo" addressID="161   ">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoiceDetailShipping>
          <Contact role="shipFrom">
            <Name xml:lang="en-US">OfficeMax Contract</Name>
            <PostalAddress>
              <Street>125 Industrial Way</Street>
              <City>Portland</City>
              <State>ME</State>
              <PostalCode>04104</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <Contact role="shipTo">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoiceDetailShipping>
        <InvoiceDetailPaymentTerm payInNumberOfDays="35" percentageRate="0"/>
        <Extrinsic name="SHIPTOPARTNRIDX">161   </Extrinsic>
      </InvoiceDetailRequestHeader>
      <InvoiceDetailOrder>
        <InvoiceDetailOrderInfo>
          <OrderReference orderID="1035023685">
            <DocumentReference payloadID="Office Max.2503690.35088442.TEST.1035023685:0:103"/>
          </OrderReference>
        </InvoiceDetailOrderInfo>
        <InvoiceDetailItem invoiceLineNumber="401" quantity="3">
          <UnitOfMeasure>EA</UnitOfMeasure>
          <UnitPrice>
            <Money currency="USD">148.00</Money>
          </UnitPrice>
          <InvoiceDetailItemReference lineNumber="4">
            <ItemID>
              <SupplierPartID>Q7VG730M</SupplierPartID>
            </ItemID>
            <Description xml:lang="en-US">LCD MONITOR 17&quot;</Description>
            <ManufacturerPartID>VG730M</ManufacturerPartID>
            <ManufacturerName xml:lang="en-US">VIEWSO</ManufacturerName>
          </InvoiceDetailItemReference>
          <SubtotalAmount>
            <Money currency="USD">00444.00</Money>
          </SubtotalAmount>
          <GrossAmount>
            <Money currency="USD">444</Money>
          </GrossAmount>
          <NetAmount>
            <Money currency="USD">00444.00</Money>
          </NetAmount>
        </InvoiceDetailItem>
      </InvoiceDetailOrder>
      <InvoiceDetailSummary>
        <SubtotalAmount>
          <Money currency="USD">444.00</Money>
        </SubtotalAmount>
        <Tax>
          <Money currency="USD">27.75</Money>
          <Description xml:lang="en-US"/>
          <TaxDetail purpose="tax" category="sales" percentageRate="6.25">
            <TaxableAmount>
              <Money currency="USD">444.00</Money>
            </TaxableAmount>
            <TaxAmount>
              <Money currency="USD">27.75</Money>
            </TaxAmount>
            <TaxLocation xml:lang="en-US">usa</TaxLocation>
          </TaxDetail>
        </Tax>
        <GrossAmount>
          <Money currency="USD">471.75</Money>
        </GrossAmount>
        <NetAmount>
          <Money currency="USD">471.75</Money>
        </NetAmount>
        <DueAmount>
          <Money currency="USD">471.75</Money>
        </DueAmount>
      </InvoiceDetailSummary>
    </InvoiceDetailRequest>
  </Request>
</cXML>
 ------------------------------------------------------------
Issues occured:

1. when using this DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));
   the data in xml file is reading in unknown characterset   

   Resolved: by using DBMS_LOB.LOADCLOBFROMFILE

2. when using this dbms_xmlparser.parseclob(l_parser,l_clob) getting the below error
    sqlerrm :ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
    resolved : by using
        dbms_xmlparser.parseclob(l_parser, REGEXP_REPLACE(l_clob,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));

3. could not able to read the file when file is in "/idc/dev/app/apps/apps_st/appl/zzcus/12.0.0/XMLDIR" and getting the below error.
        ORA-22288: file or LOB operation FILEOPEN failed
            No such file or directory
    temporarily Resolved : by placing the file in  /idc/dev/csf/tmp.

4. Able to read the node values but not attribute values
    resolved : by using
   for Ex: <Identity>178923231</Identity>  by using: dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity)
   for Ex: <Credential domain="OracleSN">  by using: dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);


   
 
Posted by Jithendra Kumar B

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)