9 Replies Latest reply: Aug 29, 2013 2:46 AM by 996599 RSS

    Parsing xml file

    996599

      hi

      plz help me how to parse XML file using oracle.I have XML file (stored XML file data into table using procedure).

       

       

      <?xml version='1.0' encoding='utf-8' ?>

      <NETWORKXML NAME='EA_CONNECTIVITY XML' VERSION= '1.0'>

       

      <FEATURES>

      <FEATURE FEATURENAME='SUBSTATION' >

      <SUBSTATION  SUBSTATIONID = ''/>

      </FEATURE>

       

      <FEATURE FEATURENAME='SWITCHGEAR' OBJECTID='2478' PARENTOBJECTID='' PARENTNAME ='' >

      <SWITCHGEAR  X='265362.0648' Y='2181321.0296' OBJECTID='2478' ENABLED='1' ANCILLARYROLE='1' ELECTRICTRACEWEIGHT='79616' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' PHASEDESIGNATION='7' LOCATIONCODE='265362.0648,2181321.0296' INSTALLEDLOCATIONDETAILS='PALGHAR SS' VOLTAGE='340' SERIALNUMBER='0310' DESCRIPTION='NA' SYMBOLROTATION='180' CREATIONUSER='YATISH.GHARAT' DATECREATED='10/16/2012 12:00:00 AM' DATEMODIFIED='10/16/2012 12:00:00 AM' LASTUSER='YATISH.GHARAT' INSULATIONTYPE='VCB' NIN='284023/205/G/001' ASSETID='284023/205/G/001' RATING='800' TYPE='1' MECHANISM='MANUAL' RATEDPOWER='0.400092' MAKE='108' SURVEYORREMARKS='TRANSFORMER NO=02 BREKAR' SYMMETRICALBREAKINGCURRENT='21' USERDEFINEDFIELD1='NAME PLATE NOT AVAILABLE' USERDEFINEDFIELD2='' WORKORDERID='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='CIRCUITSOURCE' OBJECTID='127' PARENTOBJECTID='2478' PARENTNAME ='SWITCHGEAR' >

      <CIRCUITSOURCE OBJECTID='127' FeederId='003/284023/205' FeederId2='' FeederInfo='' SubstationId='284023' SwitchGearObjId='2478' FeederName='INDUSTRIAL' FeederSourceInfo='0' WorkOrderID='' Cir_Div_SubD_BU_Sec='540/543/162/4162/001' ToSubStationID=''/>

      </FEATURE>

       

      <FEATURE FEATURENAME='CURRENTTRANSFORMER' OBJECTID='4642' PARENTOBJECTID='2478' PARENTNAME ='SWITCHGEAR' >

      <CURRENTTRANSFORMER  X='265362.0648' Y='2181319.2888' OBJECTID='4642' LOCATIONCODE='265362.0648,2181319.2888' INSTALLEDLOCATIONDETAILS='IN PALGHAR SUBSTATION' MAKE='108' SERIALNUMBER='NV' SYMBOLROTATION='0' CREATIONUSER='RUPESH.MHATRE' DATECREATED='9/26/2012 12:00:00 AM' DATEMODIFIED='9/26/2012 12:00:00 AM' LASTUSER='RUPESH.MHATRE' SURVEYORREMARKS=' ' ASSETID='' NIN='284023/205/I/008' CTRATIO='100/5' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='16384' ENABLED='1' CORECLASS='0.5' PERMITTEDLOAD='10' COREPURPOSE='METERING &amp;AMP; PROTECTION' PHASEDESIGNATION='7' VOLTAGE='340' CORENUMBER='2' WORKORDERID='' CTPTCOMBINED='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' PTRATIO='11000/110' PRMETERSRNO='09191885'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='276168' PARENTOBJECTID='4642' PARENTNAME ='CURRENTTRANSFORMER' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265362.0648' Y='2181318.569' OBJECTID='276168' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='PRIMARYMETER' OBJECTID='11250' PARENTOBJECTID='276168' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <PRIMARYMETER  X='265363.5648' Y='2181318.569' OBJECTID='11250' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='16384' KW='YES' KVAR='YES' KVA='YES' SERIALNUMBER='09191885' DATEANDTIME='' SYMBOLROTATION='' CREATIONUSER='YATISH.GHARAT' DATECREATED='12/17/2012 12:00:00 AM' LASTUSER='YATISH.GHARAT' NIN='284023/205/M/001' PHASEDESIGNATION='7' ASSETID='284023/205/M/001' VOLTAGE='120' SURVEYORREMARKS='MIDC, INDARSTEAL 205 METER 11 KV' CTRATIO='5/5' DATEMODIFIED='' LOCATIONCODE='265363.5648,2181318.569' MAXIMUMDEMAND='10' TYPE='CT OPERATED' MAKE='053' LOCATION='METER PANAL' CROSSOVER='NO' USERDEFINEDFIELD1='' USERDEFINEDFIELD2='12/17/2012 12:00:00 AM' WORKORDERID='' KWH='337759' DTC_HTCNUMBER_POLECODE_FCODE='003/284023/205' TOWNFEEDER='OUT GOING' COST='' EAEQUIPMENT='' NOOFDIGITS_READINGFREQ='006/001/1/234' MULTIPLIFACTOR='20' CONSUMPTIONATDISCONNECTION='' AMPERE='5' POWERFACTOR='YES' PTRATIO='11000/110'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='SWITCH' OBJECTID='3691' PARENTOBJECTID='276168' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <SWITCH  X='265362.0648' Y='2181317.8491' OBJECTID='3691' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='4096' PHASEDESIGNATION='7' LOCATIONCODE='265362.0648,2181317.8491' TYPE='2' SYMBOLROTATION='180' CREATIONUSER='YATISH.GHARAT' DATECREATED='11/9/2012 12:00:00 AM' DATEMODIFIED='11/9/2012 12:00:00 AM' LASTUSER='YATISH.GHARAT' SURVEYORREMARKS='11KVOUT GOING LINE INDUSTRIAL FEEDER' NIN='284023/205/S/001' ASSETID='284023/205/S/001' VOLTAGE='340' CATEGORY='AB SWITCH' RATINGAMPS='800' MAKE='999' SERIALNUMER='' WORKORDERID='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='149351' PARENTOBJECTID='3691' PARENTNAME ='SWITCH' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265362.0648' Y='2181316.4093' OBJECTID='149351' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='149352' PARENTOBJECTID='149351' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265365.9546' Y='2181354.5006' OBJECTID='149352' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='FUSE' OBJECTID='33261' PARENTOBJECTID='149352' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <FUSE  X='265365.7507' Y='2181354.6453' OBJECTID='33261' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='5888' LOCATIONCODE='265365.7507,2181354.6453' VOLTAGE='340' PHASEDESIGNATION='7' SYMBOLROTATION='234.63814093' CREATIONUSER='DARSHAN.PATIL' DATECREATED='9/20/2012 12:00:00 AM' DATEMODIFIED='9/20/2012 12:00:00 AM' LASTUSER='DARSHAN.PATIL' COMMENTS=' ' FUSETYPE='1' NIN='284023/205/U/007' FAULTCURRENT='6' MAKE='999' SERIALNUMBER='NA' WORKORDERID='' RATING='5' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='DISTRIBUTIONTRANSFORMER' OBJECTID='5258' PARENTOBJECTID='33261' PARENTNAME ='FUSE' >

      <DISTRIBUTIONTRANSFORMER  X='265365.5468' Y='2181354.7899' OBJECTID='5258' ENABLED='1' ANCILLARYROLE='' LOCATIONCODE='265365.5468,2181354.7899' CAPACITY='4' HIGHVOLTAGESIDEVOLTS='340' HTPROTECTION='FUSE' LOWVOLTAGESIDEVOLTS='210' LTPROTECTION='FUSE' TRANSFORMERMOUNTING='DOUBLE POLE' PHASEDESIGNATION='7' VECTORGROUP='DYN11' MAKE='999' SERIALNUMBER='NV' TNNUMBER='NV' SYMBOLROTATION='234.65683342' FEEDERID='003/284023/205' FEEDERID2='' ELECTRICTRACEWEIGHT='16384' FEEDERINFO='7' CREATIONUSER='RAJIV.LOCHAN' DATECREATED='11/8/2012 12:00:00 AM' DATEMODIFIED='11/8/2012 12:00:00 AM' LASTUSER='RAJIV.LOCHAN' SURVEYORREMARKS='NV' STARRATING='WITHOUT STAR' NUMBEROFLTFEEDERS='0' PERCENTAGEIMPEDANCE='4.5' NIN='284023/205/D/001' ASSETID='284023/205/D/001' NOLOADLOSSES='180' FULLLOADLOSSES='1415' LTFEEDERPROTECTION='FUSE' TYPEOFCORE='CRGO' MAXIMUMTAP='10010' MINIMUMTAP='11330' ADOPTEDTAP='2' CSPTYPE='WITHOUT CSP' DTCCODE='4162126' DTCNAME='STORE' CUSTOMERCLASSCODE='INDUSTRIAL' NUMBEROFSTEPS='5' MAGNETIZINGCURRENT='1.25' USERDEFINEDFIELD1='44.1' DTCURRENTID='003/284023/205/DT/001' USERDEFINEDFIELD2='NA' USERDEFINEDFIELD3='' WORKORDERID='' USERDEFINEDFIELD5='4162126' CONSUMPTIONTYPE='TC_0' SANCTIONEDLOAD='0' CURRENTRATING='84' HVSIDECURRENTRATING='3.30674' LVSIDECURRENTRATING='84.0049' COOLINGTYPE='OIL' JOBNUMBER='' EARTHING='YES' DATEOFCOMMISSIONING='' COMPACTTYPE='NO' COST='112019' EAEQUIPMENT='DT_63KVA_11KV_0.4KV' TERTIARYVOLTAGE='' CIR_DIV_SUBD_BU_SEC='540/543/162/4162/001' CPFNO='02228947' DTCTYPE='13'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='SWITCHGEAR' OBJECTID='46042' PARENTOBJECTID='5258' PARENTNAME ='DISTRIBUTIONTRANSFORMER' >

      <SWITCHGEAR  X='265365.3429' Y='2181354.9346' OBJECTID='46042' ENABLED='1' ANCILLARYROLE='0' ELECTRICTRACEWEIGHT='5888' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' PHASEDESIGNATION='7' LOCATIONCODE='265365.3429,2181354.9346' INSTALLEDLOCATIONDETAILS='IN FEEDER PILLAR' VOLTAGE='210' SERIALNUMBER='' DESCRIPTION='' SYMBOLROTATION='' CREATIONUSER='' DATECREATED='' DATEMODIFIED='' LASTUSER='' INSULATIONTYPE='MCCB' NIN='284023/205/D/001/X/G001' ASSETID='284023/205/D/001/X/G001' RATING='100' TYPE='1' MECHANISM='MOTOR/MANUAL' RATEDPOWER='0.018619' MAKE='999' SURVEYORREMARKS='' SYMMETRICALBREAKINGCURRENT='25' USERDEFINEDFIELD1='' USERDEFINEDFIELD2='' WORKORDERID='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='CURRENTTRANSFORMER' OBJECTID='4367' PARENTOBJECTID='46042' PARENTNAME ='SWITCHGEAR' >

      <CURRENTTRANSFORMER  X='265365.139' Y='2181355.0792' OBJECTID='4367' LOCATIONCODE='265365.139,2181355.0792' INSTALLEDLOCATIONDETAILS='ON POLE' MAKE='179' SERIALNUMBER='NV' SYMBOLROTATION='234.65683344' CREATIONUSER='RUPESH.MHATRE' DATECREATED='9/26/2012 12:00:00 AM' DATEMODIFIED='9/26/2012 12:00:00 AM' LASTUSER='RUPESH.MHATRE' SURVEYORREMARKS='DTC NO - 62459' ASSETID='' NIN='284023/205/D/001/X/I001' CTRATIO='100/5' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='16384' ENABLED='1' CORECLASS='0.5' PERMITTEDLOAD='15' COREPURPOSE='METERING' PHASEDESIGNATION='7' VOLTAGE='210' CORENUMBER='1' WORKORDERID='' CTPTCOMBINED='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' PTRATIO='1/1' PRMETERSRNO='00106330'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='2105947' PARENTOBJECTID='4367' PARENTNAME ='CURRENTTRANSFORMER' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265365.037' Y='2181355.1515' OBJECTID='2105947' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='SWITCH' OBJECTID='3888' PARENTOBJECTID='2105947' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <SWITCH  X='265364.9351' Y='2181355.2239' OBJECTID='3888' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='4096' PHASEDESIGNATION='7' LOCATIONCODE='265364.9351,2181355.2239' TYPE='2' SYMBOLROTATION='54.67008837' CREATIONUSER='' DATECREATED='' DATEMODIFIED='' LASTUSER='YATISH.GHARAT' SURVEYORREMARKS='IN  FEEDER PILLAR' NIN='284023/205/D/001/X/S001' ASSETID='284023/205/D/001/X/S001' VOLTAGE='210' CATEGORY='BUSCOUPLER' RATINGAMPS='200' MAKE='178' SERIALNUMER='' WORKORDERID='' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151293' PARENTOBJECTID='3888' PARENTNAME ='SWITCH' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.7311' Y='2181355.3685' OBJECTID='151293' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151297' PARENTOBJECTID='151293' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.6588' Y='2181355.2666' OBJECTID='151297' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151154' PARENTOBJECTID='151297' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.5865' Y='2181355.1646' OBJECTID='151154' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='FUSE' OBJECTID='33325' PARENTOBJECTID='151297' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <FUSE  X='265364.4549' Y='2181355.4112' OBJECTID='33325' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='5888' LOCATIONCODE='265364.4549,2181355.4112' VOLTAGE='210' PHASEDESIGNATION='7' SYMBOLROTATION='54.63814094' CREATIONUSER='DARSHAN.PATIL' DATECREATED='9/20/2012 12:00:00 AM' DATEMODIFIED='9/20/2012 12:00:00 AM' LASTUSER='DARSHAN.PATIL' COMMENTS='IN X FEEDER PILLAR' FUSETYPE='4' NIN='284023/205/D/001/B/U001' FAULTCURRENT='120' MAKE='178' SERIALNUMBER='NV' WORKORDERID='' RATING='100' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151298' PARENTOBJECTID='33325' PARENTNAME ='FUSE' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.251' Y='2181355.5559' OBJECTID='151298' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151294' PARENTOBJECTID='151293' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.8035' Y='2181355.4705' OBJECTID='151294' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='ELECTRICGEOMNET_JUNCTIONS' OBJECTID='151155' PARENTOBJECTID='151294' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <ELECTRICGEOMNET_JUNCTIONS  X='265364.8758' Y='2181355.5724' OBJECTID='151155' ENABLED='1'/>

      </FEATURE>

       

      <FEATURE FEATURENAME='FUSE' OBJECTID='33280' PARENTOBJECTID='151294' PARENTNAME ='ELECTRICGEOMNET_JUNCTIONS' >

      <FUSE  X='265364.5996' Y='2181355.6151' OBJECTID='33280' ENABLED='1' ANCILLARYROLE='' FEEDERID='003/284023/205' FEEDERID2='' FEEDERINFO='7' ELECTRICTRACEWEIGHT='5888' LOCATIONCODE='265364.5996,2181355.6151' VOLTAGE='210' PHASEDESIGNATION='7' SYMBOLROTATION='54.63814094' CREATIONUSER='DARSHAN.PATIL' DATECREATED='9/20/2012 12:00:00 AM' DATEMODIFIED='9/20/2012 12:00:00 AM' LASTUSER='DARSHAN.PATIL' COMMENTS='IN X FEEDER PILLAR' FUSETYPE='4' NIN='284023/205/D/001/A/U001' FAULTCURRENT='120' MAKE='178' SERIALNUMBER='NV' WORKORDERID='' RATING='100' COST='' EAEQUIPMENT='' DTCCODEDETAILS='' NORMALPOSITION_A='1' NORMALPOSITION_B='1' NORMALPOSITION_C='1'/>

      </FEATURE>

      </FEATURES>

      </NETWORKXML>

        • 1. Re: Parsing xml file
          _Karthick_

          Search for Parse XML in this forum. Also read about XMLTABLE in the document.

          • 2. Re: Parsing xml file
            BluShadow

            Also look at the XML DB forum FAQ: XML DB FAQ

             

            and consider searching the XML DB forum for further examples.

            • 3. Re: Parsing xml file
              996599

              i have trying some procedures with the help of oracle forums.

               

              pls see the below procedure....

               

              ----------------------------------As SYS

              CREATE DIRECTORY xml_dir AS 'c:\';

              GRANT READ ON DIRECTORY xml_dir TO &YOUR_SCHEMA_NAME;

               

               

              put xml file IN c DRIVE

              ----------------------------------BLOCK

              DECLARE

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

               

                TYPE tab_type IS TABLE OF feature%ROWTYPE;

               

                t_tab  tab_type := tab_type();

              BEGIN

               

                l_bfile := BFileName('xml_dir2', 'feature.xml');-----IN C DRIVE

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

               

                -- make sure implicit date conversions are performed correctly

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

               

                -- Create a parser.

                l_parser := dbms_xmlparser.newParser;

               

                -- Parse the document and create a new DOM document.

                dbms_xmlparser.parseClob(l_parser, l_clob);

                l_doc := dbms_xmlparser.getDocument(l_parser);

               

                -- Free resources associated with the CLOB and Parser now they are no longer needed.

                dbms_lob.freetemporary(l_clob);

                dbms_xmlparser.freeParser(l_parser);

               

                -- Get a list of all the EMP nodes in the document using the XPATH syntax.

                l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/NETWORKXML/FEATURE');

               

                -- Loop through the list and create a new record in a tble collection

                -- for each EMP record.

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

                  l_n := dbms_xmldom.item(l_nl, cur_feature);

               

                  t_tab.extend;

               

                  -- Use XPATH syntax to assign values to he elements of the collection.

               

                  dbms_xslprocessor.valueOf(l_n,'NAME/text()',t_tab(t_tab.last).NAME);

                  dbms_xslprocessor.valueOf(l_n,'VERSION/text()',t_tab(t_tab.last).VERSION);

                  dbms_xslprocessor.valueOf(l_n,'FEATURENAME/text()',t_tab(t_tab.last).FEATURENAME);

                  dbms_xslprocessor.valueOf(l_n,'PARENTOBJECTID/text()',t_tab(t_tab.last).PARENTOBJECTID);

                  dbms_xslprocessor.valueOf(l_n,'PARENTNAME/text()',t_tab(t_tab.last).PARENTNAME);

                  END LOOP;

               

                -- Insert data into the real EMP table from the table collection.

                FORALL i IN t_tab.first .. t_tab.last

                  INSERT INTO feature VALUES t_tab(i);

               

                COMMIT;

               

                -- Free any resources associated with the document now it

                -- is no longer needed.

                dbms_xmldom.freeDocument(l_doc);

               

              EXCEPTION

                WHEN OTHERS THEN

                  dbms_lob.freetemporary(l_clob);

                  dbms_xmlparser.freeParser(l_parser);

                  dbms_xmldom.freeDocument(l_doc);

              END;

               

              -- i don't know how to create table structure for above XML file.

              • 4. Re: Parsing xml file
                996599

                Please any one help for this task...

                • 5. Re: Parsing xml file
                  _Karthick_


                  This should give you a start.

                   

                  This your XML data. I have loaded it into a table T. I have removed the XML string from the below example.

                   

                  create table t (xml_col xmltype);

                  declare
                    l_col xmltype;
                  begin
                    l_col := xmltype(q'[..your xml here..]');
                    insert into t (xml_col) values(l_col);
                  end;

                   

                  Now you can parse your XMLTYPE using XMLTABLE Like this.

                   

                  select t1.*
                    from t
                       , xmltable
                         (
                           '/NETWORKXML/FEATURES/FEATURE' passing t.xml_col
                           columns
                           featurename    varchar2(100) path '@FEATURENAME'    ,
                           objectid       varchar2(100) path '@OBJECTID'       ,
                           parentobjectid varchar2(100) path '@PARENTOBJECTID' ,
                           parentname     varchar2(100) path '@PARENTNAME'     ,
                           xml_sub_type   xmltype       path '*'
                         ) t1

                  Here XML_SUB_TYPE column contains the XML under each FEATURE. I am not sure how you want to parse it.          
                   

                  • 6. Re: Parsing xml file
                    996599

                    ---Still i can't understand this XML file.

                    ---These subtype columns are parsing into another table.

                    • 7. Re: Parsing xml file
                      BluShadow

                      996599 wrote:

                       

                      Please any one help for this task...

                       

                      Did you ask on the XML DB forum for help?  That's where most of the XML experts hang out.

                      And did you read the XML DB forum FAQ that I linked to?  That details the various options for reading, shredding and storing XML data in the database, as well as querying it.

                      • 8. Re: Parsing xml file
                        _Karthick_


                        Your problem is not with the technology... Its with the requirement of your business. Not this forum or any other forum is going to solve that. You give us a XML file and ask us to explain you what it contains. That's not going to happen. Sit down with the person who gave you this file and try to understand it. Once you are clear on what is the purpose of the information in this file then comeback here and we can discuss about the possible technical solution that can be used to achieve your requirement.

                        • 9. Re: Parsing xml file
                          996599

                          Thank You...