How to generate the XML string from the nested table [message #580061] |
Tue, 19 March 2013 15:03 |
rangan.s
Messages: 75 Registered: February 2008 Location: chennai
|
Member |
|
|
CREATE TYPE TEST_103 AS OBJECT(CHILD_CSE_ID VARCHAR2(25));
CREATE TYPE TEST_104 AS TABLE OF TEST_103;
CREATE TABLE TEST_105 (
CL_ID VARCHAR2(25),
CSE_ID VARCHAR2(14),
EXP_ID VARCHAR2(13),
CS_LST TEST_104
)NESTED TABLE CS_LST STORE AS TEST_104_TAB;
INSERT INTO TEST_105 values ('A','100','E_100',TEST_104(TEST_103('C_100'),TEST_103('C_105')));
INSERT INTO TEST_105 values ('A','200','E_200',TEST_104(TEST_103('C_200'),TEST_103('C_205')));
INSERT INTO TEST_105 values ('A','300','E_300',TEST_104(TEST_103('C_300'),TEST_103('C_305')));
INSERT INTO TEST_105 values ('A','300',NULL,NULL);
INSERT INTO TEST_105 values ('B','400','E_400',TEST_104(TEST_103('C_400'),TEST_103('C_405')));
INSERT INTO TEST_105 values ('B','500','E_500',TEST_104(TEST_103('C_500'),TEST_103('C_505')));
INSERT INTO TEST_105 values ('B','600','E_600',TEST_104(TEST_103('C_600'),TEST_103('C_605'),TEST_103('C_606')));
COMMIT ;
SELECT * FROM TEST_105;
I want an XML output like as follows
<?xml version="1.0" encoding="UTF-8"?>
<Main_case>
<cl_id="A">
<cse_id="100">
<cs_lst>
<case>C_100</case>
<case>C_105</case>
</cs_lst>
</cse_id>
<cse_id="200">
<cs_lst>
<case>C_200</case>
<case>C_205</case>
</cs_lst>
</cse_id>
<cse_id="300">
<cs_lst>
<case>C_300</case>
<case>C_305</case>
</cs_lst>
</cse_id>
</cl_id>
<cl_id="B">
<cse_id="400">
<cs_lst>
<case>C_400</case>
<case>C_405</case>
</cs_lst>
</cse_id>
<cse_id="500">
<cs_lst>
<case>C_500</case>
<case>C_505</case>
</cs_lst>
</cse_id>
<cse_id="300">
<cs_lst>
<case>C_600</case>
<case>C_605</case>
<case>C_606</case>
</cs_lst>
</cse_id>
</cl_id>
</Main_case>
|
|
|
|
|