DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Parse XML and Load in oracle Tables

Threaded View

  1. #1
    Join Date
    Jan 2009
    Posts
    10

    Thumbs up Parse XML and Load in oracle Tables

    Dear All,

    I have a problem in current environmnet.

    Below is the XML file iam using.Iam able to parse and get values for all nodes.

    But dont know how to parse the REGIONS nodes and its child nodes and get the value. If somebody can help me it will be of great help.

    < ?xml version="1.0"?>
    < GetDataResult>
    < RDCItem Type="Cash">
    < Program> Cash Delivery Allowance< / Program>
    < ModelYear> 2008< / ModelYear>
    < Model> Aura3< / Model>
    < Make> Saturn< / Make>
    < ModelCode> ZZS69< / ModelCode>
    < Amount> 500< / Amount>
    < EndDate> 1/ 10/ 2009< / EndDate>
    < Regions>
    < Region> BC< / Region>
    < Region> AT< / Region>
    < Region> AB< / Region>
    < Region> QC< / Region>
    < Region> PR< / Region>
    < Region> ON< / Region>
    < / Regions>
    < / RDCItem>

    < RDCItem Type="Bash">
    < Program> Cash Delivery Allowance< / Program>
    < ModelYear> 2009< / ModelYear>
    < Model> Aura2< / Model>
    < Make> Saturn2< / Make>
    < ModelCode> ZZS692< / ModelCode>
    < Amount> 5000< / Amount>
    < EndDate> 11/ 10/ 2009< / EndDate>
    < Regions>
    < Region> BC< / Region>
    < Region> AT< / Region>
    < Region> AB< / Region>
    < Region> QC< / Region>
    < Region> PR< / Region>
    < Region> ON< / Region>
    < / Regions>
    < OptionCodes>
    < OptionCode> LMM< / OptionCode>
    < / OptionCodes>
    < / RDCItem>
    < / GetDataResult>

    Procedure :-


    create or replace procedure proc_rdc
    as
    l_bfile BFILE;
    l_clob CLOB;
    l_parser dbms_xmlparser.Parser;
    l_doc dbms_xmldom.DOMDocument;
    l_nl dbms_xmldom.DOMNodeList;
    l_n dbms_xmldom.DOMNode;
    l_temp VARCHAR2(1000);
    l_attrs dbms_xmldom.DOMNamedNodeMap;
    l_attr dbms_xmldom.DOMNode;

    l_users dbms_xmldom.domnodelist;
    l_user dbms_xmldom.domnode;

    l_type rdc.type%TYPE;

    TYPE tab_type IS TABLE OF rdc%ROWTYPE;

    l_region rdc.region1%TYPE;

    t_tab tab_type := tab_type();
    BEGIN
    l_bfile := BFileName('XML_DIR', 'rdc.xml');
    dbms_lob.createtemporary(l_clob, cache=>FALSE);
    dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
    dbms_lob.loadFromFile(dest_lob => l_clob,
    src_lob => l_bfile,
    amount => dbms_lob.getLength(l_bfile));
    dbms_lob.close(l_bfile);

    dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MM-YYYY''');

    l_parser := dbms_xmlparser.newParser;
    dbms_xmlparser.parseClob(l_parser, l_clob);
    l_doc := dbms_xmlparser.getDocument(l_parser);
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);

    l_users := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(l_doc), '/GetDataResult/RDCItem');

    l_attrs := dbms_xmldom.getattributes(l_user);
    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/GetDataResult/RDCItem');

    l_n := dbms_xslprocessor.selectSingleNode(xmldom.makeNode(l_doc),'/GetDataResult/RDCItem');

    l_attrs := dbms_xmldom.getattributes(l_n);
    l_attr := dbms_xmldom.item(l_attrs, 0);
    DBMS_OUTPUT.put_line(dbms_xmldom.getNodeName(l_attr) || '=' || dbms_xmldom.getNodeValue(l_attr));
    dbms_output.put_line('hi' || dbms_xmldom.getLength(l_nl));

    FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP


    dbms_output.put_line('hi inside');
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    dbms_output.put_line('one');
    l_attrs := xmldom.getattributes (l_n);

    l_attr := dbms_xmldom.ITEM (l_attrs, 0);

    IF dbms_xmldom.getnodename (l_attr) = 'Type' THEN
    l_type := dbms_xmldom.getnodevalue (l_attr);
    END IF;


    t_tab.extend;
    --dbms_xslprocessor.valueOf(l_n,'RDCItem/text()',t_tab(t_tab.last).type);
    dbms_xslprocessor.valueOf(l_n,'Program/text()',t_tab(t_tab.last).Program);
    dbms_xslprocessor.valueOf(l_n,'ModelCode/text()',t_tab(t_tab.last).modelcode);
    dbms_xslprocessor.valueOf(l_n,'ModelYear/text()',t_tab(t_tab.last).modelyear);
    dbms_xslprocessor.valueOf(l_n,'Model/text()',t_tab(t_tab.last).model);
    dbms_xslprocessor.valueOf(l_n,'Amount/text()',t_tab(t_tab.last).amount);
    dbms_xslprocessor.valueOf(l_n,'EndDate/text()',t_tab(t_tab.last).enddate);

    t_tab(t_tab.last).type := l_type;
    END LOOP;
    dbms_output.put_line('hi' || t_tab.first || ' ' || t_tab.first );
    FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO rdc
    (program,
    modelcode,
    Model,
    type,
    modelyear,
    amount,
    enddate
    )
    VALUES
    (t_tab(cur_emp).program,
    t_tab(cur_emp).modelcode,
    t_tab(cur_emp).model,
    t_tab(cur_emp).type,
    t_tab(cur_emp).modelyear,
    t_tab(cur_emp).amount,
    t_tab(cur_emp).enddate
    );

    END LOOP;
    COMMIT;
    dbms_xmldom.freeDocument(l_doc);
    END;
    /
    Last edited by Tarus; 01-05-2009 at 12:23 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width