How to convert a given hierarchical XML file into a denormalized Relational Database Table using JAVA -

given following xml structure:

<clinical_study>   <primary_outcome>     <measure></measure>     <time_frame></time_frame>     <safety_issue></safety_issue>     <description></description>   </primary_outcome>   <secondary_outcome>     <measure></measure>     <time_frame></time_frame>     <safety_issue></safety_issue>     <description></description>   </secondary_outcome> </clinical_study> 

i want parse through values within these attribute tags , dump them in oracle table named "clinical_study" following column structure:

desc clinical_study name                         null     type               ---------------------------- -------- -----------------     primary_outcome_measure               varchar2(50)       primary_outcome_time_frame            varchar2(50)       primary_outcome_safety_issue          varchar2(50)       primary_outcome_description           varchar2(4000)     secondary_outcome_measure               varchar2(50)       secondary_outcome_time_frame            varchar2(50)       secondary_outcome_safety_issue          varchar2(50)       secondary_outcome_description           varchar2(4000)    

i realize there several ways implement this, not quite sure simplest. i'm leaning towards xslts "flatten" data , converted table structure, curious if there's easier way. in advance.

if programming java, there should no need run xslt transformation. should able read in data xml file , write out database.

there more 1 way it, 1 approach read in data using jaxb. (this work if have small enough data ok hold in memory while processing. if have large amounts of data, may want use streaming xml parser api stax instead.)

first, create pair of classes represent input data, annotated jaxb annotations define mapping xml.

@xmlrootelement(name="clinical_study") @xmlaccessortype(xmlaccesstype.field) public class clinicalstudy {      @xmlelement(name="primary_outcome")     private outcome primaryoutcome;      @xmlelement(name="secondary_outcome")     private outcome secondaryoutcome;      // getters , setters omitted brevity } 

and

@xmlaccessortype(xmlaccesstype.field) public class outcome {      @xmlelement(name="measure")     private string measure;      @xmlelement(name="time_frame")     private string timeframe;      @xmlelement(name="safety_issue")     private string safetyissue;      @xmlelement(name="description")     private string description;      // getters , setters omitted brevity } 

then can read, or "unmarshal", data xml (assuming inputstream stream of xml content).

    jaxbcontext context = jaxbcontext.newinstance(clinicalstudy.class);     unmarshaller unmarshaller = context.createunmarshaller();     clinicalstudy clinicalstudy = (clinicalstudy) unmarshaller.unmarshal(inputstream); 

and insert database (assuming conn jdbc database connection).

    preparedstatement pstmt = conn.preparestatement(             "insert clinical_study ("             + "primary_outcome_measure, "             + "primary_outcome_time_frame, "             + "primary_outcome_safety_issue, "             + "primary_outcome_description, "             + "secondary_outcome_measure, "             + "secondary_outcome_time_frame, "             + "secondary_outcome_safety_issue, "             + "secondary_outcome_description) "             + "values (?, ?, ?, ?, ?, ?, ?, ?)");     pstmt.setstring(1, clinicalstudy.getprimaryoutcome().getmeasure());     pstmt.setstring(2, clinicalstudy.getprimaryoutcome().gettimeframe());     pstmt.setstring(3, clinicalstudy.getprimaryoutcome().getsafetyissue());     pstmt.setstring(4, clinicalstudy.getprimaryoutcome().getdescription());     pstmt.setstring(5, clinicalstudy.getsecondaryoutcome().getmeasure());     pstmt.setstring(6, clinicalstudy.getsecondaryoutcome().gettimeframe());     pstmt.setstring(7, clinicalstudy.getsecondaryoutcome().getsafetyissue());     pstmt.setstring(8, clinicalstudy.getsecondaryoutcome().getdescription());     pstmt.execute();