Re: Self-Referencing Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David D Webb (spivey_at_nospam.post.com)
Date: 02/01/05 

Date: Mon, 31 Jan 2005 20:25:28 -0500

You'll be ggod to go when SQL2005 comes out with CTE's. Joe Celko has a
book on maintaining "normalized" hierarchies. Its complicated, but pretty
fault tolerant. If you can calculate the level of each node ahead of time
and store it in the Menus table, you can build the XML one level at a time.
Obout has a tree control for ASP with an example that uses this approach.

-Dave

"Aaron Weiker" <aaron@sqlprogrammer.org> wrote in message
news:288203632427962739733910@news.microsoft.com...
> Problem: I want to create a menu tree by looking up the table heirarchy
> from a single table. For instance I have one row that points to a previous
> to identify that it belongs in that "folder". Think of it as a file system
> type approach. Ideally I want to represent this as an XML file as I
> already wrote a XSLT file that parses it and creates a nice tree on a web
> page. For an example check out the webpage at http://stats.weiker.org/. It
> uses this XML File to pull the content from.
> http://stats.weiker.org/links.xml
> Here is the DDL for the table.
>
> CREATE TABLE dbo.Menus
> (
> [Id] int not null identity(1,1),
> [ParentId] int,
> [Name] varchar(128) not null,
> [Order] int not null
> CONSTRAINT DF_Menus_Order DEFAULT(0),
> [Url] varchar(1024),
> [CreateDate] smalldatetime not null
> CONSTRAINT DF_Menus_CreateDate DEFAULT(GETDATE()),
> CONSTRAINT PK_Menus PRIMARY KEY CLUSTERED ([Id]),
> CONSTRAINT UK_Menus UNIQUE ([Name], [ParentId]),
> CONSTRAINT FK_Menus_Menus
> FOREIGN KEY ([ParentId])
> REFERENCES dbo.Menus([Id])
> )
>
> The problem I keep running into creating the heirarchy. For example
>
> Folder 1
> --> Item A
> --> Item B
> --> Item C
> -->Folder 2
> -->--> Item A
> -->--> Item F
> Folder 999
> --> Item Z
> --> Item Y
>
> The only solution I currently see is to look through each item and then
> retrieve the items that belong to that and perform recursively. Whenever I
> see this pattern I immediately see my CPU soaring and I break into a cold
> sweat. Maybe not that bad but you get the picture.
> So have I over normalized into this nice little design that I really want
> to work but in reality it is going to cost too much to do?
>
> Or am I just crazy and should just stick to using a pure XML file
> approach? Right now I'm thinking this might be much simpler, but that
> would require accepting defeat.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>

Relevant Pages

  • Self-Referencing Query
    ... I want to create a menu tree by looking up the table heirarchy from ... identify that it belongs in that "folder". ... The problem I keep running into creating the heirarchy. ... Or am I just crazy and should just stick to using a pure XML file approach? ...
    (microsoft.public.sqlserver.programming)
  • Re: file problem
    ... like tdom, perhaps, to parse and search the xml file, and file rename ... the resulting tree under /tmp/mytest is: ... puts "processing $filename" ... global compareTime destdir errors ...
    (comp.lang.tcl)
  • Re: Question for PapaJohn
    ... add a new folder named that under the Shared folder. ... your own and edit the xml file to change papajohn to alisyn... ... Download the Starter Kit for XP or the Starter Kit for Vista and unzip the ... If you did this while Movie Maker was open, ...
    (microsoft.public.windowsxp.moviemaker)
  • Re: Move csv file from one folder to another when output should be xml file
    ... and stored in in the Out folder as a XML file with all the records from the ... The difference is that the output XML file should be according to an example ... Most of the differences between the flat file schema and the output schema ...
    (microsoft.public.biztalk.general)
  • Re: IMF and location of XML file?
    ... Files\Exchsrvr\bin\MSCFV2" folder. ... IMF Tune - Unleash the Full Intelligent Message Filter Power ... I have seen multiple references that state that the XML file needs to be ... "MSExchange.UceContentFilter.dll" file in the new subfolder need to be ...
    (microsoft.public.windows.server.sbs)