SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 FOR XML EXPLICIT hierachical data
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/12/2002 :  09:07:19  Show Profile  Visit AskSQLTeam's Homepage
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

USA
15657 Posts

Posted - 07/12/2002 :  09:08:22  Show Profile  Visit robvolk's Homepage
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

United Kingdom
6 Posts

Posted - 07/15/2002 :  05:11:52  Show Profile
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

USA
15657 Posts

Posted - 07/15/2002 :  08:24:52  Show Profile  Visit robvolk's Homepage
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

United Kingdom
846 Posts

Posted - 07/15/2002 :  13:35:20  Show Profile  Visit jasper_smith's Homepage
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000