Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 FOR XML EXPLICIT hierachical data

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-12 : 09:07:19
andy writes "I'm using adjacent list model to specify a parent child relationship in my db. I have a table with a relation to itself to define this. Everything works fine, until I want to display this data as XML. All examples I've seen use an iterative approach to creating this hierachy, which either creates a huge incredibly complicated stored procedure, or XSD model which are both limited to a specifice depth. I would like to create a stored procedure that uses FOR XML EXPLICIT to define an infinated hierachy, which can start anywhere in the tree. This would ideally be achieved using recursion, so my question are

1) Is there any way of getting around the fact that recursion is not allowed when using FOR XML EXPLICIT.

2) If there is a way around it how can the universal table columns be dynmacally created with appropriate ids, without using an ernomous eval statement.

I can't figure a way round this problem myself, and have seen many posts of people having a similar problem.

Any help would be greatly appreciated.

Cheers

Andy M."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 09:08:22
This might help:

http://www.sqlteam.com/item.asp?ItemID=8866

I'm not sure if it will simplify the FOR XML results, but it should give you a start.

Go to Top of Page

andymaule
Starting Member

6 Posts

Posted - 2002-07-15 : 05:11:52
quote:

andy writes "I'm using adjacent list model to specify a parent child relationship in my db. I have a table with a relation to itself to define this. Everything works fine, until I want to display this data as XML. All examples I've seen use an iterative approach to creating this hierachy, which either creates a huge incredibly complicated stored procedure, or XSD model which are both limited to a specifice depth. I would like to create a stored procedure that uses FOR XML EXPLICIT to define an infinated hierachy, which can start anywhere in the tree. This would ideally be achieved using recursion, so my question are

1) Is there any way of getting around the fact that recursion is not allowed when using FOR XML EXPLICIT.

2) If there is a way around it how can the universal table columns be dynmacally created with appropriate ids, without using an ernomous eval statement.

I can't figure a way round this problem myself, and have seen many posts of people having a similar problem.

Any help would be greatly appreciated.

Cheers

Andy M."



Thanks for the idea, but I've considered this approach and it doesn't seem to get past the problem. It seems as though it just can't be done in SQL server 2000. I understand there is a way in Oracle... but hey, I'll crack on with it and see if I can't find another solution

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-15 : 08:24:52
quote:
I've considered this approach and it doesn't seem to get past the problem
Can you provide more info as to where the problem is exactly?

How about posting your table structures, some sample data, and the desired XML output you want? Without these it's hard to take a crack at it.

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-15 : 13:35:20
I use XSL to do this. I simply output the hierachy using FOR XML RAW to keep things simple and small and apply the XSL. I use it to populate a folder tree for a reporting application. Since it only ever needs to be updated when a report is added or removed the overhead is acceptable (I use DTS to output the XML, transform with XSL and rinsert the XML tree into a text column). Its really very quick. I do the same with the reports themselves except without the transform. i.e. I keep the sql text and xsl text in the table and the xml is refreshed by a scheduled job - most are daily but some are hourly. This "caching" of the xml stops these reports ( some of which might take 5-10 mins to run) from hitting our production databases and leads to a very quick response through the web app.

HTH
Jasper Smith
Go to Top of Page
   

- Advertisement -