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)
    );

Your XML appears to be invalid as it is missing the </fd115Grp> and </fd115Grp1> closing tags. — MT0
– MT0, Commented Sep 12, 2019 at 9:44
Sorry but i didnt placed complete xml as its bit large ..do you want complete xml file ? — Marcus
– Marcus, Commented Sep 12, 2019 at 9:57
No, better would be a minimal reproducible example which had only 10-20 data points but was representative of your data (and was valid XML) so that we don't have to deal with a huge document (but its also relevant that your data is larger than 4000 bytes so would need to be passed as a CLOB) but where you could extrapolate the techniques used in the answers and apply it to your (larger) data-set. — MT0
– MT0, Commented Sep 12, 2019 at 10:02
Its also not clear where some columns in the table (such as the first fd115 column) relate to the XML document. — MT0
– MT0, Commented Sep 12, 2019 at 10:04
i have edited xml and fd115 column i have just added ..i think its of no use so i will delete this column and also the other header column which not needed...the xml is not that large its just 1.5MB in size so i think we dont need to use CLOB here — Marcus
– Marcus, Commented Sep 12, 2019 at 10:08

MT0 · Accepted Answer · 2019-09-12 10:58:55Z

2

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),
   ...

edited Sep 12, 2019 at 10:58

answered Sep 12, 2019 at 9:37

MT0

173k12 gold badges70 silver badges136 bronze badges

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

14 Comments

Marcus Over a year ago

how do i check check the XQuery paths ? and how will i use this query in Java ?

MT0 Over a year ago

The base path is //fd115 which should correspond to your root element; then each column has a path that should reflect the path in the XML structure to the data. I'm not sure I got them correct as I was typing it out by hand; you'll need to check that they are correct. As for using it in java ... its a SQL statement so you create a prepared statement and can use PASSING XMLType( ? ) in the middle and pass your XML file as a CLOB bind parameter to the prepared statement and then just execute it on the database as you would with any other prepared statement.

Marcus Over a year ago

just want to know can i run this sql query in oracle database directly and how to run it? so that i can confirm what data i am receiving and also filter out the column which not needed ...PASSING XMLType( your_xml_value_passed_as_a_clob_bind_variable ) what i need to pass here ?

MT0 Over a year ago

@Andrew The code in the db<>fiddle works in at least Oracle 11gR2 and later. If you are using an earlier version then check that is supports XMLTABLE and XMLTYPE and if it doesn't then you'll need to update your question and specify what version you are using and investigate a different method of extracting data from XML (try the EXTRACT or EXTRACTVALUE functions).

MT0 Over a year ago

Changed the version to 18c db<>fiddle and it works. Since its PL/SQL, are you running it in SQL Developer as a script (F5 keyboard shortcut) rather than as a statement (ctrl-Enter keyboard shortcut)? Beyond that suggestion, I'm not sure I can assist in getting otherwise working code to run on your local machine.

|

Collectives™ on Stack Overflow

Retrive data from XML in Java and insert data in Oracle

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related