Home » Developer & Programmer » JDeveloper, Java & XML » XML parsing help (Oracle 10.2.0.4, AIX 5.3)
| XML parsing help [message #575245] |
Mon, 21 January 2013 11:53  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
I'm new to XML and got a requirement to parse xml and load it into different tables.
Here is the XML file which i need to parse
<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>
XML has a parent element Interaction and each Interaction has different elements like Consumer, Campaign, Response, Survey, MultiSuppressions. Now i need to insert Consumer element data into table1, Response and Campaign elements data into table2, Survey data into Table3 and Multisuppression data into table4 with interaction number (this can be rownumber) so that i can link all the tables based on interaction number.
I googled on parsing xml and found xmltable can be used to parse xml. I wrote below procedure, but it will not work if i include MultSuppressions (will get cartesians). Can someone please help me on parsing the above xml.
create table table1 (interaction_id number,
sourcecode varchar2(20),
externalid varchar2(20),
productcode varchar2(20),
Addressline1 varchar2(40),
Addressline2 varchar2(40),
City varchar2(30),
State varchar2(5),
Zipcode varchar2(7),
birth_dt varchar2(30),
emailaddress varchar2(100),
phonenum varchar2(14)
);
create table table2 (interaction_id number,
campaigncode varchar2(30),
promocode varchar2(30),
kitcode varchar2(30),
offercode varchar2(30),
mediaorigcode varchar2(30),
ResponseDate varchar2(30)
);
create table table3 (interaction_id number,
QuestionID varchar2(14),
Answerid varchar2(14),
OpenEndedQuestionInd varchar2(1),
OpenEndedAnswerText varchar2(100)
);
create table table4 (interaction_id number,
Multisuppressiondate varchar2(30),
Multisuppressioncode varchar2(2)
);
create table XML_TEST
(
xml_data XMLTYPE
);
Insert into xml_test
values ('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>
');
INSERT ALL
WHEN ( rnk = 1 )
THEN INTO table1 (interaction_id,
externalid,
sourcecode,
firstname,
lastname,
emailaddress,
phonenum,
Addressline1,
Addressline2,
City,
State,
Zipcode,
Birth_Dt,
ProductCode
)
VALUES (interact_id,
ExternalID, SourceCode,
FirstName, LastName,
EmailAddress,
PrimaryPhone,
AddressLine1,
AddressLine2,
City,
State,
ZipCodeBase,
BirthDate,
ProductCode
)
WHEN ( rnk = 1 )
THEN INTO table2 (interaction_id,
campaigncode,
promocode,
kitcode,
offercode,
responsedate,
Mediaorigcode)
VALUES (interact_id,
CampaignCode, PromoCode, KitCode, OfferCode,
ResponseDate,
MediaOrigcode)
WHEN (1=1)
THEN INTO table3 (interaction_id,
questionid,
answerid,
OpenEndedQuestionInd,
OpenEndedAnswerText)
VALUES (interact_id,
QuestionID, AnswerID,
QuestInd,
AnsTxt)
SELECT rank() over(partition by interaction_ord order by rownum) rnk,
rownum rec_num,
interaction_ord as interact_id,
i.*, s.*
FROM (SELECT XMLTYPE(bfilename('${DIR_ALIAS}', '${FILE_NAME}'),
nls_charset_id('${CHAR_SET}')
) xml_data
FROM dual
) x
, XMLTable('/Interactions/Interaction'
passing x.xml_data
columns
interaction_ord for ordinality
, SourceCode varchar2(4000) path '@SourceCode'
, ExternalID varchar2(4000) path '@ExternalID'
, VendorCode varchar2(4000) path '@VendorCode'
, ChannelCode varchar2(4000) path '@ChannelCode'
, ProductCode varchar2(4000) path '@ProductCode'
, FirstName varchar2(4000) path 'Consumer/@FirstName'
, LastName varchar2(4000) path 'Consumer/@LastName'
, EmailAddress varchar2(4000) path 'Consumer/@EmailAddress'
, PrimaryPhone varchar2(4000) path 'Consumer/@PrimaryPhone'
, AddressLine1 varchar2(4000) path 'Consumer/@AddressLine1'
, AddressLine2 varchar2(4000) path 'Consumer/@AddressLine2'
, City varchar2(4000) path 'Consumer/@City'
, State varchar2(4000) path 'Consumer/@State'
, ZipCodeBase varchar2(4000) path 'Consumer/@ZipCodeBase'
, CampaignCode varchar2(4000) path 'Campaign/@CampaignCode'
, PromoCode varchar2(4000) path 'Campaign/@PromoCode'
, KitCode varchar2(4000) path 'Campaign/@KitCode'
, OfferCode varchar2(4000) path 'Campaign/@OfferCode'
, ResponseDate varchar2(4000) path 'Response/@ResponseDate'
, MediaOrigCode varchar2(4000) path 'Response/@MediaOriginCode'
, survey_xml xmltype path 'Survey'
) i
, XMLTable('/Survey/Answers/Answer'
passing i.survey_xml
columns
answer_ord for ordinality
, QuestionID varchar2(4000) path '@QuestionID'
, AnswerID varchar2(4000) path '@AnswerID'
, QuestInd varchar2(4000) path '@OpenEndedQuestionInd'
, AnsTxt varchar2(4000) path '@OpenEndedAnswerText'
) (+) s
Thanks
Sri
|
|
|
|
| Re: XML parsing help [message #575246 is a reply to message #575245] |
Mon, 21 January 2013 12:58  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just to give you the idea:
with t as (select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>') xml_doc from dual)
select rownum interaction_id,
sourcecode,
externalid,
productcode,
Addressline1,
Addressline2,
City,
State,
Zipcode,
birth_dt,
emailaddress,
phonenum
from t,
xmltable(
'/Interactions/Interaction'
passing xml_doc
columns
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer'
),
xmltable(
'Consumer'
passing consumer
columns
Addressline1 varchar2(40) path '@Addressline1',
Addressline2 varchar2(40) path '@Addressline2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone'
)
/
INTERACTION_ID SOURCECODE EXTERNALID PRODUCTCODE ADDRESSLINE1 ADDRESSLINE2 CITY STATE ZIPCODE BIRTH_DT EMAILADDRESS PHONENUM
-------------- ---------- ---------- ----------- ------------ ------------ ---------- ----- ------- ------------------------------ -------------- --------------
1 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999
2 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000
SQL>
SY.
|
|
|
|
| Re: XML parsing help [message #575249 is a reply to message #575246] |
Mon, 21 January 2013 13:32 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Solomon. Do i need to write individual query to populate each table. But how can i link the interacttion_id from all the tables?
Thanks
Sri
|
|
|
|
|
|
| Re: XML parsing help [message #575256 is a reply to message #575250] |
Mon, 21 January 2013 14:10 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Problem is with the "rownum interaction_id". One interaction might have more than one QuestionID/AnswerID. For all these Interaction_ID has to be same.
But with above query it's not same.
with t as (select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>') xml_doc from dual)
select rownum interaction_id,
sourcecode,
externalid,
productcode,
Addressline1,
Addressline2,
City,
State,
Zipcode,
birth_dt,
emailaddress,
phonenum,
QuestionID,
AnswerID,
QuestInd,
AnsTxt
from t,
xmltable(
'/Interactions/Interaction'
passing xml_doc
columns
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
survey_xml xmltype path 'Survey'
),
xmltable(
'Consumer'
passing consumer
columns
Addressline1 varchar2(40) path '@Addressline1',
Addressline2 varchar2(40) path '@Addressline2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone'
),
xmltable(
'/Survey/Answers/Answer'
passing survey_xml
columns
QuestionID varchar2(4000) path '@QuestionID',
AnswerID varchar2(4000) path '@AnswerID',
QuestInd varchar2(4000) path '@OpenEndedQuestionInd',
AnsTxt varchar2(4000) path '@OpenEndedAnswerText'
)
INTERACTION_ID SOURCECODE EXTERNALID PRODUCTCODE ADDRESSLINE1 ADDRESSLINE2 CITY STATE ZIPCODE BIRTH_DT EMAILADDRESS PHONENUM QUESTIONID ANSWERID QUESTIND ANSTXT
1 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0112 ADAKGM0002
2 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0102 ADAKGM0005
3 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0565 ADAKGM0006
4 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0118 ADAKGM0001
5 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0567 ADAKGM0001
6 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0103 ADAKGM0001
7 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0103 ADAKGM0004
8 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0111 ADAKGM0001 O 3/1/1987
9 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0273 ADAKGM0001 O 59620414657
10 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0562 ADAKGM0001 O 16
11 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0563 ADAKGM0001 O 2cv0711961
12 TEST_SRC abcd1234 ABCD Charleston SC 29403 1939-03-19T00:00:00.000-05:00 test@yahoo.com (999)123-9999 QDAKGM0564 ADAKGM0001 O 09400006
13 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0112 ADAKGM0040
14 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0102 ADAKGM0007
15 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0565 ADAKGM0006
16 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0118 ADAKGM0002
17 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0103 ADAKGM0001
18 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0103 ADAKGM0002
19 TEST_SRC abcd4567 ABCD Dallas SC 75698 (888)123-1000 QDAKGM0111 ADAKGM0001 O 12/1/2012
|
|
|
|
| Re: XML parsing help [message #575258 is a reply to message #575256] |
Mon, 21 January 2013 14:52 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
sminnakanti wrote on Mon, 21 January 2013 15:10Problem is with the "rownum interaction_id".
Not really. I assume ExternalID is unique per XML ducument, right? Then use:
dense_rank() over(order by ExternalID)
instead of rownum.
SY.
|
|
|
|
| Re: XML parsing help [message #575260 is a reply to message #575258] |
Mon, 21 January 2013 15:03 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
ExternalID is not unique in XML. We can receive 2 interactions with same externalID but with different survey Question/Answers. In this case i need to count as 2 different records.
Thanks
Sri
|
|
|
|
|
|
| Re: XML parsing help [message #575366 is a reply to message #575273] |
Tue, 22 January 2013 09:48 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Solomon,
I started of with writing pl/sql block to parse the xml. If i process row by row then the code is working. If i use bulk collect it's not working (i mean working but no output). Am i doing something wrong while using bulk collect.
-- Created on 1/21/2013 by SMINNAKANTI
declare
v_xml xmltype;
cursor c1 is
with t as
(select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd9999">
<Consumer AddressLine1="1234 xyz Ave" CaptureDate="2013-01-20T10:35:25.000-05:00" City="Milford" FirstName="firstname3" LastName="LastName3" PrimaryPhone="(999)123-1000" State="CT" ZipCodeBase="06824"/>
<Campaign CampaignCode="CPGN3" KitCode="KIT3" OfferCode="Offer3" PromoCode="Promo3"/>
<Response MediaOriginCode="MOC740" ResponseDate="2013-01-20T10:35:25.000-05:00"/>
<MultiSuppressions SuppressDate= "2013-01-01T23:59:59" >
<MultiSuppression MultiSuppressionCode= "1" />
<MultiSuppression MultiSuppressionCode= "99" />
</MultiSuppressions>
</Interaction>
</Interactions>') xml_doc
from dual)
select p.*
from t,
xmltable('/Interactions/Interaction' passing xml_doc columns
interaction_id for ordinality,
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
campaign xmltype path 'Campaign',
response xmltype path 'Response',
survey_xml xmltype path 'Survey',
supp_xml xmltype path 'MultiSuppressions') p;
type rec_type is record(
id number,
src_cd varchar2(30),
extrn_id varchar2(30),
prod_cd varchar2(20),
consumer_xml xmltype,
campaign_xml xmltype,
resp_xml xmltype,
survey_xml xmltype,
suppression_xml xmltype);
c1_rec rec_type;
cursor consumer_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable('Consumer' passing t.p_xml columns FirstName
varchar2(40) path '@FirstName',
LastName varchar2(40) path '@LastName',
Addressline1 varchar2(40) path '@AddressLine1',
Addressline2 varchar2(40) path '@AddressLine2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone') c;
TYPE indiv_rec_type is record(
FirstName varchar2(40),
LastName varchar2(40),
Addressline1 varchar2(40),
Addressline2 varchar2(40),
City varchar2(30),
State varchar2(5),
Zipcode varchar2(7),
birth_dt varchar2(30),
emailaddress varchar2(100),
phonenum varchar2(30));
indiv_rec indiv_rec_type;
cursor campaign_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable('Campaign' passing t.p_xml columns Cpgn_cd
varchar2(40) path '@CampaignCode',
promo_cd varchar2(40) path '@PromoCode',
kit_cd varchar2(40) path '@KitCode',
offer_cd varchar2(40) path '@OfferCode') c;
TYPE camp_rec_type is record(
cpgn_cd varchar2(40),
promo_cd varchar2(40),
kit_cd varchar2(40),
offer_cd varchar2(40)
);
camp_rec camp_rec_type;
cursor resp_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable(
'Response'
passing t.p_xml
columns
moc varchar2(40) path '@MediaOriginCode',
rsp_dt varchar2(40) path '@ResponseDate'
) c;
TYPE resp_rec_type is record(
moc varchar2(40),
rsp_dt varchar2(40)
);
resp_rec resp_rec_type;
begin
open c1;
loop
fetch c1
into c1_rec;
exit when c1%notfound;
dbms_output.put_line('============================');
dbms_output.put_line('Interaction : ' || c1_rec.id);
dbms_output.put_line('============================');
if c1_rec.consumer_xml is not null then
dbms_output.put_line('Consumer element present');
-- Parse consumer elements
open consumer_cur(c1_rec.consumer_xml);
loop
fetch consumer_cur
into indiv_rec;
exit when consumer_cur%notfound;
dbms_output.put_line('First Name : ' || indiv_rec.firstname);
dbms_output.put_line('Phone : ' || indiv_rec.phonenum);
dbms_output.put_line('Email : ' || indiv_rec.emailaddress);
end loop;
close consumer_cur;
end if;
if c1_rec.campaign_xml is not null then
dbms_output.put_line('Campaign element present');
-- Parse consumer elements
open campaign_cur(c1_rec.campaign_xml);
loop
fetch campaign_cur
into camp_rec;
exit when campaign_cur%notfound;
dbms_output.put_line('Campaigncode : ' || camp_rec.cpgn_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.promo_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.kit_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.offer_cd);
end loop;
close campaign_cur;
end if;
if c1_rec.resp_xml is not null then
dbms_output.put_line('Response element present');
-- Parse consumer elements
open resp_cur(c1_rec.resp_xml);
loop
fetch resp_cur
into resp_rec;
exit when resp_cur%notfound;
dbms_output.put_line('MOC : ' || resp_rec.moc);
dbms_output.put_line('Rsp_DT : ' || resp_rec.rsp_dt);
end loop;
close resp_cur;
end if;
end loop;
close c1;
end;
Output
===========
============================
Interaction : 1
============================
Consumer element present
First Name : firstname1
Phone : (999)123-9999
Email : test@yahoo.com
Campaign element present
Campaigncode : CPGN1
Campaigncode : Promo1
Campaigncode : KIT1
Campaigncode : Offer1
Response element present
MOC : MOC739
Rsp_DT : 2013-01-19T10:35:25.000-05:00
============================
Interaction : 2
============================
Consumer element present
First Name : firstname2
Phone : (888)123-1000
Email :
Campaign element present
Campaigncode : CPGN1
Campaigncode : Promo1
Campaigncode : KIT1
Campaigncode : Offer1
Response element present
MOC : MOC739
Rsp_DT : 2013-01-19T10:35:25.000-05:00
============================
Interaction : 3
============================
Consumer element present
First Name : firstname3
Phone : (999)123-1000
Email :
Campaign element present
Campaigncode : CPGN3
Campaigncode : Promo3
Campaigncode : KIT3
Campaigncode : Offer3
Response element present
MOC : MOC740
Rsp_DT : 2013-01-20T10:35:25.000-05:00
Using Bulk collect
-- Created on 1/21/2013 by SMINNAKANTI
declare
v_xml xmltype;
cursor c1 is
with t as
(select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd9999">
<Consumer AddressLine1="1234 xyz Ave" CaptureDate="2013-01-20T10:35:25.000-05:00" City="Milford" FirstName="firstname3" LastName="LastName3" PrimaryPhone="(999)123-1000" State="CT" ZipCodeBase="06824"/>
<Campaign CampaignCode="CPGN3" KitCode="KIT3" OfferCode="Offer3" PromoCode="Promo3"/>
<Response MediaOriginCode="MOC740" ResponseDate="2013-01-20T10:35:25.000-05:00"/>
<MultiSuppressions SuppressDate= "2013-01-01T23:59:59" >
<MultiSuppression MultiSuppressionCode= "1" />
<MultiSuppression MultiSuppressionCode= "99" />
</MultiSuppressions>
</Interaction>
</Interactions>') xml_doc
from dual)
select p.*
from t,
xmltable('/Interactions/Interaction' passing xml_doc columns
interaction_id for ordinality,
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
campaign xmltype path 'Campaign',
response xmltype path 'Response',
survey_xml xmltype path 'Survey',
supp_xml xmltype path 'MultiSuppressions') p;
/*type rec_type is record(
id number,
src_cd varchar2(30),
extrn_id varchar2(30),
prod_cd varchar2(20),
consumer_xml xmltype,
campaign_xml xmltype,
resp_xml xmltype,
survey_xml xmltype,
suppression_xml xmltype);*/
type tmp_xml_type is table of xmltype index by binary_integer;
type rec_type is record(
id dbms_sql.Number_Table,
src_cd dbms_sql.Varchar2_Table,
extrn_id dbms_sql.Varchar2_Table,
prod_cd dbms_sql.Varchar2_Table,
consumer_xml tmp_xml_type,
campaign_xml tmp_xml_type,
resp_xml tmp_xml_type,
survey_xml tmp_xml_type,
suppression_xml tmp_xml_type);
c1_rec rec_type;
cursor consumer_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable('Consumer' passing t.p_xml columns FirstName
varchar2(40) path '@FirstName',
LastName varchar2(40) path '@LastName',
Addressline1 varchar2(40) path '@AddressLine1',
Addressline2 varchar2(40) path '@AddressLine2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone') c;
TYPE indiv_rec_type is record(
FirstName varchar2(40),
LastName varchar2(40),
Addressline1 varchar2(40),
Addressline2 varchar2(40),
City varchar2(30),
State varchar2(5),
Zipcode varchar2(7),
birth_dt varchar2(30),
emailaddress varchar2(100),
phonenum varchar2(30));
indiv_rec indiv_rec_type;
cursor campaign_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable('Campaign' passing t.p_xml columns Cpgn_cd
varchar2(40) path '@CampaignCode',
promo_cd varchar2(40) path '@PromoCode',
kit_cd varchar2(40) path '@KitCode',
offer_cd varchar2(40) path '@OfferCode') c;
TYPE camp_rec_type is record(
cpgn_cd varchar2(40),
promo_cd varchar2(40),
kit_cd varchar2(40),
offer_cd varchar2(40)
);
camp_rec camp_rec_type;
cursor resp_cur(p_xml in xmltype) is
select c.*
from (select p_xml from dual) t,
xmltable(
'Response'
passing t.p_xml
columns
moc varchar2(40) path '@MediaOriginCode',
rsp_dt varchar2(40) path '@ResponseDate'
) c;
TYPE resp_rec_type is record(
moc varchar2(40),
rsp_dt varchar2(40)
);
resp_rec resp_rec_type;
begin
open c1;
loop
fetch c1
bulk collect into c1_rec;
exit when c1%notfound;
for i in c1_rec.id.first..c1_rec.id.last loop
dbms_output.put_line('============================');
dbms_output.put_line('Interaction : ' || c1_rec.id(i));
dbms_output.put_line('============================');
if c1_rec.consumer_xml(i) is not null then
dbms_output.put_line('Consumer element present');
-- Parse consumer elements
open consumer_cur(c1_rec.consumer_xml(i));
loop
fetch consumer_cur
into indiv_rec;
exit when consumer_cur%notfound;
dbms_output.put_line('First Name : ' || indiv_rec.firstname);
end loop;
close consumer_cur;
end if;
if c1_rec.campaign_xml(i) is not null then
dbms_output.put_line('Campaign element present');
-- Parse consumer elements
open campaign_cur(c1_rec.campaign_xml(i));
loop
fetch campaign_cur
into camp_rec;
exit when campaign_cur%notfound;
dbms_output.put_line('Campaigncode : ' || camp_rec.cpgn_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.promo_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.kit_cd);
dbms_output.put_line('Campaigncode : ' || camp_rec.offer_cd);
end loop;
close campaign_cur;
end if;
if c1_rec.resp_xml(i) is not null then
dbms_output.put_line('Response element present');
-- Parse consumer elements
open resp_cur(c1_rec.resp_xml(i));
loop
fetch resp_cur
into resp_rec;
exit when resp_cur%notfound;
dbms_output.put_line('MOC : ' || resp_rec.moc);
dbms_output.put_line('Rsp_DT : ' || resp_rec.rsp_dt);
end loop;
close resp_cur;
end if;
end loop;
end loop;
close c1;
end;
|
|
|
|
| Re: XML parsing help [message #575371 is a reply to message #575366] |
Tue, 22 January 2013 10:16 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You misunderstand BULK COLLECT. You don't loop with BULK COLLECT (unless LIMIT is used) and since you fetch bulk - you fetch into a collection which you didn't declare. So:
type rec_type is record(
id dbms_sql.Number_Table,
src_cd dbms_sql.Varchar2_Table,
extrn_id dbms_sql.Varchar2_Table,
prod_cd dbms_sql.Varchar2_Table,
consumer_xml tmp_xml_type,
campaign_xml tmp_xml_type,
resp_xml tmp_xml_type,
survey_xml tmp_xml_type,
suppression_xml tmp_xml_type);
c1_rec rec_type;
Should be:
type rec_type is record(
id dbms_sql.Number_Table,
src_cd dbms_sql.Varchar2_Table,
extrn_id dbms_sql.Varchar2_Table,
prod_cd dbms_sql.Varchar2_Table,
consumer_xml tmp_xml_type,
campaign_xml tmp_xml_type,
resp_xml tmp_xml_type,
survey_xml tmp_xml_type,
suppression_xml tmp_xml_type);
type rec_tbl_type is table of rec_type;
c1_rec_tbl rec_tbl_type;
And:
begin
open c1;
loop
fetch c1
bulk collect into c1_rec;
exit when c1%notfound;
for i in c1_rec.id.first..c1_rec.id.last loop
Should be:
begin
open c1;
fetch c1
bulk collect into c1_rec_tbl;
for i in c1_rec_tbl.count loop
And so on...
But why do you need BULK? How many interactions whill XML have? But most importantly, PL/SQL will be slower than SQL. What was wrong with solution I suggested?
SY.
|
|
|
|
| Re: XML parsing help [message #575372 is a reply to message #575371] |
Tue, 22 January 2013 10:30 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Solomon.
Yes i do agree with you, pl/sql is slower when compared with SQL. Your solution is working without any issue if i have survey element with one MultiSuppressioncode element in one interaction. If i have more than one multisuppressioncode element in one interaction along with Survey element then the query is cartesianing survey and multisuppression data which will insert duplicate records into survey table while using INSERT ALL.
Thanks
Sri
|
|
|
|
| Re: XML parsing help [message #575374 is a reply to message #575372] |
Tue, 22 January 2013 11:02 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
sminnakanti wrote on Tue, 22 January 2013 11:30If i have more than one multisuppressioncode element in one interaction along with Survey element then the query is cartesianing survey and multisuppression data which will insert duplicate records into survey table while using INSERT ALL.
Then your query isn't right. Post the query.
SY.
|
|
|
|
| Re: XML parsing help [message #575385 is a reply to message #575374] |
Tue, 22 January 2013 14:13 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Solomon for helping me on this requirement.
This is the query. You can find all the table definitions on the top. Table3 and Table4 has duplicates.
Insert all
when (rnk =1)
then into table1 (interaction_id, sourcecode, externalid,productcode, addressline1, addressline2, city, state,zipcode, birth_dt,emailaddress,phonenum)
values (interaction_id, sourcecode, externalid, productcode, addressline1,addressline2, city,state, zipcode, birth_dt, emailaddress,phonenum)
when (rnk=1)
then into table2 (interaction_id, campaigncode, promocode, kitcode,offercode,mediaorigcode,responsedate)
values (interaction_id, cpgn_cd,promo_cd,kit_cd,offer_cd, moc, rsp_dt)
when (1=1 and questionid is not null)
then into table3 (interaction_id, questionid, answerid, openendedquestionind, openendedanswertext)
values (interaction_id, questionid, answerid, questind, anstxt)
when (1=1 and suppresscode is not null)
then into table4 (interaction_id, multisuppressioncode)
values (interaction_id, Suppresscode)
with t as (select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>') xml_doc from dual)
select a.*, rank() over(partition by interaction_id order by rec_num) rnk
from
(select dense_rank() over(order by externalid) interaction_id,
rownum rec_num,
i.*, c.*, cpko.*, r.*, s.*, supp.*
from t,
xmltable(
'/Interactions/Interaction'
passing xml_doc
columns
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
campaign xmltype path 'Campaign',
response xmltype path 'Response',
survey_xml xmltype path 'Survey',
supp_xml xmltype path 'MultiSuppressions'
) i,
xmltable(
'Consumer'
passing consumer
columns
Addressline1 varchar2(40) path '@AddressLine1',
Addressline2 varchar2(40) path '@AddressLine2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone'
) c,
xmltable('Campaign'
passing campaign
columns
Cpgn_cd varchar2(40) path '@CampaignCode',
promo_cd varchar2(40) path '@PromoCode',
kit_cd varchar2(40) path '@KitCode',
offer_cd varchar2(40) path '@OfferCode') cpko,
xmltable(
'Response'
passing response
columns
moc varchar2(40) path '@MediaOriginCode',
rsp_dt varchar2(40) path '@ResponseDate'
) r,
xmltable(
'/Survey/Answers/Answer'
passing survey_xml
columns
QuestionID varchar2(4000) path '@QuestionID',
AnswerID varchar2(4000) path '@AnswerID',
QuestInd varchar2(4000) path '@OpenEndedQuestionInd',
AnsTxt varchar2(4000) path '@OpenEndedAnswerText'
) (+) s,
xmltable(
'/MultiSuppressions/MultiSuppression'
passing supp_xml
columns
Suppresscode varchar2(5) path '@MultiSuppressionCode'
) (+) supp
) a
Thanks
Sri
|
|
|
|
| Re: XML parsing help [message #575389 is a reply to message #575385] |
Tue, 22 January 2013 16:45 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following may not be the most efficient method, but does appear to eliminate the duplicates. I have added rnk2 and rnk3 to the when clauses and outer select statement. Solomon may have a better method.
Insert all
when (rnk =1)
then into table1 (interaction_id, sourcecode, externalid,productcode, addressline1, addressline2, city, state,zipcode, birth_dt,emailaddress,phonenum)
values (interaction_id, sourcecode, externalid, productcode, addressline1,addressline2, city,state, zipcode, birth_dt, emailaddress,phonenum)
when (rnk=1)
then into table2 (interaction_id, campaigncode, promocode, kitcode,offercode,mediaorigcode,responsedate)
values (interaction_id, cpgn_cd,promo_cd,kit_cd,offer_cd, moc, rsp_dt)
when (rnk2=1 and questionid is not null)
then into table3 (interaction_id, questionid, answerid, openendedquestionind, openendedanswertext)
values (interaction_id, questionid, answerid, questind, anstxt)
when (rnk3=1 and suppresscode is not null)
then into table4 (interaction_id, multisuppressioncode)
values (interaction_id, Suppresscode)
with t as (select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>') xml_doc from dual)
select a.*, rank() over(partition by interaction_id order by rec_num) rnk,
rank() over(partition by interaction_id order by suppresscode) rnk2,
rank() over(partition by interaction_id order by answerid, questionid) rnk3
from
(select dense_rank() over(order by externalid) interaction_id,
rownum rec_num,
i.*, c.*, cpko.*, r.*, s.*, supp.*
from t,
xmltable(
'/Interactions/Interaction'
passing xml_doc
columns
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
campaign xmltype path 'Campaign',
response xmltype path 'Response',
survey_xml xmltype path 'Survey',
supp_xml xmltype path 'MultiSuppressions'
) i,
xmltable(
'Consumer'
passing consumer
columns
Addressline1 varchar2(40) path '@AddressLine1',
Addressline2 varchar2(40) path '@AddressLine2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(100) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone'
) c,
xmltable('Campaign'
passing campaign
columns
Cpgn_cd varchar2(40) path '@CampaignCode',
promo_cd varchar2(40) path '@PromoCode',
kit_cd varchar2(40) path '@KitCode',
offer_cd varchar2(40) path '@OfferCode') cpko,
xmltable(
'Response'
passing response
columns
moc varchar2(40) path '@MediaOriginCode',
rsp_dt varchar2(40) path '@ResponseDate'
) r,
xmltable(
'/Survey/Answers/Answer'
passing survey_xml
columns
QuestionID varchar2(4000) path '@QuestionID',
AnswerID varchar2(4000) path '@AnswerID',
QuestInd varchar2(4000) path '@OpenEndedQuestionInd',
AnsTxt varchar2(4000) path '@OpenEndedAnswerText'
) (+) s,
xmltable(
'/MultiSuppressions/MultiSuppression'
passing supp_xml
columns
Suppresscode varchar2(5) path '@MultiSuppressionCode'
) (+) supp
) a
/
|
|
|
|
| Re: XML parsing help [message #575455 is a reply to message #575385] |
Wed, 23 January 2013 07:23 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Insert all
when (interaction_id_rn = 1)
then into table1 (interaction_id, sourcecode, externalid,productcode, addressline1, addressline2, city, state,zipcode, birth_dt,emailaddress,phonenum)
values (interaction_id, sourcecode, externalid, productcode, addressline1,addressline2, city,state, zipcode, birth_dt, emailaddress,phonenum)
when (interaction_id_rn = 1)
then into table2 (interaction_id, campaigncode, promocode, kitcode,offercode,mediaorigcode,responsedate)
values (interaction_id, cpgn_cd,promo_cd,kit_cd,offer_cd, moc, rsp_dt)
when (Suppresscode_rnk = 1 and questionid is not null)
then into table3 (interaction_id, questionid, answerid, openendedquestionind, openendedanswertext)
values (interaction_id, questionid, answerid, questind, anstxt)
when (Suppresscode_id_rn = 1 and suppresscode is not null)
then into table4 (interaction_id, multisuppressioncode)
values (interaction_id, Suppresscode)
with t as (select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Charleston" EmailAddress="test@yahoo.com" FirstName="firstname1" LastName="LastName1" PrimaryPhone="(999)123-9999" State="SC" ZipCodeBase="29403"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0005" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0567"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0004" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="3/1/1987" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="59620414657" OpenEndedQuestionInd="O" QuestionID="QDAKGM0273"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="16" OpenEndedQuestionInd="O" QuestionID="QDAKGM0562"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="2cv0711961" OpenEndedQuestionInd="O" QuestionID="QDAKGM0563"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="09400006" OpenEndedQuestionInd="O" QuestionID="QDAKGM0564"/>
</Answers>
</Survey>
<MultiSuppressions SuppressDate= "2013-01-17T23:59:59" >
<MultiSuppression MultiSuppressionCode= "2" />
<MultiSuppression MultiSuppressionCode= "16" />
</MultiSuppressions>
</Interaction>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd4567">
<Consumer AddressLine1="1234 abcd Ave" CaptureDate="2013-01-19T10:35:25.000-05:00" City="Dallas" FirstName="firstname2" LastName="LastName2" PrimaryPhone="(888)123-1000" State="SC" ZipCodeBase="75698"/>
<Campaign CampaignCode="CPGN1" KitCode="KIT1" OfferCode="Offer1" PromoCode="Promo1"/>
<Response MediaOriginCode="MOC739" ResponseDate="2013-01-19T10:35:25.000-05:00"/>
<Survey>
<Answers>
<Answer AnswerID="ADAKGM0040" QuestionID="QDAKGM0112"/>
<Answer AnswerID="ADAKGM0007" QuestionID="QDAKGM0102"/>
<Answer AnswerID="ADAKGM0006" QuestionID="QDAKGM0565"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0118"/>
<Answer AnswerID="ADAKGM0001" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0002" QuestionID="QDAKGM0103"/>
<Answer AnswerID="ADAKGM0001" OpenEndedAnswerText="12/1/2012" OpenEndedQuestionInd="O" QuestionID="QDAKGM0111"/>
</Answers>
</Survey>
</Interaction>
</Interactions>') xml_doc from dual)
select dense_rank() over(order by externalid) interaction_id,
row_number() over(partition by externalid order by 1) interaction_id_rn,
dense_rank() over(partition by externalid order by Suppresscode) Suppresscode_rnk,
row_number() over(partition by externalid,Suppresscode order by 1) Suppresscode_id_rn,
sourcecode,
externalid,
productcode,
Addressline1,
Addressline2,
City,
State,
Zipcode,
birth_dt,
emailaddress,
phonenum,
Cpgn_cd,
promo_cd,
kit_cd,
offer_cd,
moc,
rsp_dt,
QuestionID,
AnswerID,
QuestInd,
AnsTxt,
Suppresscode
from t,
xmltable(
'/Interactions/Interaction'
passing xml_doc
columns
sourcecode varchar2(20) path '@SourceCode',
externalid varchar2(20) path '@ExternalID',
productcode varchar2(20) path '@ProductCode',
consumer xmltype path 'Consumer',
campaign xmltype path 'Campaign',
response xmltype path 'Response',
survey_xml xmltype path 'Survey',
supp_xml xmltype path 'MultiSuppressions'
) i,
xmltable(
'Consumer'
passing consumer
columns
Addressline1 varchar2(40) path '@AddressLine1',
Addressline2 varchar2(40) path '@AddressLine2',
City varchar2(30) path '@City',
State varchar2(5) path '@State',
Zipcode varchar2(7) path '@ZipCodeBase',
birth_dt varchar2(30) path '@BirthDate',
emailaddress varchar2(30) path '@EmailAddress',
phonenum varchar2(14) path '@PrimaryPhone'
),
xmltable('Campaign'
passing campaign
columns
Cpgn_cd varchar2(40) path '@CampaignCode',
promo_cd varchar2(40) path '@PromoCode',
kit_cd varchar2(40) path '@KitCode',
offer_cd varchar2(40) path '@OfferCode') cpko,
xmltable(
'Response'
passing response
columns
moc varchar2(40) path '@MediaOriginCode',
rsp_dt varchar2(40) path '@ResponseDate'
),
xmltable(
'/Survey/Answers/Answer'
passing survey_xml
columns
QuestionID varchar2(40) path '@QuestionID',
AnswerID varchar2(40) path '@AnswerID',
QuestInd varchar2(40) path '@OpenEndedQuestionInd',
AnsTxt varchar2(40) path '@OpenEndedAnswerText'
) (+),
xmltable(
'/MultiSuppressions/MultiSuppression'
passing supp_xml
columns
Suppresscode varchar2(5) path '@MultiSuppressionCode'
) (+) supp
/
SY.
|
|
|
|
| Re: XML parsing help [message #575523 is a reply to message #575455] |
Wed, 23 January 2013 17:08 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Solomon and Barbara for your time on helping with this query. Query is working great.
Tested the query with my production file which has 800+ interactions (near 10,000+ survey records) and the query is running forever. Converted query to PL/SQL block which finished in 31 secs. Please take a look at the pl/sql block and suggest me if i can further tune the code. Appreciate your help.
-- Created on 1/21/2013 by SMINNAKANTI
declare
v_xml xmltype;
type tmp_xml_type is table of xmltype index by binary_integer;
type rec_type is record(
id dbms_sql.Number_Table,
src_cd dbms_sql.Varchar2_Table,
extrn_id dbms_sql.Varchar2_Table,
prod_cd dbms_sql.Varchar2_Table,
FirstName dbms_sql.Varchar2_Table,
LastName dbms_sql.Varchar2_Table,
Addressline1 dbms_sql.Varchar2_Table,
Addressline2 dbms_sql.Varchar2_Table,
city dbms_sql.Varchar2_Table,
state dbms_sql.Varchar2_Table,
zipcode dbms_sql.Varchar2_Table,
birth_dt dbms_sql.Varchar2_Table,
emailaddress dbms_sql.Varchar2_Table,
phonenum dbms_sql.Varchar2_Table,
cpgn_cd dbms_sql.Varchar2_Table,
promo_cd dbms_sql.Varchar2_Table,
kit_cd dbms_sql.Varchar2_Table,
offer_cd dbms_sql.Varchar2_Table,
moc dbms_sql.Varchar2_Table,
rsp_dt dbms_sql.Varchar2_Table,
consumer_xml tmp_xml_type,
survey_xml tmp_xml_type,
supp_date dbms_sql.varchar2_table,
supp_xml tmp_xml_type);
c1_rec rec_type;
TYPE survey_rec_type is record(
Questionid dbms_sql.Varchar2_Table,
Answerid dbms_sql.Varchar2_Table,
QuestInd dbms_sql.Varchar2_Table,
Anstxt dbms_sql.Varchar2_Table);
survey_rec survey_rec_type;
TYPE supp_rec_type is record(
SuppressCode dbms_sql.Varchar2_Table);
supp_rec supp_rec_type;
TYPE indiv_rec_type is table of table1%rowtype;
TYPE promo_rec_type IS TABLE OF table2%rowtype;
TYPE svy_rec_type IS TABLE OF table3%rowtype;
TYPE suppression_rec_type IS TABLE OF table4%rowtype;
indiv_rec_tab indiv_rec_type := indiv_rec_type();
promo_rec_tab promo_rec_type := promo_rec_type();
survey_rec_tab svy_rec_type := svy_rec_type();
survey_rsp_holder_tab svy_rec_type := svy_rec_type();
supp_rec_tab suppression_rec_type := suppression_rec_type();
v_c_last number := 0;
v_p_last number := 0;
v_s_last number := 0;
v_ss_last number := 0;
v_sp_last number := 0;
rec_num number := 0;
v_rsp_dt varchar2(40);
v_interact_id varchar2(50);
v_extrn_id varchar2(30);
v_cmd clob;
p_xml varchar2(100) := 'RCDG_CALL_STD_DAKGM_INTR_M_20130114052016_874_IBMK.xml';
c1 sys_refcursor;
survey_cur sys_refcursor;
supp_cur sys_refcursor;
procedure parse_survey (p_in_xml xmltype,
p_out_col out survey_rec_type
) is
begin
-- Get the survey cursor from table
get_command(i_name => 'STANDARD_XML_SURVEY',
o_command => v_cmd);
open survey_cur for to_char(v_cmd)
using p_in_xml;
loop
fetch survey_cur bulk collect
into p_out_col;
exit when survey_cur%notfound;
end loop;
close survey_cur;
end;
procedure parse_supp (p_in_xml xmltype,
p_out_col out supp_rec_type
) is
begin
-- get the sql to parse Suppression elements for each interaction
get_command(i_name => 'STANDARD_XML_SUPP',
o_command => v_cmd);
open supp_cur for to_char(v_cmd)
using p_in_xml;
loop
fetch supp_cur bulk collect
into p_out_col;
exit when supp_cur%notfound;
end loop;
close supp_cur;
end;
begin
-- get the sql to parse main part of XML (i.e. Interaction)
get_command(i_name => 'STANDARD_XML_PARSE',
o_command => v_cmd);
v_cmd := replace(v_cmd, '${XML_FILE}', p_xml);
open c1 for to_char(v_cmd);
loop
fetch c1 bulk collect
into c1_rec limit 100;
if c1_rec.src_cd.count > 0 then
for i in c1_rec.src_cd.first .. c1_rec.src_cd.last loop
v_interact_id := c1_rec.id(i);
v_extrn_id := c1_rec.extrn_id(i);
indiv_rec_tab.extend;
v_c_last := indiv_rec_tab.last;
promo_rec_tab.extend;
v_p_last := promo_rec_tab.last;
indiv_rec_tab(v_c_last).interaction_id := c1_rec.id(i);
indiv_rec_tab(v_c_last).sourcecode := c1_rec.src_cd(i);
indiv_rec_tab(v_c_last).externalid := c1_rec.extrn_id(i);
indiv_rec_tab(v_c_last).productcode := c1_rec.prod_cd(i);
promo_rec_tab(v_p_last).interaction_id := c1_rec.id(i);
indiv_rec_tab(v_c_last).firstname := c1_rec.firstname(i);
indiv_rec_tab(v_c_last).lastname := c1_rec.lastname(i);
indiv_rec_tab(v_c_last).emailaddress := c1_rec.emailaddress(i);
indiv_rec_tab(v_c_last).phonenum := c1_rec.phonenum(i);
indiv_rec_tab(v_c_last).addressline1 := c1_rec.addressline1(i);
indiv_rec_tab(v_c_last).addressline2 := c1_rec.addressline2(i);
indiv_rec_tab(v_c_last).city := c1_rec.city(i);
indiv_rec_tab(v_c_last).state := c1_rec.state(i);
indiv_rec_tab(v_c_last).birth_dt := c1_rec.birth_dt(i);
promo_rec_tab(v_p_last).campaigncode := c1_rec.cpgn_cd(i);
promo_rec_tab(v_p_last).promocode := c1_rec.promo_cd(i);
promo_rec_tab(v_p_last).kitcode := c1_rec.kit_cd(i);
promo_rec_tab(v_p_last).offercode := c1_rec.offer_cd(i);
promo_rec_tab(v_p_last).mediaorigcode := c1_rec.moc(i);
promo_rec_tab(v_p_last).responsedate := c1_rec.rsp_dt(i);
v_rsp_dt := c1_rec.rsp_dt(i);
survey_rsp_holder_tab.delete;
survey_rsp_holder_tab.extend;
v_s_last := survey_rsp_holder_tab.last;
survey_rsp_holder_tab(v_s_last).interaction_id := c1_rec.id(i);
survey_rsp_holder_tab(v_s_last).RSP_DT := v_rsp_dt;
survey_rsp_holder_tab(v_s_last).EXTRN_ID := c1_rec.extrn_id(i);
survey_rsp_holder_tab(v_s_last).SRC_CD := c1_rec.src_cd(i);
if c1_rec.survey_xml(i) is not null then
-- Procedure to parse survey elements in xml
parse_survey(p_in_xml => c1_rec.survey_xml(i), p_out_col => survey_rec);
for j in survey_rec.questionid.first .. survey_rec.questionid.last loop
if survey_rec.questionid(j) is not null then
survey_rec_tab.extend;
v_ss_last := survey_rec_tab.last;
rec_num := rec_num + 1;
survey_rec_tab(v_ss_last) := survey_rsp_holder_tab(v_s_last);
survey_rec_tab(v_ss_last).questionid := survey_rec.questionid(j);
survey_rec_tab(v_ss_last).answerid := survey_rec.answerid(j);
survey_rec_tab(v_ss_last).openendedquestionind := survey_rec.questind(j);
survey_rec_tab(v_ss_last).openendedanswertext := survey_rec.anstxt(j);
survey_rec_tab(v_ss_last).rec_num := rec_num;
end if;
end loop;
end if;
if c1_rec.supp_xml(i) is not null then
-- Procedure to parse suppression elements in xml
parse_supp(p_in_xml => c1_rec.supp_xml(i), p_out_col => supp_rec);
for j in supp_rec.suppresscode.first .. supp_rec.suppresscode.last loop
if supp_rec.suppresscode(j) is not null then
supp_rec_tab.extend;
v_sp_last := supp_rec_tab.last;
supp_rec_tab(v_sp_last).extrn_id := c1_rec.extrn_id(i);
supp_rec_tab(v_sp_last).src_cd := c1_rec.src_cd(i);
supp_rec_tab(v_sp_last).interaction_id := c1_rec.id(i);
supp_rec_tab(v_sp_last).multisuppressiondate := c1_rec.supp_date(i);
supp_rec_tab(v_sp_last).multisuppressioncode := supp_rec.suppresscode(j);
end if;
end loop;
end if;
end loop;
IF indiv_rec_tab.count > 0 THEN
FORALL i IN INDICES OF indiv_rec_tab
INSERT INTO table1 VALUES indiv_rec_tab (i);
indiv_rec_tab.delete;
COMMIT;
END IF;
IF promo_rec_tab.count > 0 THEN
FORALL i IN INDICES OF promo_rec_tab
INSERT INTO table2 VALUES promo_rec_tab (i);
promo_rec_tab.delete;
COMMIT;
END IF;
IF survey_rec_tab.count > 0 THEN
FORALL i IN INDICES OF survey_rec_tab
INSERT INTO table3 VALUES survey_rec_tab (i);
survey_rec_tab.delete;
COMMIT;
END IF;
IF supp_rec_tab.count > 0 THEN
FORALL i IN INDICES OF supp_rec_tab
INSERT INTO table4 VALUES supp_rec_tab (i);
supp_rec_tab.delete;
COMMIT;
END IF;
end if;
EXIT WHEN c1%notfound;
end loop;
close c1;
commit;
exception
WHEN OTHERS THEN
db.p('Error :' || db.backtrace);
RAISE;
end;
Thanks
Srinath
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:30:33 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
