2 Replies Latest reply on Mar 7, 2014 8:19 PM by Jason_(A_Non)

    Insert XML data to a table

    Rajanjai

      Hi,

            I am working on XML data.Where I have a xml file like this

      <ROOT>

          <HEAD>

            <HEAD1>ABCD</HEAD1>

            <DATE1>30-12-2013</DATE1>

          </HEAD>

          <TESTING>

           <APP_TEST>ASDF</APP_TEST>

          </TESTING>

         </ROOT>

      We need to insert this into table, we tried the below script and we are unable to insert as single record

       

       

      DECLARE

          L_CTX  DBMS_XMLSTORE.CTXTYPE;

          l_rows NUMBER;

        L_CTX1  DBMS_XMLSTORE.CTXTYPE;

        BEGIN

          l_ctx := dbms_xmlstore.newcontext('TABLENAME');

           DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(L_CTX);

               

             DBMS_XMLSTORE.SETROWTAG(L_CTX,'HEAD');

                 DBMS_XMLSTORE.SETUPDATECOLUMN(L_CTX, 'HEAD1');

                 DBMS_XMLSTORE.SETUPDATECOLUMN(L_CTX, 'DATE1');

         

             DBMS_XMLSTORE.SETROWTAG(L_CTX1,'TESTING');

                 DBMS_XMLSTORE.SETUPDATECOLUMN(L_CTX1, 'APP_TEST');

          

         L_ROWS := DBMS_XMLSTORE.INSERTXML(L_CTX,'

         <ROOT>

          <HEAD>

            <HEAD1>ABCD</HEAD1>

            <DATE1>30-12-2013</DATE1>

          </HEAD>

          <TESTING>

           <APP_TEST>ASDF</APP_TEST>

          </TESTING>

         </ROOT>');

          DBMS_XMLSTORE.CLOSECONTEXT(L_CTX);

       

        END;

       

      OutPut:

      SELECT * FROM TABLENAME

       

      HEAD1       DATE1           APP_TEST

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

      ABCD         30-12-2013     (NULL)

      (NULL)        (NULL)           04

       

       

      BUT I NEED LIKE THIS

       

      HEAD1               DATE1           APP_TEST

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

      ABCD                 30-12-2013     

       

       

      Please suggest the correction that I need to do.

        • 1. Re: Insert XML data to a table
          AndreyKa

          • 2. Re: Insert XML data to a table
            Jason_(A_Non)

            What about something simple as

            INSERT INTO tablename
            SELECT head1, date1, app_test
              FROM XMLTABLE('/ROOT'
                            PASSING XMLType('<ROOT>
                <HEAD>
                  <HEAD1>ABCD</HEAD1>
                  <DATE1>30-12-2013</DATE1>
                </HEAD>
                <TESTING>
                 <APP_TEST>ASDF</APP_TEST>
                </TESTING>
               </ROOT>')
                            COLUMNS
                            head1     VARCHAR2(10) PATH 'HEAD/HEAD1',
                            date1     VARCHAR2(10) PATH 'HEAD/DATE1',
                            app_test  VARCHAR2(10) PATH 'TESTING/APP_TEST') xt1;
            

            where the SELECT returns

            HEAD1  DATE1       
            APP_TEST

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

            ABCD   30-12-2013  ASDF