PL/SQL (MOSC)

MOSC Banner

How to make a XML from a hierarchical query where some data is to be grouped

edited Nov 9, 2011 12:00AM in PL/SQL (MOSC) 10 commentsAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center