Hi gurus,
This is my XML,
<CaseNotification messageProducedDateTime="2005-07-21T14:49:18-05:00">
<NotificationAdminHeader caseNumberKey="87CR0569">
<CaseCountyIdentifier>87</CaseCountyIdentifier>
<CaseLocationText>Yellow Medicine County</CaseLocationText>
<BaseCaseTypeText>Adult</BaseCaseTypeText>
<CaseCategoryText>Criminal</CaseCategoryText>
<PartyIdentifier partyKey="854">
<PartyCaseAssociationText>Jurisdiction</PartyCaseAssociationText>
</PartyIdentifier>
<PartyIdentifier partyKey="123637">
<PartyCaseAssociationText>Defendant</PartyCaseAssociationText>
</PartyIdentifier>
</NotificationAdminHeader>
<InterimConditionsNotification>
<TriggeredDateTime>2005-07-21T14:48:57-05:00</TriggeredDateTime>
<NotificationEvent>InterimConditionsExpired</NotificationEvent>
<InterimConditionsOrder orderEventKey="1760110">
<OrderDate>2005-07-20</OrderDate>
<OrderExpirationDate>2005-07-21</OrderExpirationDate>
<OrderedBy judgeKey="00001J">
<JudgeName>
<PersonGivenName>John</PersonGivenName>
<PersonMiddleName>P.</PersonMiddleName>
<PersonSurName>Smith</PersonSurName>
<PersonFullName>Smith, John P.</PersonFullName>
</JudgeName>
<JudicialAgencyIdentifier>MN011015J</JudicialAgencyIdentifier>
</OrderedBy>
<CourtCaseNumber documentVersion="1" effectiveDateTime="2005-06-06T00:00:00-06:00">
<MNCISCaseNumber>
<CountyNumber>87</CountyNumber>
<CaseType>CR</CaseType>
<YearFiled>05</YearFiled>
<SequenceNumber>69</SequenceNumber>
</MNCISCaseNumber>
<CaseNumberIdentifier caseNumberKey="87CR0569">87-CR-05-69</CaseNumberIdentifier>
</CourtCaseNumber>
<ConditionSubject>
<Party partyKey="123637" currentNameIndicator="true">
<StandardName>
<PersonGivenName>Clanry</PersonGivenName>
<PersonSurName>Ogavitz</PersonSurName>
<PersonFullName>Ogavitz, Clanry</PersonFullName>
</StandardName>
<PartyCaseAssociationText>Defendant</PartyCaseAssociationText>
</Party>
</ConditionSubject>
<InterimConditions>
<InterimConditionText>Pay bail</InterimConditionText>
<InterimConditionAmount>950</InterimConditionAmount>
</InterimConditions>
<InterimConditions>
<InterimConditionText>Anger management</InterimConditionText>
</InterimConditions>
<InterimConditions>
<InterimConditionText>Attend AA (Alcoholics Anonymous)</InterimConditionText>
</InterimConditions>
</InterimConditionsOrder>
</InterimConditionsNotification>
<InterimConditionsNotification>
<TriggeredDateTime>2005-07-21T14:48:57-05:00</TriggeredDateTime>
<NotificationEvent>InterimConditionsAdded</NotificationEvent>
<InterimConditionsOrder orderEventKey="1760247">
<OrderDate>2005-07-21</OrderDate>
<OrderedBy judgeKey="00001J">
<JudgeName>
<PersonGivenName>John</PersonGivenName>
<PersonMiddleName>P.</PersonMiddleName>
<PersonSurName>Smith</PersonSurName>
<PersonFullName>Smith, John P.</PersonFullName>
</JudgeName>
<JudicialAgencyIdentifier>MN011015J</JudicialAgencyIdentifier>
</OrderedBy>
<CourtCaseNumber documentVersion="1" effectiveDateTime="2005-06-06T00:00:00-06:00">
<MNCISCaseNumber>
<CountyNumber>87</CountyNumber>
<CaseType>CR</CaseType>
<YearFiled>05</YearFiled>
<SequenceNumber>69</SequenceNumber>
</MNCISCaseNumber>
<CaseNumberIdentifier caseNumberKey="87CR0569">87-CR-05-69</CaseNumberIdentifier>
</CourtCaseNumber>
<ConditionSubject>
<Party partyKey="123637" currentNameIndicator="true">
<StandardName>
<PersonGivenName>Clanry</PersonGivenName>
<PersonSurName>Ogavitz</PersonSurName>
<PersonFullName>Ogavitz, Clanry</PersonFullName>
</StandardName>
<PartyCaseAssociationText>Defendant</PartyCaseAssociationText>
</Party>
</ConditionSubject>
<InterimConditions>
<InterimConditionText>Pay bail</InterimConditionText>
<InterimConditionAmount>2300</InterimConditionAmount>
</InterimConditions>
<InterimConditions>
<InterimConditionText>Anger management</InterimConditionText>
</InterimConditions>
<InterimConditions>
<InterimConditionText>Attend AA (Alcoholics Anonymous)</InterimConditionText>
</InterimConditions>
</InterimConditionsOrder>
</InterimConditionsNotification>
</CaseNotification>
Now this part works,
insert into interimconditionstype
select ict.NEvent, ict.OrderEventKey, to_date(ict.OrderDate, 'yyyy/mm/dd'),
ict.OrderByJudgeKey, ict.CourtCaseNumber, ict.CaseNumberKey
from
xmltable
(
--xmlnamespaces
--(
-- default 'http://www.courts.state.mn.us/CourtXML/1.0.0'
--),
'/CaseNotification/InterimConditionsNotification'
passing v_xml
columns
NEvent varchar2(50) path '/InterimConditionsNotification/NotificationEvent',
OrderEventKey number path '/InterimConditionsNotification/InterimConditionsOrder/@orderEventKey',
OrderDate varchar2(15) path '/InterimConditionsNotification/InterimConditionsOrder/OrderDate',
OrderByJudgeKey varchar2(10) path '/InterimConditionsNotification/InterimConditionsOrder/OrderedBy/@judgeKey',
CourtCaseNumber varchar2(15) path '/InterimConditionsNotification/InterimConditionsOrder/CourtCaseNumber/CaseNumberIdentifier',
CaseNumberKey varchar2(15) path '/InterimConditionsNotification/InterimConditionsOrder/CourtCaseNumber/CaseNumberIdentifier/@caseNumberKey'
) ict;
But this part returns the Data not found error.
declare
cursor c_InterimEvenKeys is
select * from interimconditionstype;
begin
for v_IEK in c_InterimEvenKeys loop
v_OEKey := v_IEK.ORDEREVENTKEY;
dbms_output.put_line(v_OEKey);
--insert into interimconditions
--select v_IEK.casenumberkey, v_IEK.ordereventkey, ics.ICSText, ics.ICSAmount from
select ics.ICSText, ics.ICSAmount into v_ICSText, v_ICSAmount from
xmltable
(
--xmlnamespaces
--(
-- default 'http://www.courts.state.mn.us/CourtXML/1.0.0'
--),
'for $ics in /CaseNotification/InterimConditionsNotification/InterimConditionsOrder[orderEventKey = $val/oeKey]/InterimConditions
return
<ICS>
<ICSText>{$ics/InterimConditionText}</ICSText>
<ICSAmount>{$ics/InterimConditionAmount}</ICSAmount>
</ICS>'
passing v_xml, xmlelement("oeKey", v_OEKey) as "val"
columns
ICSText varchar2(100) path '/ICS/ICSText',
ICSAmount number path '/ICS/ICSAmount'
) ics;
dbms_output.put_line('ICSText: ' || v_ICSText);
dbms_output.put_line('ICSAmount: ' || v_ICSAmount);
end loop;
exception
when others then
dbms_output.put_line(SQLErrM);
end;
Could you please help me figure out what I did wrong?
Thanks a lot.
Ben