SQL Server Performance

XML style cyclical queries

Discussion in 'General Developer Questions' started by ishmell, Feb 23, 2007.

  1. ishmell New Member

    Hello all,

    I need to do some cyclical queries that would probably work much better in an XML hierarchy than this relational SQL database... but I have no idea how to do it:

    I've got a table T with 3 fields:

    ID uniqueidentifier
    ParentID uniqueidentifier
    Entry varchar(50)

    I've got root records, child records, and subchild records (and possibly so on...):

    ID ParentID Entry
    -----------------------------
    1 null root
    2 1 child
    3 1 child2
    4 2 subchild
    5 4 subchild2
    6 null root2
    7 6 child
    8 6 child2

    Which in XML looks like this:

    <root>
    <child/>
    <subchild/>
    <subchild2/>
    <child2/>
    </root>
    <root2>
    <child>
    <child2>
    </root2>

    How can I do a working query that I assume looks nothing like this:

    select * from T where entry='root' or (entry='root' and ID=ParentID)

    so I can get records like this:

    ID ParentID Entry
    -----------------------------
    1 null root
    2 1 child
    3 1 child2
    4 2 subchild
    5 4 subchild2

    Thanks!

Share This Page