Home » Developer & Programmer » JDeveloper, Java & XML » Query help to generate XML (Oracle 10.2.0.4, AIX 5.3)
Query help to generate XML [message #578746] Mon, 04 March 2013 11:59 Go to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Hi,

I've below table.


create table TEST_RESPONSE
(
  rsp_id     NUMBER(10),
  rsp_dt     DATE,
  hist_id    NUMBER(10),
  cpgn_cd    VARCHAR2(15),
  promo_cd   VARCHAR2(15),
  moc        VARCHAR2(15),
  quest_num  VARCHAR2(15),
  ans_num    VARCHAR2(15),
  prod_cd    VARCHAR2(10),
  first_nm   VARCHAR2(40),
  last_nm    VARCHAR2(40),
  addr_line1 VARCHAR2(40),
  city       VARCHAR2(30),
  state      VARCHAR2(5)
);

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (1, to_date('22-12-2012 14:32:01', 'dd-mm-yyyy hh24:mi:ss'), 100, 'CPGN1', 'PROMO1', 'MOC1', 'Q1', 'A1', 'P1', 'TEST_FIRST1', 'TEST_LAST1', 'TEST_ADDR_1', 'MILFORD', 'OH');

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (1, to_date('22-12-2012 14:32:01', 'dd-mm-yyyy hh24:mi:ss'), 100, 'CPGN1', 'PROMO1', 'MOC1', 'Q2', 'A2', 'P1', 'TEST_FIRST1', 'TEST_LAST1', 'TEST_ADDR_1', 'MILFORD', 'OH');

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (1, to_date('22-12-2012 14:32:01', 'dd-mm-yyyy hh24:mi:ss'), 100, 'CPGN1', 'PROMO1', 'MOC1', 'Q3', 'A3', 'P1', 'TEST_FIRST1', 'TEST_LAST1', 'TEST_ADDR_1', 'MILFORD', 'OH');

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (2, to_date('02-01-2013 11:45:00', 'dd-mm-yyyy hh24:mi:ss'), 200, 'CPGN2', 'PROMO2', 'MOC2', 'Q1', 'A1', 'P1', 'TEST_FIRST2', 'TEST_LAST2', 'TEST_ADDR_2', 'EDISON', 'NJ');

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (2, to_date('02-01-2013 11:45:00', 'dd-mm-yyyy hh24:mi:ss'), 200, 'CPGN2', 'PROMO2', 'MOC2', 'Q5', 'A5', 'P1', 'TEST_FIRST2', 'TEST_LAST2', 'TEST_ADDR_2', 'EDISON', 'NJ');

insert into test_response (RSP_ID, RSP_DT, HIST_ID, CPGN_CD, PROMO_CD, MOC, QUEST_NUM, ANS_NUM, PROD_CD, FIRST_NM, LAST_NM, ADDR_LINE1, CITY, STATE)
values (2, to_date('02-01-2013 11:45:00', 'dd-mm-yyyy hh24:mi:ss'), 200, 'CPGN2', 'PROMO2', 'MOC2', 'Q6', 'A6', 'P1', 'TEST_FIRST2', 'TEST_LAST2', 'TEST_ADDR_2', 'EDISON', 'NJ');

commit;



I need to generate xml in below format from above table. Can someone help with the query to get below xml output.


<Interactions>
 <Interaction ProductCode="P1" ExternalID="100">
  <Individual AddressLine1="TEST_ADDR_1" City="MILFORD" FirstName="TEST_FIRST1" LastName="TEST_LAST1" State="OH" />
  <Campaign CampaignCode="CPGN1" PromoCode="PROMO1"/>
  <Response MediaOriginCode="MOC1" ResponseDate="12/22/2012 2:32:01 PM"/>
  <Survey>
    <Answers>
	<Answer AnswerID="A1" QuestionID="Q1"/>
	<Answer AnswerID="A2" QuestionID="Q2"/>
	<Answer AnswerID="A3" QuestionID="Q3"/>
    </Answers>
  </Survey>
 </Interaction>
 <Interaction ProductCode="P1" ExternalID="200">
  <Individual AddressLine1="TEST_ADDR_2" City="EDISON" FirstName="TEST_FIRST2" LastName="TEST_LAST2" State="NJ" />
  <Campaign CampaignCode="CPGN2" PromoCode="PROMO2"/>
  <Response MediaOriginCode="MOC2" ResponseDate="1/2/2013 11:45:00 AM"/>
  <Survey>
    <Answers>
	<Answer AnswerID="A1" QuestionID="Q1"/>
	<Answer AnswerID="A5" QuestionID="Q5"/>
	<Answer AnswerID="A6" QuestionID="Q6"/>
    </Answers>
  </Survey>
 </Interaction>
</Interactions>



Appreciate your help and Thanks in advance for your time.

Regards
Sri
Re: Query help to generate XML [message #578747 is a reply to message #578746] Mon, 04 March 2013 12:05 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Take a look at Generating XML Data from the Database


Regards,
Dariyoosh
Re: Query help to generate XML [message #578748 is a reply to message #578747] Mon, 04 March 2013 12:39 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Thank you Dariyoosh for the link.

I wrote below query, but it's giving duplicate interactions for each response and also end tags (i.e. </Individual>, </Campaign>, </Response>, </Answer>). For each response (i.e. each rsp_id ) i need only one interaction. How can i fix that in my query.



select xmlelement("Interactions",
                  xmlagg(xmlelement("Interaction",
                                    xmlattributes('W' as "ChannelCode",
                                                  prod_cd as "ProductCode",
                                                  hist_id as "ExternalID"),
                                    xmlelement("Individial",
                                               xmlattributes(first_nm as
                                                             "FirstName",
                                                             last_nm as
                                                             "LastName",
                                                             addr_line1 as
                                                             "AddressLine1",
                                                             city as "City",
                                                             state as "State")),
                                    xmlelement("Campaign",
                                               xmlattributes(cpgn_cd as
                                                             "CampaignCode",
                                                             promo_cd as
                                                             "PromoCode")),
                                    xmlelement("Response",
                                               xmlattributes(moc as
                                                             "MediaOriginCode",
                                                             rsp_dt as
                                                             "ResponseDate")),
                                    (select XMLELEMENT("Survey",
                                                       XMLELEMENT("Answers",
                                                                  XMLAGG(XMLELEMENT("Answer",
                                                                                    xmlattributes(of1.ans_num as
                                                                                                  "AnswerID",
                                                                                                  of1.quest_num as
                                                                                                  "QuestionID")))))
                                       from test_response of1
                                      where of1.rsp_id = a.rsp_id)))) a
  from test_response a
 where rsp_id = 1;


Getting below output when i ran above query.


<Interactions>
	<Interaction ChannelCode="W" ProductCode="P1" ExternalID="100">
		<Individial FirstName="TEST_FIRST1" LastName="TEST_LAST1" AddressLine1="TEST_ADDR_1" City="MILFORD" State="OH"></Individial>
		<Campaign CampaignCode="CPGN1" PromoCode="PROMO1"></Campaign>
		<Response MediaOriginCode="MOC1" ResponseDate="2012-12-22T14:32:01"></Response>
		<Survey>
			<Answers>
				<Answer AnswerID="A1" QuestionID="Q1"></Answer>
				<Answer AnswerID="A2" QuestionID="Q2"></Answer>
				<Answer AnswerID="A3" QuestionID="Q3"></Answer>
			</Answers>
		</Survey>
	</Interaction>
	<Interaction ChannelCode="W" ProductCode="P1" ExternalID="100">
		<Individial FirstName="TEST_FIRST1" LastName="TEST_LAST1" AddressLine1="TEST_ADDR_1" City="MILFORD" State="OH"></Individial>
		<Campaign CampaignCode="CPGN1" PromoCode="PROMO1"></Campaign>
		<Response MediaOriginCode="MOC1" ResponseDate="2012-12-22T14:32:01"></Response>
		<Survey>
			<Answers>
				<Answer AnswerID="A1" QuestionID="Q1"></Answer>
				<Answer AnswerID="A2" QuestionID="Q2"></Answer>
				<Answer AnswerID="A3" QuestionID="Q3"></Answer>
			</Answers>
		</Survey>
	</Interaction>
	<Interaction ChannelCode="W" ProductCode="P1" ExternalID="100">
		<Individial FirstName="TEST_FIRST1" LastName="TEST_LAST1" AddressLine1="TEST_ADDR_1" City="MILFORD" State="OH"></Individial>
		<Campaign CampaignCode="CPGN1" PromoCode="PROMO1"></Campaign>
		<Response MediaOriginCode="MOC1" ResponseDate="2012-12-22T14:32:01"></Response>
		<Survey>
			<Answers>
				<Answer AnswerID="A1" QuestionID="Q1"></Answer>
				<Answer AnswerID="A2" QuestionID="Q2"></Answer>
				<Answer AnswerID="A3" QuestionID="Q3"></Answer>
			</Answers>
		</Survey>
	</Interaction>
</Interactions>




Regards
Sri
Re: Query help to generate XML [message #578749 is a reply to message #578748] Mon, 04 March 2013 13:57 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
The problem is, that you have provided a test case but it is not clear based on what exactly the XML hierarchy is to be built.


Regards,
Dariyoosh
Re: Query help to generate XML [message #578752 is a reply to message #578749] Mon, 04 March 2013 14:26 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Need to build one interaction element for each unique rsp_id with individual sub elements Individual, Campaign, Response and Survey (Each rsp_id has different quest_num/ans_num values and this will come as part of Survey element) . Modified above query as below and getting the output with end tags for individual, campign, response element tags. How can i get rid of end tags.


select xmlelement("Interactions",
                  xmlagg(xmlelement("Interaction",
                                    xmlattributes('W' as "ChannelCode",
                                                  prod_cd as "ProductCode",
                                                  hist_id as "ExternalID"),
                                    xmlelement("Individial",
                                               xmlattributes(first_nm as
                                                             "FirstName",
                                                             last_nm as
                                                             "LastName",
                                                             addr_line1 as
                                                             "AddressLine1",
                                                             city as "City",
                                                             state as "State")),
                                    xmlelement("Campaign",
                                               xmlattributes(cpgn_cd as
                                                             "CampaignCode",
                                                             promo_cd as
                                                             "PromoCode")),
                                    xmlelement("Response",
                                               xmlattributes(moc as
                                                             "MediaOriginCode",
                                                             to_char(rsp_dt,
                                                                     'YYYY-MM-DD"T"HH24:MI:SS') as
                                                             "ResponseDate")),
                                    (select XMLELEMENT("Survey",
                                                       XMLELEMENT("Answers",
                                                                  XMLAGG(XMLELEMENT("Answer",
                                                                                    xmlattributes(of1.ans_num as
                                                                                                  "AnswerID",
                                                                                                  of1.quest_num as
                                                                                                  "QuestionID")))))
                                       from test_response of1
                                      where of1.rsp_id = a.rsp_id)))) a
  from (select distinct rsp_id,
                        hist_id,
                        cpgn_cd,
                        promo_cd,
                        moc,
                        rsp_dt,
                        first_nm,
                        last_nm,
                        addr_line1,
                        city,
                        state,
                        prod_cd
          from test_response) a
;




Thanks
Sri
Re: Query help to generate XML [message #578759 is a reply to message #578752] Mon, 04 March 2013 15:11 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Quote:
... Need to build one interaction element for each unique rsp_id

What I see in your data sample is that rsp_id is not unique (3 times rsp_id = 1 and 3 times rsp_id = 2)

Regards,
Dariyoosh
Re: Query help to generate XML [message #578760 is a reply to message #578752] Mon, 04 March 2013 15:14 Go to previous message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Added xmlroot function to include xml version in the query and getting the output as wanted without end tags. Thanks for your help Dariyoosh.


select XMLROOT(xmlelement("Interactions",
         XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS
                                  "xmlns:xsi",
                                'http://ulcer.epsilon.com/PharmaInteraction/PDP_XML_RMI-RDC.xsd' AS
                                  "xsi:nonamespaceSchemaLocation"),
                  xmlagg(xmlelement("Interaction",
                                    xmlattributes('W' as "ChannelCode",
                                                  prod_cd as "ProductCode",
                                                  hist_id as "ExternalID"),
                                    xmlelement("Individial",
                                               xmlattributes(first_nm as
                                                             "FirstName",
                                                             last_nm as
                                                             "LastName",
                                                             addr_line1 as
                                                             "AddressLine1",
                                                             city as "City",
                                                             state as "State")),
                                    xmlelement("Campaign",
                                               xmlattributes(cpgn_cd as
                                                             "CampaignCode",
                                                             promo_cd as
                                                             "PromoCode")),
                                    xmlelement("Response",
                                               xmlattributes(moc as
                                                             "MediaOriginCode",
                                                             to_char(rsp_dt,
                                                                     'YYYY-MM-DD"T"HH24:MI:SS') as
                                                             "ResponseDate")),
                                    (select XMLELEMENT("Survey",
                                                       XMLELEMENT("Answers",
                                                                  XMLAGG(XMLELEMENT("Answer",
                                                                                    xmlattributes(of1.ans_num as
                                                                                                  "AnswerID",
                                                                                                  of1.quest_num as
                                                                                                  "QuestionID")))))
                                       from test_response of1
                                      where of1.rsp_id = a.rsp_id)))), version '1.0" encoding="ISO-8859-1').getClobVal() "xml_row"
  from (select distinct rsp_id,
                        hist_id,
                        cpgn_cd,
                        promo_cd,
                        moc,
                        rsp_dt,
                        first_nm,
                        last_nm,
                        addr_line1,
                        city,
                        state,
                        prod_cd
          from test_response) a
;




Output of above query:


<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nonamespaceSchemaLocation="http://ulcer.epsilon.com/PharmaInteraction/PDP_XML_RMI-RDC.xsd">
  <Interaction ChannelCode="W" ProductCode="P1" ExternalID="100">
    <Individial FirstName="TEST_FIRST1" LastName="TEST_LAST1" AddressLine1="TEST_ADDR_1" City="MILFORD" State="OH"/>
    <Campaign CampaignCode="CPGN1" PromoCode="PROMO1"/>
    <Response MediaOriginCode="MOC1" ResponseDate="2012-12-22T14:32:01"/>
    <Survey>
      <Answers>
        <Answer AnswerID="A1" QuestionID="Q1"/>
        <Answer AnswerID="A2" QuestionID="Q2"/>
        <Answer AnswerID="A3" QuestionID="Q3"/>
      </Answers>
    </Survey>
  </Interaction>
  <Interaction ChannelCode="W" ProductCode="P1" ExternalID="200">
    <Individial FirstName="TEST_FIRST2" LastName="TEST_LAST2" AddressLine1="TEST_ADDR_2" City="EDISON" State="NJ"/>
    <Campaign CampaignCode="CPGN2" PromoCode="PROMO2"/>
    <Response MediaOriginCode="MOC2" ResponseDate="2013-01-02T11:45:00"/>
    <Survey>
      <Answers>
        <Answer AnswerID="A1" QuestionID="Q1"/>
        <Answer AnswerID="A5" QuestionID="Q5"/>
        <Answer AnswerID="A6" QuestionID="Q6"/>
      </Answers>
    </Survey>
  </Interaction>
</Interactions>



Thanks
Sri
Previous Topic: kill oracle database session from javascript
Next Topic: recursive hierachy query to xml
Goto Forum:
  


Current Time: Thu Mar 28 15:42:30 CDT 2024