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 |
|
jbkayne
Posting Yak Master
100 Posts |
 Posted - 2003-03-25 : 12:34:53
|
| Does anyone have an example, or advice of representing an N level heirarcy using FOR XML Explicit? Is this even possible?a sample schema is as follows:CREATE TABLE [dbo].[Dept] ( [DeptID] [int] NOT NULL , [Department] [varchar] (256) NOT NULL , [ParentId] [int] NULL) ON [PRIMARY]GO desired results:<depts> <dept> <department>level 1</department> <dept> <department>level 2</department>// .... n levels </dept> </dept></depts> |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-25 : 20:24:52
|
| You would have to use a self-join in order to model that kind of structure, and you'd need to join the table again for each layer of depth to the hierarchy.You might be able to tweak some tree structures to model the XML the way you want, search SQL Team for "trees" and check out some of the articles.Actually, it would probably be better to output the XML with one level and use some XSL or DOM methods to build the hierarchy properly. You can probably do it in one pass through the regular XML. It will be a lot easier than trying to do it in SQL Server and you won't have to modify the SQL data to accommodate extra levels. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-26 : 15:46:10
|
| What would you do if you hade many branches in this scenario?If you where to go from the top down (how do you know where the top is?)Also would you need to isolate a thread? Or would you use like explorer and expand when requested? A / B C| \ /D EF| /| GH I JIt get's more complicated if you have more than 1 parent per chilc (which would be baaad).Brett8-)Edited by - x002548 on 03/26/2003 15:46:53 |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2003-03-26 : 15:52:24
|
| I'm not sure that n-level FOR XML EXPLICIT is possible without using dynamic SQL. One must explicitly declare each node or attribute as a column in a universal table first, then the FOR XML EXPLICT clause creates the XML stream from that.Now using XML AUTO may work, if you can get the sorting right so the nesting is generated correctly.-- monkey |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2003-03-27 : 12:39:02
|
| Thanks for all the information.Monkeybite, I was able to get advice from a friend of mine that was similar to yours.... He also suggested that I create a universal table first that represents the hierarchy, and then run a second query on the universal table using FOR XML EXPLICIT.Seems like a really innovative way to get this working.I'll let you know if it turns out to work correctly.... This should allow N levels.Edited by - jbkayne on 03/27/2003 12:56:37 |
|
|
|
|
|
|
FOR XML EXPLICIT HEIRARCHY