Home » Developer & Programmer » JDeveloper, Java & XML » How to generate the XML string from the nested table (Oracle 9i)
How to generate the XML string from the nested table [message #580061] Tue, 19 March 2013 15:03 Go to next message
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>

Re: How to generate the XML string from the nested table [message #580063 is a reply to message #580061] Tue, 19 March 2013 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT use object and nested table in stored objects.
All what you will get is just problems.
Problems to queries.
Problems to maintain code.
Performances problems.
All problems you can imagine you WILL have them.
And this is not the last time you will post such question.
If you are not able to write the code by yourself, change the objects.

Regards
Michel
Re: How to generate the XML string from the nested table [message #580064 is a reply to message #580063] Tue, 19 March 2013 15:20 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, as you don't feedback nor thank people that help you, you do not deserve to helped.

Regards
Michel

[Updated on: Tue, 19 March 2013 15:21]

Report message to a moderator

Previous Topic: how to extract xml data having namespace
Next Topic: inserting current date from xml
Goto Forum:
  


Current Time: Thu Mar 28 11:50:50 CDT 2024