oogooglies
asked on
Parsing XML held in a Clob field
Hi All
I have some CLOB Data which is in XML format that i want to parse and process into tables within my database on my Oracle 10g database
Does anyone have an example script of how this is done?
Example XML:
<EXAMPLE>
<CANDIDATES>
<CANDIDATE>
<NAME>JAMES</NAME>
<JOBHISTORY>
<COMPANYNAME>A</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>B</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>C</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<EDUCATION>
<SCHOOLNAME>D</SCHOOLNAME>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
<QUALIFICATIONS>
<SUBJECT>HISTORY<GRADE>A</ GRADE>
</SUBJECT>
<SUBJECT>MATHS<GRADE>A</GR ADE>
</SUBJECT>
<SUBJECT>ENGLISH<GRADE>A</ GRADE>
</SUBJECT>
<SUBJECT>SCIENCE<GRADE>A</ GRADE>
</SUBJECT>
</QUALIFICATIONS>
</EDUCATION>
</CANDIDATE>
<CANDIDATE>
<NAME>BOB</NAME>
<JOBHISTORY>
<COMPANYNAME>A</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>B</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>C</COMPANYNAM E>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
</JOBHISTORY>
<EDUCATION>
<SCHOOLNAME>D</SCHOOLNAME>
<STARTDATE>01/01/2008</STA RTDATE>
<ENDDATE>01/01/2008</ENDDA TE>
<QUALIFICATIONS>
<SUBJECT>HISTORY<GRADE>A</ GRADE>
</SUBJECT>
<SUBJECT>MATHS<GRADE>A</GR ADE>
</SUBJECT>
<SUBJECT>ENGLISH<GRADE>A</ GRADE>
</SUBJECT>
<SUBJECT>SCIENCE<GRADE>A</ GRADE>
</SUBJECT>
</QUALIFICATIONS>
</EDUCATION>
</CANDIDATE>
</CANDIDATES>
</EXAMPLE>
Many Thanks
Anthony
I have some CLOB Data which is in XML format that i want to parse and process into tables within my database on my Oracle 10g database
Does anyone have an example script of how this is done?
Example XML:
<EXAMPLE>
<CANDIDATES>
<CANDIDATE>
<NAME>JAMES</NAME>
<JOBHISTORY>
<COMPANYNAME>A</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>B</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>C</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<EDUCATION>
<SCHOOLNAME>D</SCHOOLNAME>
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
<QUALIFICATIONS>
<SUBJECT>HISTORY<GRADE>A</
</SUBJECT>
<SUBJECT>MATHS<GRADE>A</GR
</SUBJECT>
<SUBJECT>ENGLISH<GRADE>A</
</SUBJECT>
<SUBJECT>SCIENCE<GRADE>A</
</SUBJECT>
</QUALIFICATIONS>
</EDUCATION>
</CANDIDATE>
<CANDIDATE>
<NAME>BOB</NAME>
<JOBHISTORY>
<COMPANYNAME>A</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>B</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<JOBHISTORY>
<COMPANYNAME>C</COMPANYNAM
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
</JOBHISTORY>
<EDUCATION>
<SCHOOLNAME>D</SCHOOLNAME>
<STARTDATE>01/01/2008</STA
<ENDDATE>01/01/2008</ENDDA
<QUALIFICATIONS>
<SUBJECT>HISTORY<GRADE>A</
</SUBJECT>
<SUBJECT>MATHS<GRADE>A</GR
</SUBJECT>
<SUBJECT>ENGLISH<GRADE>A</
</SUBJECT>
<SUBJECT>SCIENCE<GRADE>A</
</SUBJECT>
</QUALIFICATIONS>
</EDUCATION>
</CANDIDATE>
</CANDIDATES>
</EXAMPLE>
Many Thanks
Anthony
ASKER
Thanks, is there any way to loop through the xml and store the data into a table.
don't loop, just insert directly from the select statement
insert into your_table(name,companynam e,startdat e,enddate)
SELECT EXTRACTVALUE(VALUE(c), '/CANDIDATE/NAME') name,
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/COMPANYNAME') companyname,
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/STARTDATE') startdate,
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/ENDDATE') enddate
FROM (SELECT xmltype(yourclob) xml FROM yourtable),
table(XMLSEQUENCE(EXTRACT( xml, '/EXAMPLE/CANDIDATES/CANDI DATE'))) c,
table(XMLSEQUENCE(EXTRACT( VALUE(c), '/CANDIDATE/JOBHISTORY'))) j
insert into your_table(name,companynam
SELECT EXTRACTVALUE(VALUE(c), '/CANDIDATE/NAME') name,
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/COMPANYNAME')
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/STARTDATE') startdate,
EXTRACTVALUE(VALUE(j), '/JOBHISTORY/ENDDATE') enddate
FROM (SELECT xmltype(yourclob) xml FROM yourtable),
table(XMLSEQUENCE(EXTRACT(
table(XMLSEQUENCE(EXTRACT(
oops, "yourtable" in the insert would be a different table than the "yourtable" in the select
ASKER
Thanks for your help so far, the XML i am trying to parse is alot more complex than the exmaple i gave. is there anyway of getting oracle to create and populate tables in the structure of the XML?
ASKER
Is there anyway oracle can dynamically go through the elements of XML data and depending on the element found process the data accordingly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window