Wednesday, 18 February 2015

Loading parent XML tag values into Oracle table along with child values




<project>
<id>001</id>
<name>Math Project</name>
.
.
<team>
<id>1</id>
<name> Team Roger </name>
.
.
</team>

</project>
<project>
<id>002</id>
<name>Science Project</name>
.
.
<team>
<id>2</id>
<name> Team Alpha </name> .
</team>
<team>
<id>3</id>
<name>Team Romeo </id>

</project>


This is the xml I have. I am trying to load a table (teams) with team details and its corresponding parent project_id, like this:



proj_id team_id team_name
001 1 Team Roger
002 2 Team Alpha
002 3 Team Romeo
Below is the pl/sql block I wrote:

declare
cursor proj_cur is select project_id from projects;
proj_rec proj_cur%rowtype;
begin
for proj_rec in proj_cur
loop
INSERT INTO teams(team_id,team_name,project_id)
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','SCRUM.xml'), nls_charset_id('UTF8')) xmlcol FROM dual)
SELECT
extractValue(value(x),'*/id')team_id
,extractValue(value(x),'*/name') team_name
,extractValue(value(y),'/project/id') project_id
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/scrumwise-export/data/account/projects/project'))) y
,TABLE(XMLSequence(extract(t.xmlcol,'/scrumwise-export/data/account/projects/project/people/teams/team'))) x
where (extractValue(value(y),'/project/id'))='27628-0-5';
end loop;
end;


This returns a cartesian product! Any help with this is much appreciated.


No comments:

Post a Comment