reports 6i grouping question for XML generation

From: mdb777 <mburns_at_sjm.com>
Date: 20 Jul 2005 12:20:01 -0700
Message-ID: <1121887201.202520.276790_at_g47g2000cwa.googlegroups.com>

I have a problem trying to diplay some parent/child groupings in a report. The problem is that it is a single table I am getting the records out of and it holds the relationships by listing each record's first_child and its next_sibling. So it has two foreign keys into itself. I need to use this to build the xml structure.

The table format is like this:
CREATE TABLE DATA_WINDOW
(
  FIRST_CHILD NUMBER,
  NEXT_SIBLING NUMBER,

  NAME           VARCHAR2(200),
  ID             NUMBER                         NOT NULL,

 )

I am trying to create an xml report, so I was trying to do this with only data model (no layout).
The problem is that I can't do a single query to get all the children of a single record. I have to get the first_child then get the query that record to get its sibling and so on. I am trying to do it by using plsql to loop through this and generate temp tables for the diffenent levels. Then I can create queries to get that data which can be used to generate the xml. But this is not working very well. Having problems getting past 1st couple of levels to work correctly.
Sample of code I am using to get siblings: Works well to build first layer of siblings, but after I need to pass(link) the first child for each sibling to another query which will then build the next level siblings. But I am getting confused on how to proceed from here.

---

	SibID 		number;
	ChildID 	number;
	iFirstDW 	number;
	DWID 		number;
	WChild		number;
	WSib		number;

	cursor First is select id, first_child, next_sibling, name
	from data_window
	order by id;

	DW_row		First%rowtype;
	W_row		First%rowtype;

begin
  delete from DW_1st;

  For count in First

	loop
		iFirstDW:= :first_child;   /*Get the first child of the selected
block */
		fetch First into DW_row;

		DWID:= DW_row.id;
		ChildID:= DW_row.first_child;
		SibID:= DW_row.next_sibling;

		if DWID = iFirstDW then
			insert into DW_1st (id, first_child, next_sibling, name) /* Add
first row to table*/
			values (DWID, ChildID, SibID, DW_row.name);
			commit;
			WSib:= SibID;
		end if;

		if DWID=WSib then
			WSib:= SibID;
			insert into DW_1st (id, first_child, next_sibling, name) /* Add
other siblings of 1st rung to DW1 table*/
			values (DWID, ChildID, SibID, dw_row.name);
			commit;
		end if;
	end loop;
*******************************************************

I do not know how many levels there will be or how many siblings each record could have.

Any ideas? I know that it is easy to generate the data I need in a procedural language, but how do I display it in an xml report?

thanks for any help Received on Wed Jul 20 2005 - 21:20:01 CEST