XML stored in CLOB - repeating Nodes - how to read?

[dtoronto]

Hi folks:

I am very new to CLOB data - I have tried writing SQL that reads multiple occurrances of XML nodes stored within a clob. The problem is that some of the nodes repeat.

Below is my code, which works for XML structures with only one node (in the action tag), but I get the 19025 error when there are multiple nodes (ie: More than one action)

I am pasting the code below, followed by the XML structure that gives me the error. Note: I have pretty much tried using the existsnode, but I am not sure how to use it for reading multiple XML elements;

One last thing - I do not have priviledges to create functions, procs, or use the UTIL for file creation...

now the code:

select
i.customer_ow_id
, di_info
, extractvalue(di_info,'/DI/DIType') DI_Type
, extractvalue(di_info,'/DI/IsConditional') IsConditional
, extractvalue(di_info,'/DI/Created') Created
, extractvalue(di_info,'/DI/LastModifiedDate') LastModifiedDate
, extractvalue(di_info,'/DI/LastModifiedBy') LastModifiedBy
, extractvalue(di_info,'/DI/Instructions/Instruction/Tag') Tag
, extractvalue(di_info,'/DI/Instructions/Instruction/Conditions/SuitTypeCond/SuitTypes/SuitType') SuitType
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/Tag') Action_Tag
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/ActionType') ActionType
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/What') What
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/How') How
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/Who') Who
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/FedExService') FedExService
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/ParticipantID') ParticipantID
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/CustomerId') CustomerId
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/ParticipantName') ParticipantName
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/CustomerName') CustomerName
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/AddrLine1') AddrLine1
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/AddrLine2') AddrLine2
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/City') City
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/State') State
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/Zip') Zip
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/Email') Email
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/Phone') Phone
, extractvalue(di_info,'/DI/Instructions/Instruction/Actions/Action/RecipientInfo/Fax')
from arrow.tdi d
, arrow.tdi_participant p
, gds.tbv_individual i
where d.di_id = p.di_id
and p.participant_id = i.individual_id
and rownum < 58

Here is the problem element -I didnot beautify it ... sorry
~~~~~~~~~~~~~~~~~~~~~~~~~~



<DI xmlns:xsi='

http://www.w3.org/2001/XMLSchema-instance'

xsi:noNamespaceSchemaLocation='

http://www.cch-lis.com/di.xsd'><DII...merId>341384</CustomerId><ParticipantName>Jim

Lenhard</ParticipantName><CustomerName>Southeast Subway Development Company, LLC</CustomerName><AddrLine1>4043 Baymeadows Road</AddrLine1><AddrLine2>#C</AddrLine2><City>Jacksonville</City><State>FL</State><Zip>32217-4638</Zip><Country>US</Country><Email></Email><Phone></Phone><Fax></Fax></RecipientInfo></Action></Actions></Instruction></Instructions></DI>

 

[taupirho]

May I suggest that if you want help on this problem, you provide

a) Create table statements
b) insert data statements (minimum required to highlight the problem)
c) simplest possible SQL you are using to extract the data (i.e don't include extraneous columns, tables etc ...)
d) what output you're getting
e) what output you're expecting


In order to understand recursion, you must first understand recursion.