Home » Developer & Programmer » JDeveloper, Java & XML » Struggling with XML
Struggling with XML [message #165461] Thu, 30 March 2006 06:14 Go to next message
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


Re: Struggling with XML [message #166507 is a reply to message #165461] Thu, 06 April 2006 13:30 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

See http://www.oratechinfo.co.uk/sqlxml.html#xmlattributes

Rgds
Re: Struggling with XML [message #196251 is a reply to message #166507] Wed, 04 October 2006 11:54 Go to previous message
stevebooth
Messages: 14
Registered: June 2006
Location: Pewaukee, WI
Junior Member
I know it's not nice to shout, but "THANK YOU" for that reference. It's extremely helpful!
Previous Topic: XML Data collection
Next Topic: ORA-22275: invalid LOB locator specified
Goto Forum:
  


Current Time: Sun Dec 04 10:37:07 CST 2016

Total time taken to generate the page: 0.24514 seconds