title: Easiest way to parse XML using pl/sql dom parser
body: <p>I have no idea how to explain but I will try my best. I am using Oracle Apex and have XML in my table called "CLOBTABLE" with datatype Clob. I have tried XMLTYPE as a datatype but its not accepting any data in apex, so i am using CLOB Currently as a data type. now i want to use data which is in xml form i.e. </p>
  
  <pre><code>&lt;Row&gt;
      &lt;cellArray&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;1&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;IR000024575452&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;5&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;AZ12579856-001&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;2&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;IT06686&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;9&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;Hu Mics Metab K&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;8&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;2006-03-31&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;7&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;2006-07-27&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
      &lt;/cellArray&gt;
  &lt;/Row&gt;
  &lt;Row&gt;
      &lt;cellArray&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;1&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;IR000024575452&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;5&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;AZ12579856-001&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;2&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;IT06686&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;9&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;Hu Mics Metab K&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;8&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;2006-03-31&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
          &lt;Cell&gt;
              &lt;columnId&gt;7&lt;/columnId&gt;
              &lt;valueArray&gt;
                  &lt;Value&gt;
                      &lt;value&gt;2006-07-27&lt;/value&gt;
                  &lt;/Value&gt;
              &lt;/valueArray&gt;
          &lt;/Cell&gt;
      &lt;/cellArray&gt;
  &lt;/Row&gt;
  </code></pre>
  
  <p>and want to parse this data and insert it in another table called "Hyp". each  data corresponds to the column id of the database table and rest are the column values. i have tried Oracle XMLTYPE parser which is</p>
  
  <pre><code>DECLARE
      indoc    VARCHAR2(2000);
      indomdoc DBMS_XMLDOM.DOMDocument;
      innode   DBMS_XMLDOM.DOMNode;
      myparser DBMS_XMLPARSER.parser;
      buf      VARCHAR2(2000);
  BEGIN
      indoc := '&lt;emp&gt;&lt;name&gt;De Selby&lt;/name&gt;&lt;/emp&gt;';
      myParser := DBMS_XMLPARSER.newParser;
      DBMS_XMLPARSER.parseBuffer(myParser, indoc);
      indomdoc := DBMS_XMLPARSER.getDocument(myParser);
      innode := DBMS_XMLDOM.makeNode(indomdoc);
      DBMS_XMLDOM.writeToBuffer(innode, buf);
      DBMS_OUTPUT.put_line(buf);
      DBMS_XMLDOM.freeDocument(indomdoc);
      DBMS_XMLPARSER.freeParser(myParser);
  END;
  </code></pre>
  
  <p>but now i want to instead of using static content i.e. De Selby give dynamic content i.e.  from that "CLOBTABLE" xml is given, data is parsed and inserted into "Hyp" table. I don't know how to do it. Any help will be greatly appreciated.</p>