SQL & PL/SQL

Thank you for your time.

I am trying to use method 1 mentioned in reply 1 of https://community.oracle.com/thread/1115266?tstart=0

Please suggest how to modify the XML part of Method 1 to suit my XML?

I am also including the code mentioned in Method 1 for convenience

Create or replace procedure parse_xml is
  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_file      dbms_xmldom.DOMNodeList;
  l_filen       dbms_xmldom.DOMNode;
  lv_value VARCHAR2(1000);
   l_ch      dbms_xmldom.DOMNode;

l_partname varchar2(100);
l_filename varchar2(1000);
  l_temp    VARCHAR2(1000);
  TYPE tab_type IS TABLE OF tab_software_parts%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
  l_bfile := BFileName('DIR1', 'SoftwareParts.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-MON-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_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/PartDetails/Part');
  
    FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);
    t_tab.extend;
  
    dbms_xslprocessor.valueOf(l_n,'Name/text()',l_partname);
    t_tab(t_tab.last).partname := l_partname;
    l_file := dbms_xslprocessor.selectNodes(l_n,'Files/FileName');

    FOR cur_ch IN 0 .. dbms_xmldom.getLength(l_file) - 1 LOOP
      l_ch := dbms_xmldom.item(l_file, cur_ch);
      lv_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_ch));
      
      if t_tab(t_tab.last).partname is null then t_tab(t_tab.last).partname := l_partname; end if;
      t_tab(t_tab.last).filename := lv_value;
    t_tab.extend;
    
   END LOOP;

   END LOOP;
    t_tab.delete(t_tab.last);
  
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
  
  if t_tab(cur_emp).partname is not null and  t_tab(cur_emp).filename is not null then
    INSERT INTO tab_software_parts
    VALUES
    (t_tab(cur_emp).partname, t_tab(cur_emp).filename);
    end if;
    
  END LOOP;
  COMMIT;

  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 have an XML file in the following format.

<TWObject className="TWObject">

  <array size="240">

    <item>

      <variable type="QuestionDetail">

        <questionId type="String"><![CDATA[30]]></questionId>

        <questionType type="questionType"><![CDATA[COUNTRY]]></questionType>

        <country type="String"><![CDATA[GB]]></country>

        <questionText type="String"><![CDATA[Please indicate if the following "Type of Market Research" applies to your Project : <br><br>Detail Follow-up Study OR Message Recall Study fielded from a Lilly Call/Target List]]></questionText>

        <optionType type="String"><![CDATA[RadioButton]]></optionType>

        <answerOptions type="String[]">

          <item><![CDATA[Yes]]></item>

          <item><![CDATA[No]]></item>

        </answerOptions>

        <ruleId type="String"><![CDATA[CRP_GB001]]></ruleId>

        <parentQuestionId type="String"></parentQuestionId>

        <parentQuestionResp type="String"></parentQuestionResp>

      </variable>

    </item>

     </array>

</TWObject>