2 Replies Latest reply: Dec 13, 2012 2:47 PM by 937454 RSS

    how to insert xml into a table in different format

    937454
      Hi,
      My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

      I have an xml like below:
      <?xml version="1.0"?>
          <ROWSET>
          <ROW>
          <a>CNET</a>
          <b>21416911</b>
          <c>12345678</c>
          <d>878787</d>
          </ROW>
      </ROWSET>
      
      creating a test table:
      CREATE TABLE test (
          friendlyname VARCHAR2(150),
          value varchar2(200));
      I want to insert the xml into the table test as below
      SELECT * FROM test;
      friendlyname value
      a   CNET
      b   21416911
      c   12345678
      d   878787
      Please advice.
        • 1. Re: how to insert xml into a table in different format
          odie_63
          Here's the idea :
          SQL> DECLARE
            2  
            3    xmldoc xmltype := xmltype(
            4  '<?xml version="1.0"?>
            5      <ROWSET>
            6      <ROW>
            7      <a>CNET</a>
            8      <b>21416911</b>
            9      <c>12345678</c>
           10      <d>878787</d>
           11      </ROW>
           12  </ROWSET>') ;
           13  
           14  BEGIN
           15  
           16    INSERT INTO test (friendlyname, value)
           17    SELECT x.fname, x.value
           18    FROM XMLTable(
           19           '/ROWSET/ROW/*'
           20           passing xmldoc
           21           columns fname varchar2(150) path 'local-name(.)'
           22                 , value varchar2(200) path 'text()'
           23         ) x ;
           24  
           25  END;
           26  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from test;
           
          FRIENDLYNAME     VALUE
          ---------------- ------------
          a                CNET
          b                21416911
          c                12345678
          d                878787
           
          May be further optimized depending on the size of input XML, and its location.