How to transfer hierarchical output from stored procedure into well-formed xml.

Question

Post reply

How to transfer hierarchical output from stored procedure into well-formed xml.

H-122523

Hall of Fame

Points: 3667
More actions
August 20, 2007 at 5:54 am

#98488

Hello all,
Has anybody found a solution for the following problem:
I have a table with a parent-child relationship (column ParentID, column ChildID)
I have a recursive procedure that prints the list parent-child like a tree.
I need to show this list in a webpage.
Can this be done from a stored procedure?
tia,
Hans

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

No. The showing of the data has to be done in your ASP page, by calling the SP.

Or, you can create an HTML page from your SP, but why would you?

N 56°04'39.16"
E 12°55'05.25"

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Search hierarchies in SQL Server, but why bother.

Then get the results you need and you'd need to display the tree list in your ASP page. The SQL query would have nothing to do with the display.

H-122523 Hall of Fame Points: 3667 More actions · Answer 3

Yes,

executing the stored procedure from asp and taking the resultset is the way to go.

But is there any easy way to preserve the order in which the stored procedure finds and displays the results? In a recursive procedure I have not found a way to keep a counter that I could use as a value to use for sorting the results.

In summary, my problems boils down to this:

- output from the recursive procedure:

Parent1

Child1

Child2

Child3

Child4

I can have this printed from within the stored procedure, and then the hierarchy is fine.

When I store the results in a temporary table, I can only keep the correct order when I do not define any primary key.

So I have a solution here, but I wonder if there is an easier way to do this.

thanks for the help so far.

Hans

H-122523 Hall of Fame Points: 3667 More actions · Answer 4

I saw that the layout of the output list was not preserved; what I mean is:

-Parent1

---Child1

------Child2

---Child3

------Child4

Hans

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

If you post your table DDL, maybe we can find a new way to return the records you want.

Now that you have CTE's available, it should be easy.

N 56°04'39.16"
E 12°55'05.25"

H-122523 Hall of Fame Points: 3667 More actions · Answer 6

Hello Peter,

Here is the create table statement:

CREATE TABLE [dbo].[tblFamily](

[ChildID] [int] IDENTITY(1,1) NOT NULL

,[ParentID] [int] NOT NULL CONSTRAINT [DF_tblFamily_ParentID] DEFAULT ((0))

,[Name] [varchar](255) NOT NULL

,CONSTRAINT [PK_tblFamiliy] PRIMARY KEY CLUSTERED ([ChildID] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

regards,

Hans

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

You could produce XML in a stored procedure, and then just use XSLT to make it nice. Of course you need to consider the supporting browsers, whether you want some other content, and what kind of other content you want on the page, but this method would allow some extra flexibility, such as traversing the tree, etc. Just an idea

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software