Hi,
Need your help to generate the parent and child relationships in the XML using the PL/SQL block on multiple levels. I do use Oracle 11g. The provided data is very less but actually, it has a large volume may be more than 2000 for each ID and the total ID will have around 100K which is difficult to store in the VARCHAR2 so planning to use the CLOB to store it. Also, I need to update the value if I received any updates. The expected output is attached in the pdf and it's in an XML format. This
Sorry I am not able to describe my expectation properly. The parent and child relation have many data points and can be added many more.
create table xml_STAGE
(
id NUMBER(9) not null,
rpt_date DATE not null,
type_id NUMBER(9) not null,
parent_group_id NUMBER(4),
group_id NUMBER(4),
group_name VARCHAR2(200),
name VARCHAR2(400),
code VARCHAR2(9),
value_text VARCHAR2(4000),
value_caption_id NUMBER(9),
data_id NUMBER(3),
data_type VARCHAR2(50)
);
insert into xml_stage values(111,'1-Jun-22',59810,220,230,'Group Name 1','Data Name 1',30000,'5/1/2022',0,40,'Date');
insert into xml_stage values(111,'1-Jun-22',59811,220,230,'Group Name 1','Data Name 2',30010,'4/1/2022',0,40,'Date');
insert into xml_;stage values(111,'1-Jun-22',60047,230,241,'Group Name 2','Data Name 3',32080,'1/3/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',60049,230,241,'Group Name 2','Data Name 4',32100,'1/0/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59956,230,253,'Group Name 3','Data Name 5',30980,'1/3/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59957,230,253,'Group Name 3','Data Name 6',30990,'Yes',398,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59975,241,242,'Group Name 4','Data Name 7',32400,'1/0/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59978,241,242,'Group Name 4','Data Name 8',32420,'1/0/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59963,241,243,'Group Name 5','Data Name 9',32280,'1/3/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59762,241,245,'Group Name 6','Data Name 10',31290,'1/0/1900',0,20,'Value');
insert into xml_stage values(111,'1-Jun-22',59686,220,226,'Group Name 7','Data Name 11',10000,'Test002',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59687,220,226,'Group Name 7','Data Name 12',10010,'Test003',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59688,220,226,'Group Name 7','Data Name 13',10020,'Test004',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59692,226,227,'Group Name 8','Data Name 14',11010,'Test001',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59695,226,227,'Group Name 8','Data Name 15',11040,'A',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59696,226,228,'Group Name 9','Data Name 16',12000,'D',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59717,226,229,'Group Name 10','Data Name 17',13000,'tset00154',0,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59676,220,225,'Group Name 11','Data Name 18',10,'V1',60261,10,'Text');
insert into xml_stage values(111,'1-Jun-22',59685,220,225,'Group Name 11','Data Name 19',100,'Yes',398,10,'Text');
select * from xml_stage;
Expected output - XML file (Unable to attach the XML file
SampleXMLFile.pdf (28.15 KB)
<ABC>
<Report ReportDate="2022-06-01">
<Group Id="230" Type="Group Name 1">
<Element Id="1059810" Code="30000" Type="Data Name 1">
<Date>2022-05-01</Date>
</Element>
<Element Id="1059811" Code="30010" Type="Data Name 2">
<Date>2022-04-01</Date>
</Element>
<Group Id="241" Type="Group Name 2">
<Element Id="1060047" Code="32080" Type="Data Name 3">
<Value>3.6</Value>
</Element>
<Element Id="1060049" Code="32100" Type="Data Name 4">
<Value>0.03</Value>
</Element>
<Group Id="242" Type="Group Name 4">
<Element Id="1059975" Code="32400" Type="Data Name 7">
<Value>0.03</Value>
</Element>
<Element Id="1059978" Code="32420" Type="Data Name 8">
<Value>0.03</Value>
</Element>
</Group>
<Group Id="243" Type="Group Name 5">
<Element Id="1059963" Code="32280" Type="Data Name 9">
<Value>3.6</Value>
</Element>
</Group>
<Group Id="245" Type="Group Name 6">
<Element Id="1059762" Code="31290" Type="Data Name 10">
<Value>0.15</Value>
</Element>
</Group>
</Group>
<Group Id="253" Type="Group Name 3">
<Element Id="1059956" Code="30980" Type="Data Name 5">
<Value>3.6</Value>
</Element>
<Element Id="1059957" Code="30990" Type="Data Name 6">
<Text Id="1000398">Yes</Text>
</Element>
</Group>
</Group>
<Group Id="226" Type="Group Name 7">
<Element Id="1059686" Code="10000" Type="Data Name 11">
<Text>Test002</Text>
</Element>
<Element Id="1059687" Code="10010" Type="Data Name 12">
<Text>Test003</Text>
</Element>
<Element Id="1059688" Code="10020" Type="Data Name 13">
<Text>Test004</Text>
</Element>
<Group Id="227" Type="Group Name 8">
<Element Id="1059692" Code="11010" Type="Data Name 14">
<Text>Test001</Text>
</Element>
<Element Id="1059695" Code="11040" Type="Data Name 15">
<Text>A</Text>
</Element>
</Group>
<Group Id="228" Type="Group Name 9">
<Element Id="1059696" Code="12000" Type="Data Name 16">
<Text>D</Text>
</Element>
</Group>
<Group Id="229" Type="Group Name 10">
<Element Id="1059717" Code="13000" Type="Data Name 17">
<Text>tset001154</Text>
</Element>
</Group>
</Group>
<Group Id="225" Type="Group Name 11">
<Element Id="1059676" Code="10" Type="Data Name 18">
<Text Id="1060261">V1</Text>
</Element>
<Element Id="1059685" Code="100" Type="Data Name 19">
<Text Id="1000398">Yes</Text>
</Element>
</Group>
</Report>
</ABC>
Thanks in advance for your valuable help.