Parse XML and Load in oracle Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Parse XML and Load in oracle Tables

  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.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Use XSLT (the xmltype.transform function) to extract the Regions node to another XML document and transform it to the oracle canonical form <ROWSET><ROW> ... </ROW><ROW>...</ROW>...</ROWSET>. Then use dbms_xmlstore to insert data into table.
    I think you could use the same practice for the rest of the document and avoid the "node_after_node" parsing.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

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