How to make a XML from a hierarchical query where some data is to be grouped
Oracle 10.2.0.4
Problem:
I want to convert a hierarchical query to a XML and group common values in a “Bloc” element and specific values in a “Field” element.
Eg (Correct solution):
<Blocs>
<Bloc id="1">
<Field id="1"/>
</Bloc>
<Bloc id="1">
<Field id="2"/>
<Bloc id="2">
<Field id="1"/>
<Field id="2"/>
<Bloc id="3">
<Field id="1"/>
<Field id="2"/>
</Bloc>
<Field id="3"/>
<Field id="4"/>
<Bloc id="4">
<Field id="1"/>
</Bloc>
</Bloc>
</Bloc>
</Blocs>
1) Create tables
create table BLOC
(
bloc_id number not null,name varchar2(30 char)
);alter table BLOCadd constraint BLOC_PK primary key (BLOC_ID);
create table BLOC_FIELD
(
bloc_id number not null,
bloc_field_id