IDObject          TaxID    
   DB1               259

and one hierarchical table called (TAXONOMY) with this columns:

TaxID    TaxIDParent   tax_name         taxlevel
259        256          P.O                3

256        4             A                 2

4           1            B                 1

1           0            C                 0


i get the TaxID from P_TAXONOMY and i have to look for recursively its father (level 0)  in the TAXONOMY table; I'm pretty sure the solution to this involves some type of recursive query but if anyone here can help me out I'd really appreciate it.

i have the next query but only for  3 levels, i need it for n levels (from any leaf)

select stf.tax_name
from TAXONOMY stf,
(select st0.TaxIDParent
from TAXONOMY st0, (select st.TaxIDParent
from P_TAXONOMY spt, TAXONOMY st                                                                  where spt.TaxID = st.TaxID ) as u
where st0.TaxID = u.TaxIDParent ) as cons
where stf.TaxID = cons.TaxIDParent
group by tax_name


Thanx
Diego Bayona.

pd:for example:
the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)

-- Prepare sample data
DECLARE    @Taxonomy TABLE (TaxID INT, TaxIDParent INT, Tax_Name VARCHAR(10), TaxLevel INT)

INSERT    @Taxonomy
SELECT    259, 256, 'P.O', 3 UNION ALL
SELECT    256, 4, 'A', 2 UNION ALL
SELECT    4, 1, 'B', 1 UNION ALL
SELECT    1, 0, 'C', 0

-- Prepare recursion
DECLARE    @StartingNode INT

SET    @StartingNode = 259

;WITH Yak (TaxID, ParentID)
AS (
    SELECT    TaxID,
        TaxIDParent
    FROM    @Taxonomy
    WHERE    TaxID = @StartingNode

    UNION ALL

    SELECT        t.TaxID,
            t.TaxIDParent
    FROM        @Taxonomy AS t
    INNER JOIN    Yak AS y ON y.ParentID = t.TaxID
)

SELECT    TaxID AS RootNode
FROM    Yak
WHERE    ParentID = 0