Hi, I'm trying to parse an xml feed but the way it's structured makes it difficult to pull out the values. (see example) Would anyone be able to recommend a solution or some ideas as to how to get around this?
SAMPLE XML:
<env:Envelope xmlns:env='http://schemas.xmlsoap.org/soap/envelope/'>
<env:Header>
</env:Header>
<env:Body>
<ns3:commandResponse xmlns:ns2="http://test.com/2011/Generic/schema" xmlns:ns3="http://test.com/2011/Generic">
<return>
<ns2:return>success</ns2:return>
<ns2:Command>issues</ns2:Command>
<ns2:WorkItems>
<ns2:Id>216141</ns2:Id>
<ns2:ModelType>Issue</ns2:ModelType>
<ns2:DisplayId>216141</ns2:DisplayId>
<ns2:Field>
<ns2:Name>Type</ns2:Name>
<ns2:Value>
<ns2:Item>
<ns2:Id>Dev Task</ns2:Id>
<ns2:ModelType>Type</ns2:ModelType>
<ns2:DisplayId>Dev Task</ns2:DisplayId>
</ns2:Item>
</ns2:Value>
</ns2:Field>
<ns2:Field>
<ns2:Name>ID</ns2:Name>
<ns2:Value>
<ns2:int>216141</ns2:int>
</ns2:Value>
</ns2:Field>
<ns2:Field>
<ns2:Name>Reason</ns2:Name>
<ns2:Value>
<ns2:string>Integrating</ns2:string>
</ns2:Value>
</ns2:Field>
<ns2:Field>
<ns2:Name>Dev Task Component</ns2:Name>
<ns2:Value>
<ns2:string>Java Tools</ns2:string>
</ns2:Value>
</ns2:Field>
<ns2:Field>
<ns2:Name>Created Date</ns2:Name>
<ns2:Value>
<ns2:datetime>2009-08-10T15:52:39.000-04:00</ns2:datetime>
</ns2:Value>
</ns2:Field>
<ns2:Field>
<ns2:Name>Date Closed</ns2:Name>
<ns2:Value/>
</ns2:Field>
<ns2:Field>
<ns2:Name>Modified Date</ns2:Name>
<ns2:Value>
<ns2:datetime>2011-03-04T12:57:05.000-05:00</ns2:datetime>
</ns2:Value>
</ns2:Field>
</ns2:WorkItems>
</return>
</ns3:commandResponse>
</env:Body>
</env:Envelope>
This is just a sample with just one WorkItem, but there would be much more, N number of items with 9 fields per item. (Not all of the fields were put in the sample, and some can have null values)
I only need to pull the content from /ns2:WorkItems/ns2:Field/ns2:Value/ns2:Item/ns2:Id for the first field and the /ns2:value/* tag of all the other fields. Then put this in a table where each row is a workitem and the fields are the columns (create table workitems (Type,ID,Reason,Dev Task Component,Created Date, Date Closed, Modified Date) --all the fields should be varchar2 except the dates)
What I've been trying so far seems rather brute force by running a nested loop to go through every item and field and then an IF case for each field 1,2,...9 which would insert the value into a table.
At the moment I'm using something like below to pull a single value
path1 = '//ns2:WorkItems[1]/ns2:Field[1]/ns2:Value[1]/ns2:Item[1]/ns2:Id[1]';
nameserve = 'xmlns:ns2="http://test.com/2011/Generic/schema"';
extractvalue(xmltype(src_clob),path1,nameserve);
I'm not entirely sure if I would be able to substitute the [1]'s with [' || nitem || '] where nitem is loop number to do something like:
for nitem in 1..itemcount
loop
FOR nfield in 1..9
loop
if nfield=1 then
path1 := '//ns2:WorkItems[' || nitem || ']/ns2:Field[' || nfield || ']/ns2:Value[1]/ns2:Item[1]/ns2:Id';
fieldvalue := extractvalue(xmltype(src_clob),path1,nameserve);';
else
path2 := '//ns2:WorkItems[' || nitem || ']/ns2:Field[' || nfield || ']/ns2:Value[1]/*[1]';
fieldvalue := extractvalue(xmltype(src_clob),path2,nameserve);';
end if;
end loop;
end loop;
The problem with the above script is how do I insert this fieldvalue into different columns on a table without using an IF case for each field.
I was wondering if there is simpler way to put each field into a different column and loop through every workitem. I looked into dynamically naming variables but I don't think plsql supports that.
Any help/advice is appreciated,
Thanks!
Edited by: 843508 on Mar 10, 2011 1:56 PM
Edited by: 843508 on Mar 10, 2011 1:57 PM
Edited by: 843508 on Mar 10, 2011 2:01 PM