Retrive data from XML in Java and insert data in Oracle

Question

I have used JAXB Unmarshaler in Java to retrive data from XML into Java objects which is working fine.

Below is my code:

import java.io.File;
import java.io.FileNotFoundException;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBElement;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import javax.xml.transform.stream.StreamSource;    

public class FRDDbImportWorkflow {


     public static void main(String...args) throws FileNotFoundException {

          File xmlFile = new File("//home/tr.xml");       

          try {             

              JAXBContext jaxbContext  = JAXBContext.newInstance(FRD115Type.class);

              Unmarshaller jaxbUnmarshaller = jaxbContext.createUnmarshaller();

              JAXBElement<FRD115Type> jaxbElement = (JAXBElement<Fd115Type>) jaxbUnmarshaller
                        .unmarshal(new StreamSource(xmlFile), Fd115Type.class);

              FRD115Type obj = jaxbElement.getValue();

              System.out.println(obj.getRptHdr().getEnvText()); 
           } catch (JAXBException e) {
               e.printStackTrace();
       }

    }

}

Now i want to create another java class which will insert this data into Oracle table. As i have many xml element in xml file then do i need to create get method for all this element to retrive the data from xml and then insert into Oracle ?

Is there any other parser technique used instead of creating get method for all this xml element ?

I am also not sure how to insert this xml data into Oracle. I have created below table in Oracle :

CREATE TABLE
    IMPORT_XML
    (
        fd115 VARCHAR2(180),
        RPTHDR VARCHAR2(180),
        EXCHNAM VARCHAR2(50),
        ENVTEXT VARCHAR2(20),
        RPTCOD VARCHAR2(20),
        RPTNAM VARCHAR2(180),
        RPTFLEXKEY VARCHAR2(180),
        MEMBID VARCHAR2(50),
        MEMBLGLNAM VARCHAR2(80),
        RPTPRNTEFFDAT DATE,
        RPTPRNTEFFTIM VARCHAR2(50),
        RPTPRNTRUNDAT DATE,
        fd115GRP VARCHAR2(180),
        fd115KEYGRP VARCHAR2(180),
        PARTICIPANTGRP VARCHAR2(180),
        PARTICIPANT VARCHAR2(50),
        PARTLNGNAME VARCHAR2(50),
        fd115GRP1 VARCHAR2(180),
        fd115KEYGRP1 VARCHAR2(180),
        BUSINESSUNITGRP VARCHAR2(180),
        BUSINESSUNIT VARCHAR2(180),
        BUSUNTLNGNAME VARCHAR2(50),
        BUSINESSUNITID NUMBER(30,9),
        fd115GRP2 VARCHAR2(180),
        fd115KEYGRP2 VARCHAR2(180),
        USER_NAME VARCHAR2(50),
        USERNUMERICID NUMBER(30,9),
        fd115REC1 VARCHAR2(180),
        USRGROUP VARCHAR2(50),
        USER_LEVEL VARCHAR2(10),
        LOGNAM VARCHAR2(50),
        ISUSFLG NUMBER(30,9),
        EFFSTATUS NUMBER(30,9),
        DELPROTECTED VARCHAR2(10),
        ENABLEPROPRIETARYACCT VARCHAR2(10),
        ENABLEAGENCYACCT VARCHAR2(10),
        ENABLEMARKETMAKINGACCT VARCHAR2(10),
        ENABLEBESTACCT VARCHAR2(10),
        ENABLERISKLESSPRINCIPALACCT VARCHAR2(10),
        MAXORDERVALUE VARCHAR2(100),
        MAXORDRQTY VARCHAR2(100),
        SETTLACCT NUMBER(30,9),
        SETTLLOCAT VARCHAR2(50),
        fd115GRP3 VARCHAR2(180),
        fd115KEYGRP3 VARCHAR2(180),
        MKTGRPNAM VARCHAR2(50),
        fd115REC2 VARCHAR2(180),
        ENTROLE VARCHAR2(50),
        fd115GRP4 VARCHAR2(180),
        TESTYPE NUMBER(30,9),
        TESELIGIBILITY NUMBER(30,9)
    );

Answer 1

Use XMLTABLE. Something like:

INSERT INTO import_xml (
  EXCHNAM,
  ENVTEXT,
  RPTCOD,
  RPTNAM,
  RPTFLEXKEY,
  MEMBID,
  MEMBLGLNAM,
  RPTPRNTEFFDAT,
  RPTPRNTRUNDAT,
  PARTICIPANT
)
SELECT EXCHNAM,
       ENVTEXT,
       RPTCOD,
       RPTNAM,
       RPTFLEXKEY,
       MEMBID,
       MEMBLGLNAM,
       RPTPRNTEFFDAT + ( TO_TIMESTAMP( RPTPRNTEFFTIM, 'HH24:MI:SS.FF2' )
                       - TO_TIMESTAMP( '00:00:00.00', 'HH24:MI:SS.FF2' ) ),
       RPTPRNTRUNDAT,
       PARTICIPANT
FROM   XMLTABLE(
         XMLNAMESPACES( DEFAULT 'http://www.eu.com/technology' ),
         '//fd115'
         PASSING XMLType( your_xml )
         COLUMNS
           EXCHNAM        VARCHAR2(50)  PATH './rptHdr/exchNam',
           ENVTEXT        VARCHAR2(20)  PATH './rptHdr/envText',
           RPTCOD         VARCHAR2(20)  PATH './rptHdr/rptCod',
           RPTNAM         VARCHAR2(180) PATH './rptHdr/rptNam',
           RPTFLEXKEY     VARCHAR2(180) PATH './rptHdr/rptFlexKey',
           MEMBID         VARCHAR2(50)  PATH './rptHdr/membId',
           MEMBLGLNAM     VARCHAR2(80)  PATH './rptHdr/membLglNam',
           RPTPRNTEFFDAT  TIMESTAMP     PATH './rptHdr/rptPrntEffDat',
           RPTPRNTEFFTIM  VARCHAR2(50)  PATH './rptHdr/rptPrntEffTim',
           RPTPRNTRUNDAT  DATE          PATH './rptHdr/rptPrntRunDat',
           PARTICIPANT    VARCHAR2(50)  PATH './fd115Grp/fd115KeyGrp/participantGrp/participant'
       );

db<>fiddle

(You'll need to check the XQuery paths and add in all the extra columns but this should give you an example of the syntax you can use.)

Also, RPTPRNTEFFDAT can have the TIMESTAMP data type and store both the RPTPRNTEFFDAT and RPTPRNTEFFTIM xml data. (You could use a DATE column but the RPTPRNTEFFTIM data appears to have fractional seconds and you would lose that precision.)

CREATE TABLE
    IMPORT_XML
    (
        fd115 VARCHAR2(180),
        RPTHDR VARCHAR2(180),
        EXCHNAM VARCHAR2(50),
        ENVTEXT VARCHAR2(20),
        RPTCOD VARCHAR2(20),
        RPTNAM VARCHAR2(180),
        RPTFLEXKEY VARCHAR2(180),
        MEMBID VARCHAR2(50),
        MEMBLGLNAM VARCHAR2(80),
        RPTPRNTEFFDAT TIMESTAMP, -- TIMESTAMP and remove the RPTPRNTEFFTIM column
        RPTPRNTRUNDAT DATE,
        fd115GRP VARCHAR2(180),
        fd115KEYGRP VARCHAR2(180),
        PARTICIPANTGRP VARCHAR2(180),
        PARTICIPANT VARCHAR2(50),
   ...