Re: Help w/Self-Join Hierarchy Query
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/10/04
- Next message: Paul: "Re: Question on temp table"
- Previous message: Kalen Delaney: "Re: Change default instance -- MSDE 7.0 and 2000"
- In reply to: Pitcairnia: "Re: Help w/Self-Join Hierarchy Query"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Dec 2004 20:34:57 -0500
Pitcairnia wrote:
> Thanks, your example code almost does it, and if I delve into
> EXPLICIT then I may find the answer in there. If the answer lies
> there, then I will have to learn to write an XSD file.
>
> My complaint/gripe/question is that this seemingly common
> functionality is not provided, already encapsulated in these objects
> (UNLESS I missed it).
>
> 1. Common? I think so. My particular case study is nested
> departments within a store.
>
> <store>
> <department id="Sporting Goods">
> <department id="Raquet Sports">
> <department id="Tennis"> />
> <department id="Raquetball" />
> </department>
> <department id="Aquatic Sports" />
> ...
> </deparment>
> </store>
>
> Seems like a fairly common application. If not, consider directories:
>
> <directory name="Documents and Settings">
> <directory name="Administrator">
> <directory name="Cookies" />
> <directory name="Desktop" />
> </directory>
> <directory name="User">
> ...
> </directory>
> </directory>
>
> Then there is the ever popular employee - manager adjacency.
> Adjacency refers to an adjacent column in a table that is a foreign
> key to the same tables primary key, as in:
>
> EmployeeId Name ManagerId
> ________________________________________
> 1 Jim Manager NULL
> 2 Joe LeadMan 1
> 3 Jack Programmer 2
> 4 Young Newguy 2
>
> This is the simplest way to represent such relationships in the
> database; an associative table is unnecessary here. In fact there is
> often good discussions about adjacency model verses nested sets etc
> by Celko and others.
>
> 2 Simple? NO. Something common should be simple. There should be an
> encapsulated way to specify that output in a T-SQL Query. Let me
> make one up.
>
> SELECT * FROM Employee
> FOR XML SELF(ManagerId, EmployeeId)
>
> If that were a command, the node could take the entity name and the
> attributes could take the column names, ommitting ParentId because the
> command knows to relate the Manger Id to the EmployeeId thus nesting
> the XML and implying the ManagerId.
>
> 3. Solution: So what do i do in the meantime? Can anybody with a
> working knowledge of the XML EXPLICIT and of XSD documents assure me
> that if I take the time to learn it, there is a solution in there for
> me at the end. I have two XML books, both from Microsoft Press, and
> a skim of those chapters does not indicate to me that the answer lies
> there. I could just buckle down and read those books, but time is of
> the essence and I feel like I would be mastering a whole new subject
> to tackle that.
>
> Thanks, David
>
See if any of these articles help (I don't think XSD files are
required):
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000534.htm
http://www.eggheadcafe.com/articles/20030804.asp
-- David Gugick Imceda Software www.imceda.com
- Next message: Paul: "Re: Question on temp table"
- Previous message: Kalen Delaney: "Re: Change default instance -- MSDE 7.0 and 2000"
- In reply to: Pitcairnia: "Re: Help w/Self-Join Hierarchy Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
