Data not inserting into table via XML in Oracle

Question

I am trying to insert few records into one of my Oracle table. So for that I am using XML. Below is the XML which is inserted into the table.

But without any error, it's not inserting the records.

PROCEDURE UploadGTLData
(
  BulkXML CLOB            
 )
 AS
 XMLType SYS.XMLTYPE;
    BEGIN
    XMLType:= SYS.XMLType.CreateXML(BulkXML);
    DECLARE
    
            ID   NVARCHAR2(100);
            SAP_ID   NVARCHAR2(100);
            CITY      NVARCHAR2(100 CHAR);
            CIRCLE      NVARCHAR2(100 CHAR);
            FINAL_SO_DATE     NVARCHAR2(100 CHAR);
            INVOICE_DATE      NVARCHAR2(100 CHAR);
            IP_ID            NVARCHAR2(100 CHAR);
            APPLICABLE_MSA    NVARCHAR2(100 CHAR);
            SITE_CATEGORY     NVARCHAR2(100 CHAR);
            ID_OD             NVARCHAR2(100 CHAR);
            RFI_DATE         NVARCHAR2(100 CHAR);
            BILL_START_DATE   NVARCHAR2(100 CHAR);
            BILL_END_DATE     NVARCHAR2(100 CHAR);
            NO_OF_OPCO        NVARCHAR2(100 CHAR);
            ACTUAL_RENT_AMT   NVARCHAR2(100 CHAR);
            TENURE            NVARCHAR2(100 CHAR);
            MW_ANTENNA_OF_UPTO06_DIA   NVARCHAR2(100 CHAR);
            MW_ANTENNA_OF_12DIA       NVARCHAR2(100 CHAR);
            MW_ANTENNA_OF_GREATER12_DIA    NVARCHAR2(100 CHAR);
            HEIGHT_OF_HEIGHEST_ANTENNA    NVARCHAR2(100 CHAR);
            SITE_RRH_COUNT                  NVARCHAR2(100 CHAR);
            WIND_SPEED                       NVARCHAR2(100 CHAR);
            GREATER_9_RRH                   NVARCHAR2(100 CHAR);
            FLOOR_SPACE_INDOOR              NVARCHAR2(100 CHAR); 
            FLOOR_SPACE_OUTDOOR             NVARCHAR2(100 CHAR);
            EB_STATUS_VALUE                 NVARCHAR2(100 CHAR);
            NO_OF_US                       NVARCHAR2(100 CHAR);
            VOLUME_DISCOUNT                  NVARCHAR2(100 CHAR);           
            
            BEGIN            
            FOR RECD IN           
            (            
            SELECT            
            Records.EXTRACT('//RecodSet/ID/text()').getstringval() As ID,
            Records.EXTRACT('//RecodSet/SAP_ID/text()').getstringval() As SAP_ID,            
            Records.EXTRACT('//RecodSet/CITY/text()').getstringval() As CITY,            
            Records.EXTRACT('//RecodSet/CIRCLE/text()').getstringval() As CIRCLE,
            Records.EXTRACT('//RecodSet/FINAL_SO_DATE/text()').getstringval() As FINAL_SO_DATE,            
            Records.EXTRACT('//RecodSet/INVOICE_DATE/text()').getstringval() As INVOICE_DATE , 
            Records.EXTRACT('//RecodSet/IP_ID/text()').getstringval() As IP_ID  ,          
            Records.EXTRACT('//RecodSet/APPLICABLE_MSA/text()').getstringval() As APPLICABLE_MSA,
            Records.EXTRACT('//RecodSet/SITE_CATEGORY/text()').getstringval() As SITE_CATEGORY,
            Records.EXTRACT('//RecodSet/ID_OD/text()').getstringval() As ID_OD ,           
            Records.EXTRACT('//RecodSet/RFI_DATE/text()').getstringval() As RFI_DATE ,
            Records.EXTRACT('//RecodSet/BILL_START_DATE/text()').getstringval() As BILL_START_DATE,
            Records.EXTRACT('//RecodSet/BILL_END_DATE/text()').getstringval() As BILL_END_DATE ,
            Records.EXTRACT('//RecodSet/NO_OF_OPCO/text()').getstringval() As NO_OF_OPCO  ,
            Records.EXTRACT('//RecodSet/ACTUAL_RENT_AMT/text()').getstringval() As ACTUAL_RENT_AMT,
            Records.EXTRACT('//RecodSet/TENURE/text()').getstringval() As TENURE ,
            Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_UPTO06_DIA /text()').getstringval() As MW_ANTENNA_OF_UPTO06_DIA ,
            Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_12DIA/text()').getstringval() As MW_ANTENNA_OF_12DIA,
            Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_GREATER12_DIA/text()').getstringval() As MW_ANTENNA_OF_GREATER12_DIA ,
            Records.EXTRACT('//RecodSet/HEIGHT_OF_HEIGHEST_ANTENNA/text()').getstringval() As HEIGHT_OF_HEIGHEST_ANTENNA ,
            Records.EXTRACT('//RecodSet/SITE_RRH_COUNT/text()').getstringval() As SITE_RRH_COUNT ,            
            Records.EXTRACT('//RecodSet/WIND_SPEED/text()').getstringval() As WIND_SPEED ,
            Records.EXTRACT('//RecodSet/GREATER_9_RRH/text()').getstringval() As GREATER_9_RRH ,
            Records.EXTRACT('//RecodSet/FLOOR_SPACE_INDOOR/text()').getstringval() As FLOOR_SPACE_INDOOR ,
            Records.EXTRACT('//RecodSet/FLOOR_SPACE_OUTDOOR/text()').getstringval() As FLOOR_SPACE_OUTDOOR ,            
            Records.EXTRACT('//RecodSet/EB_STATUS_VALUE/text()').getstringval() As EB_STATUS_VALUE ,
            Records.EXTRACT('//RecodSet/NO_OF_US/text()').getstringval() As NO_OF_US ,
            Records.EXTRACT('//RecodSet/VOLUME_DISCOUNT/text()').getstringval() As VOLUME_DISCOUNT            
            
            
            FROM TABLE(XMLSequence(XMLType.EXTRACT('//NewDataSet/RecodSet')))Records
            )LOOP
            DBMS_OUTPUT.PUT_LINE('INLOOP');            
            INSERT INTO IPCOLO_IPFEE_CALC_GTL(ID,SAP_ID,CITY,CIRCLE,FINAL_SO_DATE,INVOICE_DATE,IP_ID, APPLICABLE_MSA,SITE_CATEGORY,ID_OD,RFI_DATE,BILL_START_DATE,BILL_END_DATE ,NO_OF_OPCO,ACTUAL_RENT_AMT ,TENURE, MW_ANTENNA_OF_UPTO06_DIA, MW_ANTENNA_OF_12DIA, MW_ANTENNA_OF_GREATER12_DIA, HEIGHT_OF_HEIGHEST_ANTENNA, SITE_RRH_COUNT, WIND_SPEED, GREATER_9_RRH, FLOOR_SPACE_INDOOR, FLOOR_SPACE_OUTDOOR, EB_STATUS_VALUE, NO_OF_US, VOLUME_DISCOUNT)                                    
            VALUES (RECD.ID,RECD.SAP_ID,RECD.CITY,RECD.CIRCLE,RECD.FINAL_SO_DATE,RECD.INVOICE_DATE,RECD.IP_ID, RECD.APPLICABLE_MSA, RECD.SITE_CATEGORY,RECD.ID_OD, RECD.RFI_DATE,RECD.BILL_START_DATE,RECD.BILL_END_DATE , RECD.NO_OF_OPCO, RECD.ACTUAL_RENT_AMT ,RECD.TENURE, RECD.MW_ANTENNA_OF_UPTO06_DIA, RECD.MW_ANTENNA_OF_12DIA, RECD.MW_ANTENNA_OF_GREATER12_DIA, RECD.HEIGHT_OF_HEIGHEST_ANTENNA, RECD.SITE_RRH_COUNT, RECD.WIND_SPEED, RECD.GREATER_9_RRH, RECD.FLOOR_SPACE_INDOOR, RECD.FLOOR_SPACE_OUTDOOR, RECD.EB_STATUS_VALUE, RECD.NO_OF_US, RECD.VOLUME_DISCOUNT);
            
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('OUTLOOP');
            END;


END UploadGTLData;

Also below is the table desc

Name                        Null Type           
--------------------------- ---- -------------- 
ID                               NVARCHAR2(100) 
SAP_ID                           NVARCHAR2(100) 
CITY                             NVARCHAR2(100) 
CIRCLE                           NVARCHAR2(100) 
FINAL_SO_DATE                    NVARCHAR2(100) 
INVOICE_DATE                     NVARCHAR2(100) 
IP_ID                            NVARCHAR2(100) 
APPLICABLE_MSA                   NVARCHAR2(100) 
SITE_CATEGORY                    NVARCHAR2(100) 
ID_OD                            NVARCHAR2(100) 
RFI_DATE                         NVARCHAR2(100) 
BILL_START_DATE                  NVARCHAR2(100) 
BILL_END_DATE                    NVARCHAR2(100) 
NO_OF_OPCO                       NVARCHAR2(100) 
ACTUAL_RENT_AMT                  NVARCHAR2(100) 
TENURE                           NVARCHAR2(100) 
MW_ANTENNA_OF_UPTO06_DIA         NVARCHAR2(100) 
MW_ANTENNA_OF_12DIA              NVARCHAR2(100) 
MW_ANTENNA_OF_GREATER12_DIA      NVARCHAR2(100) 
HEIGHT_OF_HEIGHEST_ANTENNA       NVARCHAR2(100) 
SITE_RRH_COUNT                   NVARCHAR2(100) 
WIND_SPEED                       NVARCHAR2(100) 
GREATER_9_RRH                    NVARCHAR2(100) 
FLOOR_SPACE_INDOOR               NVARCHAR2(100) 
FLOOR_SPACE_OUTDOOR              NVARCHAR2(100) 
EB_STATUS_VALUE                  NVARCHAR2(100) 
NO_OF_US                         NVARCHAR2(100) 
VOLUME_DISCOUNT                  NVARCHAR2(100)

The generated XML is below

<DocumentElement>
  <RecodSet>
    <ID>1</ID>
    <SAP_ID>I-HR-BWKR-ENB-G001</SAP_ID>
    <CITY>OTHERS</CITY>
    <CIRCLE>Haryana</CIRCLE>
    <FINAL_SO_DATE>05-08-2016</FINAL_SO_DATE>
    <INVOICE_DATE>05-07-2021</INVOICE_DATE>
    <IP_ID>HAR/GBT/A/05661</IP_ID>
    <APPLICABLE_MSA>MSA_1</APPLICABLE_MSA>
    <SITE_CATEGORY>GBT</SITE_CATEGORY>
    <ID_OD>OD</ID_OD>
    <RFI_DATE>27-10-2016</RFI_DATE>
    <BILL_START_DATE>01-07-2021</BILL_START_DATE>
    <BILL_END_DATE>31-07-2021</BILL_END_DATE>
    <NO_OF_OPCO>2</NO_OF_OPCO>
    <ACTUAL_RENT_AMT>0</ACTUAL_RENT_AMT>
    <TENURE>10</TENURE>
    <MW_ANTENNA_OF_UPTO06_DIA>0</MW_ANTENNA_OF_UPTO06_DIA>
    <MW_ANTENNA_OF_12DIA>0</MW_ANTENNA_OF_12DIA>
    <MW_ANTENNA_OF_GREATER12_DIA>0</MW_ANTENNA_OF_GREATER12_DIA>
    <HEIGHT_OF_HEIGHEST_ANTENNA>0.00</HEIGHT_OF_HEIGHEST_ANTENNA>
    <SITE_RRH_COUNT>9</SITE_RRH_COUNT>
    <WIND_SPEED>0</WIND_SPEED>
    <GREATER_9_RRH>0</GREATER_9_RRH>
    <FLOOR_SPACE_INDOOR>0</FLOOR_SPACE_INDOOR>
    <FLOOR_SPACE_OUTDOOR>0</FLOOR_SPACE_OUTDOOR>
    <EB_STATUS_VALUE>Y</EB_STATUS_VALUE>
    <NO_OF_US>0</NO_OF_US>
    <VOLUME_DISCOUNT>N</VOLUME_DISCOUNT>
  </RecodSet>
</DocumentElement>

Where am I going wrong?

You do not need any loop, because you may insert ... select columns from xmltable('/DocumentElement/RecodSet' ...) and debug the select statement before this. Also please post table definition as create table or at least minimize number of columns, because they all are processed in the same way. — astentx
– astentx, Commented Dec 3, 2021 at 16:23
@astentx: why don't i need loop ? maybe sometimes i need to insert multiple records then ? — HEEN
– HEEN, Commented Dec 3, 2021 at 18:35
u have just given the xml part, please help with the procedure as to how make the changes — HEEN
– HEEN, Commented Dec 3, 2021 at 18:43
Below is the answer how to do it with insert ... select ... without loop. If you need to insert records into multiple places, then you may use multitable insert: insert all into t1 values(id...) into t2 values(id, sap_id, ...) ... select ... from xmltable (.... see examples in the docs. — astentx
– astentx, Commented Dec 3, 2021 at 22:11

MT0 · Accepted Answer · 2021-12-03 19:29:25Z

1

Just use INSERT ... SELECT ... from an XMLTABLE:

CREATE PROCEDURE UploadGTLData(
  BulkXML IN CLOB
)
AS
BEGIN
  INSERT INTO IPCOLO_IPFEE_CALC_GTL(
    ID,
    SAP_ID,
    CITY,
    CIRCLE,
    FINAL_SO_DATE,
    INVOICE_DATE
  )
  SELECT ID,
         SAP_ID,
         CITY,
         CIRCLE,
         FINAL_SO_DATE,
         INVOICE_DATE
  FROM   XMLTABLE(
           '/DocumentElement/RecodSet'
           PASSING XMLTYPE( BulkXML )
           COLUMNS
             id            NVARCHAR2(100) PATH './ID',
             sap_id        NVARCHAR2(100) PATH './SAP_ID',
             city          NVARCHAR2(100) PATH './CITY',
             circle        NVARCHAR2(100) PATH './CIRCLE',
             final_so_date NVARCHAR2(100) PATH './FINAL_SO_DATE',
             invoice_date  NVARCHAR2(100) PATH './INVOICE_DATE'
         );
END UploadGTLData;
/

Then you can call it using:

BEGIN
  UploadGTLData(
    '<DocumentElement>
  <RecodSet>
    <ID>1</ID>
    <SAP_ID>I-HR-BWKR-ENB-G001</SAP_ID>
    <CITY>OTHERS</CITY>
    <CIRCLE>Haryana</CIRCLE>
    <FINAL_SO_DATE>05-08-2016</FINAL_SO_DATE>
    <INVOICE_DATE>05-07-2021</INVOICE_DATE>
  </RecodSet>
</DocumentElement>'
  );
END;
/

You can amend it to add the other columns.

db<>fiddle here

answered Dec 3, 2021 at 19:29

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

HEEN Over a year ago

Thanks for the answer MT0, let me try and check.

Collectives™ on Stack Overflow

Data not inserting into table via XML in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related