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  |
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 #578748 is a reply to message #578747] |
Mon, 04 March 2013 12:39  |
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 #578752 is a reply to message #578749] |
Mon, 04 March 2013 14:26 |
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 #578760 is a reply to message #578752] |
Mon, 04 March 2013 15:14 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 00:59:49 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
