| XML generation [message #164850] |
Mon, 27 March 2006 01:52  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Hi
I have a view which gets data by joining few tables
CREATE OR REPLACE VIEW CHAMP_COUNTRY_VW
(NAME, TITLE, EMAIL, LOCATION, PHONE_NO,
COUNTRYNAME, COUNTRYCODE)
AS
SELECT NAME, TITLE, EMAIL, LOCATION, PHONE_NO, CCCV.COUNTRYNAME, BRAX.COUNTRYCODE
FROM PEOPLE_TABLE_VW PTV, (SELECT UPI, ASSOCIATED_OBJECT COUNTRYCODE FROM B2B_ROLE_ASSOCIATION_X BRAX
WHERE BRAX.ROLE_PK IN
(SELECT ROLE_PK FROM B2B_ROLE WHERE ROLE_NAME = 'COC' ) ) BRAX,
CC_CDS_COUNTRY_VW CCCV
WHERE PTV.UPI = BRAX.UPI /*AND BRAX.COUNTRYCODE = 'IN' /*Parameter*/
AND BRAX.COUNTRYCODE = CCCV.COUNTRYCODE;
I have got another table CC_CDS_COUNTRY_VW CCCV which has got the following information
REGIONCODE, COUNTRYCODE, COUNTRYSHORTNAME, COUNTRYNAME, COUNTRYABBRNAME,
COUNTRYREALFLAG, COUNTRYSORTNAME)
Now I need to generate an XML whose structure is as below
==================================================
<CCCHAMPIONS>
<COUNTRY NAME="BENGLADESH" CODE="BD">
<CHAMPIONS>
<CHAMPION>
<NAME>Zaidi Sattar</NAME>
<TITLE>Senior Economist</TITLE>
<EMAIL>Zsattar@xxx.org</EMAIL>
<LOCATION>Dhaka</LOCATION>
<PHONE>0234234324</PHONE>
</CHAMPION>
<CHAMPION>
<NAME>XXX</NAME>
<TITLE>CCCCCC</TITLE>
<EMAIL>DDDDDDD@ss.com</EMAIL>
<LOCATION>Dhaka)</LOCATION>
<PHONE>0234234324</PHONE>
</CHAMPION>
</CHAMPIONS>
</COUNTRY>
</CCCHAMPIONS>
I have used the following script to generate the XML but there should be some way out to make it less complicated.
CREATE OR REPLACE TYPE CHAMPION AS OBJECT
(
NAME VARCHAR2(81),
TITLE VARCHAr2(60),
EMAIL VARCHAR2(200),
LOCATION VARCHAR2(124),
PHONE VARCHAR2(100)
)
/
CREATE TYPE CHAMPION_LST AS TABLE OF CHAMPION ;
CREATE OR REPLACE TYPE COUNTRY_CHAMP as OBJECT
("COUNTRYNAME" VARCHAR2(1000),
"COUNTRYCODE" VARCHAR2(1000),
CHAMPIONS CHAMPION_LST);
CREATE OR REPLACE VIEW CHAMP_COUNTRY_XMLVW
(COUNTRYCODE, CHAMPLST)
AS
SELECT COUNTRYCODE, SYS_XMLGEN(COUNTRY_CHAMP
( COUNTRYNAME, COUNTRYCODE,
CAST (MULTISET
(SELECT NAME,TITLE,EMAIL,LOCATION, PHONE_NO FROM Champ_Country_Vw
WHERE COUNTRYCODE = CCCV.COUNTRYCODE)
AS CHAMPION_LST
)
) , xmlformat.createformat('COUNTRY')
) AS "ChampList"
FROM CC_CDS_COUNTRY_VW CCCV;
Function GetCountryChamp(Code_f_in IN VARCHAR2 ) RETURN XMLTYPE IS
Return_XML XMLType;
BEGIN
SELECT CHAMPLST INTO Return_XML
FROM CHAMP_COUNTRY_XMLVW WHERE COUNTRYCODE = Code_f_in;
RETURN Return_XML ;
END GetCountryChamp;
Also I need to generate an XML whose structure is as below which I am unable to find a solution.
<CCCHAMPIONS>
<REGION CODE='4' NAME='SOUTH ASIA'>
<COUNTRY NAME="MALDIVES" CODE="MV">
<CHAMPIONS>
<CHAMPION>
<NAME>ffffffffff</NAME>
<TITLE>Senior Executive Assistant</TITLE>
<EMAIL>Mwossene@xxx.org</EMAIL>
<LOCATION>Washington DC (yyy)</LOCATION>
<PHONE>044-234234324</PHONE>
</CHAMPION>
<CHAMPION>
<NAME>Savinay Grover</NAME>
<TITLE>Finance Analyst</TITLE>
<EMAIL>sgrover@xxx.org</EMAIL>
<LOCATION>Washington DC (yyy)</LOCATION>
<PHONE>2234311111</PHONE>
</CHAMPION>
</CHAMPIONS>
</COUNTRY>
<COUNTRY NAME="BENGLADESH" CODE="BD">
<CHAMPIONS>
<CHAMPION>
<NAME>tttttttt</NAME>
<TITLE>Senior Economist</TITLE>
<EMAIL>Zsattar@xxx.org</EMAIL>
<LOCATION>Dhaka, Bangladesh (yyy)</LOCATION>
<PHONE>0234234324</PHONE>
</CHAMPION>
</CHAMPIONS>
</COUNTRY>
</REGION>
</CCCHAMPIONS>
</CCCHAMPIONS>
Thanks
Ajendra
|
|
|
|
| Re: XML generation [message #165079 is a reply to message #164850] |
Tue, 28 March 2006 04:48  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
But why the topic is moved to JDeveloper, Java & XML. I need this in Oracle PLSQL only and in fact I had had ceratin solution in PLSQL only and was trying for an improvement.
Moving to PLSQL could have been made sense too.
Can someone please get some input on this.
Thanks
Ajendra
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
