CODEWITHSUNDEEP
asp.netxmloracle-databaseoracle11goracle10g
Vaibhav Save
Vaibhav Save

Reputation: 56

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

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?

Upvotes: 1

Views: 1247

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 4

Related Questions