OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » Struggling with XML

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Struggling with XML [message #165461]

Thu, 30 March 2006 06:14

Aju
Messages: 94
Registered: October 2004

Member

CREATE TABLE CHAMP_COUNTRY_VW
(
NAME VARCHAR2(81 BYTE),
TITLE VARCHAR2(60 BYTE),
EMAIL VARCHAR2(200 BYTE),
LOCATION VARCHAR2(124 BYTE),
PHONE_NO VARCHAR2(100 BYTE),
COUNTRYNAME VARCHAR2(40 BYTE),
COUNTRYCODE VARCHAR2(10 BYTE)
)
/
INSERT INTO CHAMP_COUNTRY_VW ( NAME, TITLE, EMAIL, LOCATION, PHONE_NO, COUNTRYNAME,
COUNTRYCODE ) VALUES (
'x', 'CD', 't@tahoo.com', NULL, NULL, 'Republic of India', 'IN');
INSERT INTO CHAMP_COUNTRY_VW ( NAME, TITLE, EMAIL, LOCATION, PHONE_NO, COUNTRYNAME,
COUNTRYCODE ) VALUES (
'y', 'c', 'ylogabiraman@xxx.org', 'Chennai, India', '91-44-5323-4241', 'Republic of India'
, 'IN');
INSERT INTO CHAMP_COUNTRY_VW ( NAME, TITLE, EMAIL, LOCATION, PHONE_NO, COUNTRYNAME,
COUNTRYCODE ) VALUES (
'z', 'cd', 'bmanickavasagam@xxx.org', 'Chennai, India', '91-4-5523-4111', 'Republic of India'
, 'IN');
INSERT INTO CHAMP_COUNTRY_VW ( NAME, TITLE, EMAIL, LOCATION, PHONE_NO, COUNTRYNAME,
COUNTRYCODE ) VALUES (
'swdev_coc_in', NULL, 'swdev_coc_in@ogburn.net', NULL, NULL, 'Republic of India', 'IN');
COMMIT;
/

CREATE TABLE CC_CDS_COUNTRY_VW(
REGIONCODE VARCHAR2(2 BYTE),
COUNTRYCODE VARCHAR2(3 BYTE),
COUNTRYSHORTNAME VARCHAR2(60 BYTE),
COUNTRYNAME VARCHAR2(40 BYTE),
COUNTRYABBRNAME VARCHAR2(10 BYTE),
COUNTRYREALFLAG VARCHAR2(1 BYTE),
COUNTRYSORTNAME VARCHAR2(15 BYTE)
)
/
INSERT INTO CC_CDS_COUNTRY_VW CCCV ( REGIONCODE, COUNTRYCODE, COUNTRYSHORTNAME, COUNTRYNAME, COUNTRYABBRNAME,
COUNTRYREALFLAG, COUNTRYSORTNAME ) VALUES (
'4', 'IN', 'India', 'Republic of India', NULL, 'Y', 'India');
COMMIT;

From the above structure I am generating the below XML with the PLSQL code as below.

DECLARE
X XMlTYPE;
BEGIN
SELECT
xmlelement("Country",
xmlforest (CountryName, CountryCode),
(select xmlagg(xmlelement( "Champion",
xmlforest (NAME,EMAIL,LOCATION, PHONE_NO )
)
)
from CHAMP_COUNTRY_VW CCV WHERE COUNTRYCODE = CCCV.COUNTRYCODE
)
) sss INTO X
FROM CC_CDS_COUNTRY_VW CCCV
WHERE COUNTRYCODE = 'IN' ;
DELETE FROM AJENDRA_TEST;
INSERT INTO AJENDRA_TEST VALUES(X.GetSTRINGVAL);
COMMIT;
END;

The generated XML is

<Country><COUNTRYNAME>Republic of India</COUNTRYNAME><COUNTRYCODE>IN</COUNTRYCODE><Champion><NAME>Thanige_CD</NAME><EMAIL>t@tahoo.com</EMAIL></Champion><Champion><NAME>Yeshwanth Logabiraman</NAME><EMAIL>ylogabiraman@worldbank.org</EMAIL><LOCATION>Chennai, India</LOCATION><PHONE_NO>91-44-5323-4241</PHONE_NO></Champion><Champion><NAME>Balasubramanian Manickavasagam</NAME><EMAIL>bmanickavasagam@worldbank.org</EMAIL><LOCATION>Chennai, India</LOCATION><PHONE_NO>91-4-5523-4111</PHONE_NO></Champion><Champion><NAME>swdev_coc_in</NAME><EMAIL>swdev_coc_in@ogburn.net</EMAIL></Champion></Country>

But I need to generate the XML as below

<CCCHAMPIONS>
<COUNTRY NAME="BENGLADESH" CODE="BD"> <CHAMPIONS>
<CHAMPION>
<NAME>vvvvv</NAME>
<TITLE>Senior Mgr</TITLE>
<EMAIL>Zsattar@xxx.org</EMAIL>
<LOCATION>erteteteLOCATION>
<PHONE>023432124</PHONE>
</CHAMPION>
</CHAMPIONS>
</COUNTRY>
</CCCHAMPIONS>

I understand XMLattribute can help on this but tried and failed. Request someone to get me the syntax to generate the XML as above

Thanks

Report message to a moderator