1 Reply Latest reply on Nov 5, 2018 10:39 PM by Jason_(A_Non)

    how to store xml data into two different tables

    863516

      Hi All,

       

      I am giving the sample xml file as below . I want to store the data in two different tables .both are related to eachother with personID .

      <?xml version="1.0" encoding="UTF-8"?><HTrn:script xmlns:HTrn="urn:orgi:abcd:message:script:v2.3.0" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="urn:orgi:abcd:message:script:v1.5.0 script_v2.3.0.xsd">
       <TransData><DocID>TKSCD2016100100011</DocID>
       <CreatedDate>2016-10-01</CreatedDate>
       <RID>PLI1201200</RID></TransData>
       <Stnt>
       <Person><PersonID>551214578</PersonID><AIden>
       <AID>D12578</AID><ACode>ovi</ACode><SPCodeCode>CA</SPCode>
       </AIden>
       <RAID>E00123</RAID>
       <Birth><BirthDate>2000-10-12</BirthDate></Birth>
       <Name><FName>DyEan</FName><MName>J Millny</MName>
       <LName>Horkup</LName></Name>
       <ParentGuardianName/>
       <ParentGuardian>
       <Name/>
       </ParentGuardian><Gender><GenderCode>Male</GenderCode></Gender>
       </Person>
       <AcademicRecord>
       <Schl><OrgName>Harvest High School</OrgName>
        <Contacts><Address><Address>14 Yellow Ave</Address><City>Harvest</City><PCode>P9H 4TT</PCode>
       <StateP>CA</StateP><ItemsElementName>:PCode</ItemsElementName>
       <ItemsElementName>:Stateovi</ItemsElementName><EffectiveDate>0001-01-01</EffectiveDate>
       <ExpirationDate>1111-11-11</ExpirationDate></Address>
       <Phone><PhoneNumber>711-111-1234</PhoneNumber>
       <EffectiveDate>1111-11-11</EffectiveDate><ExpirationDate>1111-11-11</ExpirationDate></Phone>
       <FaxPhone><PhoneNumber>711-111-0987</PhoneNumber><EffectiveDate>1111-11-11</EffectiveDate>
       <ExpirationDate>1111-11-11</ExpirationDate></FaxPhone></Contacts></Schl>
       <StudentLevel/><AcademicAward>
       <AADate>1111-11-11</AADate><ComplIndi>false</ComplIndi>
       <ACDate>1111-11-11</ACDate></AcademicAward>
       <Academicsess><AcademicsessDetail>
       <SessDesi>2012-09</SessDesi>
       <sessSchlYear>2012-2013</sessSchlYear><sessBeginDate>1111-11-11</sessBeginDate>
       <sessEndDate>1111-11-11</sessEndDate></AcademicsessDetail><School><LocalorgID/></School>
       <StudentLevel/><Cse>
       <CseCreditBasis>Regular</CseCreditBasis><CseCreditUnits>Another</CseCreditUnits>
       <CseCreditLevel>10Grade</CseCreditLevel><CseCreditValue>1</CseCreditValue>
       <CseCreditEarned>2.00</CseCreditEarned><CseAcademicGrade>89</CseAcademicGrade><CseSupplementalAcademicGrade/>
       <CseAcademicGradeStatusCode>PFP</CseAcademicGradeStatusCode>
       <CseQualityPointsEarned>0</CseQualityPointsEarned>
       <CseNumber>ENU16TU</CseNumber><AgencyCseID>PNG2AS</AgencyCseID>
       <CseTitle>ENGLISH 10</CseTitle><CseAddDate>1111-11-11</CseAddDate>
       <CseDropDate>1111-11-11</CseDropDate><CseOverrideSchool><LocalorgID/></CseOverrideSchool>
       <CseBeginDate>1111-11-11</CseBeginDate><CseEndDate>1111-11-11</CseEndDate>
       <LanguageOfInstruction><LanguageCode>ENGLISH</LanguageCode></LanguageOfInstruction>
       <NoteMessage>ABCD;PAP</NoteMessage></Cse>
       <Cse>
       <CseCreditBasis>Regular</CseCreditBasis>
       <CseCreditUnits>Another</CseCreditUnits>
       <CseCreditLevel>10Grade</CseCreditLevel>
       <CseCreditValue>0</CseCreditValue>
       <CseCreditEarned>2.00</CseCreditEarned>
       <CseAcademicGrade>79</CseAcademicGrade>
       <CseSupplementalAcademicGrade/>
       <CseGradeStatusCode>PFP</CseGradeStatusCode>
       <CsePointsEarned>0</CsePointsEarned>
       <CseNumber>MT10</CseNumber>
       <CseID>MTH2AS</CseID>
       <CseTitle>MATH 10</CseTitle>
       <CseAddDate>1111-11-11</CseAddDate>
       <CseDropDate>1111-11-11</CseDropDate>
       <CseOverrideSchool><LocalorgID/></CseOverrideSchool>
       <CseBeginDate>1111-11-11</CseBeginDate><CseEndDate>1111-11-11</CseEndDate>
       <LanguageOfInstruction><LanguageCode>ENGLISH</LanguageCode></LanguageOfInstruction>
       <NoteMessage>EFGH;PDP</NoteMessage>
       </Cse>
       <AttendanceCount><DaysPresent>1</DaysPresent><DaysAbsent>1</DaysAbsent></AttendanceCount>
       </Academicsess>
       <NoteMessage>Center for Education</NoteMessage>
       </AcademicRecord><Health/>
       <NoteMessage>25.00</NoteMessage>
       </Stnt></HTrn:script>
      

      I have to store the data as below :

      Table1 :    persondetails

       

        PersonID    AID     RAID     BOD         FName   MName    LName    orgname                                Address             city             Phone             Faxphone            Notemessage(value from end of the file)

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      551214578   D12578  E00123   2000-10-12  DyEan   J Millny Horkup   Harvest High School         14 Yellow Ave   Harvest      711-111-1234   711-111-0987      25

       

       

        Table2 :   csedetails

       

      PersonID    sessSchlYear   csetitle                CseCreditLevel     csetype       csestatus    CseAcademicGrade   LanguageCode  

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      551214578   2012-2013      ENGLISH 10     10Grade                ABCD          PAP                  89                        ENGLISH

      551214578   2012-2013      MATH 10          10Grade                 EFGH         PDP                  79                        ENGLISH