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.
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 are1) 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.CheersAndy M." |
|
robvolk
Most Valuable Yak
15732 Posts |
|
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 are1) 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.CheersAndy 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 |
|
|
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. |
|
|
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.HTHJasper Smith |
|
|
|
|
|
|
|