2 Replies Latest reply: Jan 6, 2013 9:39 PM by SigCle RSS

    Insert XML data to Table -> return with null value

    SigCle
      Dear Experts,

      -- I have xml table as below :

      CREATE TABLE example(XML_spec XMLTYPE);

      insert into example
      select '<Message name="dataStaticInvestor" type="IncomingMessage">
      <Field name="batchReference">OPENINGBATCH000000</Field><List name="data">
      <Record name="data">
      <Field name="externalReference">01234567890aaaaaaa</Field>
      <Field name="participantID">OD001</Field>
      <Field name="participantName">EQUITY SECURITIES INDONESIA,PT</Field>
      </Record>
      <Record name="data">
      <Field name="externalReference">01234567890aaaaaaa</Field>
      <Field name="participantID">OD001</Field>
      <Field name="participantName">EQUITY SECURITIES INDONESIA,PT</Field>
      </Record>
      <Record name="data">
      <Field name="externalReference">01234567890aaaaaaa</Field>
      <Field name="participantID">OD001</Field>
      <Field name="participantName">EQUITY SECURITIES INDONESIA,PT</Field>
      </Record>
      </List>
      </Message>' from dual;

      select * from example;

      create table hasil1 (c1 varchar2(500),c2 varchar2(500),c3 varchar2(500));

      -- this step I create procedure to insert xml data into table as batching.

      DECLARE
      x XMLTYPE;
      BEGIN
      select XML_SPEC into x from example;

      insert into hasil1
      SELECT
      p.Extract('/Record/Field/@externalReference').getstringval() as c1,
      p.Extract('/Record/Field/@participantID').getstringval() as c2,
      p.Extract('/Record/Field/@participantName').getstringval() as c3
      FROM TABLE(XMLSequence(Extract(x,'Message/List/Record'))) p;
      commit;
      END;
      /

      -- when the result from select from hasil1, the output is return 3 rows and 3 columns but all data is null value*

      Best regards,
      Sigcle
        • 1. Re: Insert XML data to Table -> return with null value
          odie_63
          You didn't explain what output you require but I suppose something like this :
          SQL> insert into hasil1 (c1, c2, c3)
            2  select x.c1, x.c2, x.c3
            3  from example t
            4     , xmltable(
            5         'Message/List/Record'
            6         passing t.xml_spec
            7         columns c1 varchar2(500) path 'Field[@name="externalReference"]'
            8               , c2 varchar2(500) path 'Field[@name="participantID"]'
            9               , c3 varchar2(500) path 'Field[@name="participantName"]'
           10       ) x
           11  ;
           
          3 rows inserted
           
          SQL> select * from hasil1;
           
          C1                     C2        C3
          ---------------------- --------- --------------------------------
          01234567890aaaaaaa     OD001     EQUITY SECURITIES INDONESIA,PT
          01234567890aaaaaaa     OD001     EQUITY SECURITIES INDONESIA,PT
          01234567890aaaaaaa     OD001     EQUITY SECURITIES INDONESIA,PT
           
          • 2. Re: Insert XML data to Table -> return with null value
            SigCle
            Thanks so much Sir for your help.

            Best Regards,
            Sigcle