How to insert xml string into Oracle database in a stored procedure?

Question

My XML data is:

<NewDataSet>
         <AotReversefeedback>
         <Refid>N161144</Refid>
         <DPID />
         <TrdAccOpenId>92021144            </TrdAccOpenId>
         <TrdAccOpenDate>May  7 2018 12:00AM </TrdAccOpenDate>
         <EntryDate>25/03/2018</EntryDate>
         <ITicketStatus>POA</ITicketStatus>
         <LastupdatedDate>07/05/2018</LastupdatedDate>
         <Status>ACTIVE</Status>
         </AotReversefeedback>
         <AotReversefeedback>
         <Refid>N202240</Refid>
         <DPID />
         <TrdAccOpenId>83082240            </TrdAccOpenId>
         <TrdAccOpenDate>May  7 2018 12:00AM </TrdAccOpenDate>
         <EntryDate>03/05/2018</EntryDate>
         <ITicketStatus>KRA</ITicketStatus>
         <LastupdatedDate>07/05/2018</LastupdatedDate>
         <Status>ACTIVE</Status>
         </AotReversefeedback>
</NewDataSet>

and my table structure is

 create table LMSDATA
 (
         refid      nvarchar2(20),
         DPID      NVARCHAR2(20),
         trdaccopenid  number(9),
         trdaccopendate    nvarchar2(20),
         entrydate        date,
         iticketstatus nvarchar2(20),
         lastupdateddate date,
         status nvarchar2(20)
 );

The stored procedure will get input string which has xml data. What method can be used to insert XML data into table?

Duplicate of oracle plsql: how to parse XML and insert into table — Kaushik Nayak
– Kaushik Nayak, Commented Jul 19, 2018 at 7:17

Wernfried Domscheit · Accepted Answer · 2018-07-19 07:22:11Z

4

Would be this one:

WITH t AS 
    (SELECT XMLTYPE(
    '<NewDataSet>
        <AotReversefeedback>
            <Refid>N161144</Refid>
            <DPID />
            <TrdAccOpenId>92021144            </TrdAccOpenId>
            <TrdAccOpenDate>May  7 2018 12:00AM </TrdAccOpenDate>
            <EntryDate>25/03/2018</EntryDate>
            <ITicketStatus>POA</ITicketStatus>
            <LastupdatedDate>07/05/2018</LastupdatedDate>
            <Status>ACTIVE</Status>
        </AotReversefeedback>
        <AotReversefeedback>
            <Refid>N202240</Refid>
            <DPID />
            <TrdAccOpenId>83082240            </TrdAccOpenId>
            <TrdAccOpenDate>May  7 2018 12:00AM </TrdAccOpenDate>
            <EntryDate>03/05/2018</EntryDate>
            <ITicketStatus>KRA</ITicketStatus>
            <LastupdatedDate>07/05/2018</LastupdatedDate>
            <Status>ACTIVE</Status>
        </AotReversefeedback>
    </NewDataSet>') AS XML_DATA
FROM dual)
SELECT 
    refid,
    DPID,
    trdaccopenid,
    trdaccopendate,
    TO_DATE(entrydate_str, 'dd/mm/yyyy') AS entrydate,
    iticketstatus,
    TO_DATE(LastupdatedDate_str, 'dd/mm/yyyy') AS LastupdatedDate,
    status
FROM t
    CROSS JOIN XMLTABLE('/NewDataSet/AotReversefeedback' PASSING XML_DATA COLUMNS 
        refid      NVARCHAR2(20) PATH 'Refid',
        DPID      NVARCHAR2(20) PATH 'DPID',
        trdaccopenid  NUMBER(9) PATH 'TrdAccOpenId',
        trdaccopendate    NVARCHAR2(20) PATH 'TrdAccOpenDate',
        entrydate_str        VARCHAR2(15) PATH 'EntryDate',
        iticketstatus NVARCHAR2(20) PATH 'ITicketStatus',
        lastupdateddate_str VARCHAR2(15) PATH 'LastupdatedDate',
        status NVARCHAR2(20) PATH 'Status'      
 ) x;

Results:

REFID   DPID    TRDACCOPENID    TRDACCOPENDATE  ENTRYDATE   ITICKETSTATUS   LASTUPDATEDDATE STATUS
N161144     92021144    May  7 2018 12:00AM     25.03.2018  POA 07.05.2018  ACTIVE
N202240     83082240    May  7 2018 12:00AM     03.05.2018  KRA 07.05.2018  ACTIVE

ExtractValue will also works but the function is deprecated.

edited Jul 19, 2018 at 7:22

answered Jul 19, 2018 at 7:19

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vaibhav Save Over a year ago

I dont have fixed xml no of rows can be different all time and i m getting string xml as input to sp.

Wernfried Domscheit Over a year ago

It works with any number of <AotReversefeedback> elements.

Collectives™ on Stack Overflow

How to insert xml string into Oracle database in a stored procedure?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related