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 Go to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 #575250 is a reply to message #575249] Mon, 21 January 2013 13:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
No, write a query to get all desired columns from XML document and use INSERT ALL to populate proper columns to proper tables.

SY.
Re: XML parsing help [message #575256 is a reply to message #575250] Mon, 21 January 2013 14:10 Go to previous messageGo to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
sminnakanti wrote on Mon, 21 January 2013 15:10
Problem 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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 #575273 is a reply to message #575260] Mon, 21 January 2013 19:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
Then you need to find something else that is unique within interaction. Is CaptureDate unique? If so, use it in dense_rank.

SY.
Re: XML parsing help [message #575366 is a reply to message #575273] Tue, 22 January 2013 09:48 Go to previous messageGo to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
sminnakanti wrote on Tue, 22 January 2013 11:30
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.


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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
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 Go to previous message
sspn2010
Messages: 147
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
Previous Topic: Reset expired password with Java
Next Topic: Select data from XMLType column
Goto Forum:
  


Current Time: Wed Nov 26 00:30:27 CST 2014

Total time taken to generate the page: 0.15105 seconds