3 Replies Latest reply: Jul 29, 2013 5:56 AM by odie_63 RSS

    Create Table from XML Data

    936666

      XML info: D:\XMLDATA\mytable.xml

       

      create directory XML_dir as 'D:\XMLDATA';

       

      <INFO>

      <Column_Name>Col1</Column_Name>

      <Data_Type>Char(1)</Data_Type>

      </INFO>

      <INFO>

      <Column_Name>Col2</Column_Name>

      <Data_Type>VARCHAR2(50)</Data_Type>

      </INFO>

       

      I need to create a table based on the XML data.

       

      Create Table mytable

      (

      Col1 Char(1),

      Col2 Varchar2(50)

      );

      How to read and execute the xml data to create a table.

      Thanks!    

        • 1. Re: Create Table from XML Data
          odie_63

          Something like this :

           

          SQL> declare

            2 

            3    v_xmlinfo      clob;

            4    v_ddl          varchar2(32767) := 'CREATE TABLE mytable ( #COLUMN_LIST# )';

            5    v_column_list  varchar2(4000);

            6 

            7  begin

            8 

            9    v_xmlinfo := dbms_xslprocessor.read2clob('TEST_DIR', 'info.xml');

          10 

          11    select column_list

          12    into v_column_list

          13    from xmltable(

          14           'string-join(

          15              for $i in /INFO

          16              return concat($i/Column_Name, " ", $i/Data_Type)

          17            , ", "

          18            )'

          19           passing xmlparse(content v_xmlinfo)

          20           columns column_list varchar2(4000) path '.'

          21         ) ;

          22 

          23 

          24    v_ddl := replace(v_ddl, '#COLUMN_LIST#', v_column_list);

          25    --dbms_output.put_line(v_ddl);

          26 

          27    execute immediate v_ddl;

          28 

          29  end;

          30  /

           

          PL/SQL procedure successfully completed

           

          SQL> select * from mytable;

           

          COL1 COL2

          ---- --------------------------------------------------

           

          • 2. Re: Create Table from XML Data
            936666

            it is not info.xml it is Mytable.xml.

            XML name is mytable it should be able to read the xml header.

            • 3. Re: Create Table from XML Data
              odie_63
              it is not info.xml it is Mytable.xml.

              I just gave you an example. You're free to adapt it to your liking.

               

              XML name is mytable it should be able to read the xml header.

              Not a clue what that means. Could you explain a little more?

              And please remember, you are the one seeking help here, the more useful information you can give, the better answer you'll get.

               

              What's the database version?