Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Hierarchical Queries and Self-Referencing objects.

    i have the following table:

    create table A1 as (
    id number(5),
    parent_id number(5),
    some_data varchar2(10)
    );
    /

    and i have a self-referencing type:

    create type A1_T as object(
    id number(5),
    some_data varchar2(10)
    p_ref REF A1_T
    );
    /

    what i essentially want to after this is to create an XML output using dbms_xmlgen that will take a row and traverse the parents (3 levels say). and populate the data(for example)

    <A1_T>
    <ID>5</ID>
    <SOME_DATA>data for 5</SOME_DATA>
    <A1_T>
    <ID>3</ID>
    <SOME_DATA>data for 3</SOME_DATA>
    <A1_T>
    <ID>1</ID>
    <SOME_DATA>data for 1</SOME_DATA>
    </A1_T>
    </A1_T>
    </A1_T>


    any thoughts will be appreciated.

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    For the SQL to populate those records:
    read oracle (or serach Google) documentation about CONNECT BY.
    It's used for tables like yours (Bill Of Material).

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    Hierarchical queries and Objects.

    hi edwin -
    i have used connect-by construct for hierarchical queries. the specific problem
    to populate the self-referencing objects and using the REF to construct into
    a recursive XML is where, i have run out of ideas.,
    thanks
    srini

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •