This discussion is archived
2 Replies Latest reply: Jan 6, 2013 7:39 PM by SigCle RSS

Insert XML data to Table -> return with null value

SigCle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks so much Sir for your help.

    Best Regards,
    Sigcle

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points