Hope this is very simple, but I am missing something.
This is my XML :
<?xml version = '1.0' encoding = 'UTF-8'?><PFA>
<Person id="11" action="add" date="20-Nov-2012">
<Gender>Male</Gender>
<ActiveStatus>Active</ActiveStatus>
<Deceased>Yes</Deceased>
<RoleDetail>
<Roles RoleType="Primary Occupation">
<OccTitle OccCat="16">Deceased</OccTitle>
</Roles>
<Roles RoleType="Previous Roles">
<OccTitle SinceMonth="Nov" SinceYear="2010" ToMonth="Jan" ToYear="2011" OccCat="16">Candidate</OccTitle>
<OccTitle SinceMonth="Mar" SinceYear="2005" ToYear="2005" OccCat="16">Candidate,23</OccTitle>
<OccTitle OccCat="16">President</OccTitle>
<OccTitle SinceDay="22" SinceMonth="Oct" SinceYear="1993" ToDay="15" ToMonth="Mar" ToYear="2003" OccCat="1">President </OccTitle>
<OccTitle OccCat="7">Supreme Commander</OccTitle>
<OccTitle SinceDay="08" SinceMonth="Dec" SinceYear="1976" ToDay="14" ToMonth="Jul" ToYear="1978" OccCat="1">Prime Minister </OccTitle>
</Roles>
</RoleDetail>
</Person>
</PFA>
I am querying this xml to store the values in my table.
My Query -
SELECT t.personid, t.occtitle,r.roleid,t.sinceday,t.sincemonth,t.sinceyear,t.today,t.tomonth,t.toyear,t.occcat
FROM xml_files p,master_roletypelists r,
XMLTable(
'for $i in PFA/Person/RoleDetail/Roles/OccTitle
return <row>
{
$i/../../../@id,
$i/../@RoleType,
$i/@SinceDay,
$i/@SinceMonth,
$i/@SinceYear,
$i/@ToDay,
$i/@ToMonth,
$i/@ToYear,
$i/@OccCat
}
</row>'
PASSING p.filecontent
COLUMNS
personid number PATH '@id',
occtitle VARCHAR2(4000) PATH '.',
RoleType VARCHAR2(2000) PATH '@RoleType',
sinceday VARCHAR2(2000) PATH '@SinceDay',
sincemonth VARCHAR2(2000) PATH '@SinceMonth',
sinceyear VARCHAR2(2000) PATH '@SinceYear',
today VARCHAR2(2000) PATH '@ToDay',
tomonth VARCHAR2(2000) PATH '@ToMonth',
toyear VARCHAR2(2000) PATH '@ToYear',
occcat VARCHAR2(2000) PATH '@OccCat'
) t where t.roletype = r.rolename
;
But for this I am getting the column 'OCCTITLE' as null.
Please help me to modify my query to get the OccTitle node value as well. For example in OccTitle column in first row I am supposed to get the value "Deceased" instead of null.
If this is not the way to do this please help me to correct it. Any help would be appreciated.
You never included the <OccTitle/>
element. For example, use
for $i in PFA/Person/RoleDetail/Roles/OccTitle
return <row>
{
$i/../../../@id,
$i/../@RoleType,
$i/@SinceDay,
$i/@SinceMonth,
$i/@SinceYear,
$i/@ToDay,
$i/@ToMonth,
$i/@ToYear,
$i/@OccCat,
$i (: was missing :)
}
</row>
This will include the whole element. If you're only interested in the data, replace that line with data($i)
.