Generate breadcrumb from database

I have table with data

 id       parent    order   tab       desc
---------开发者_StackOverflow中文版---------------------------------------------------------------
    1        Null   1   False       abcdef
    2       Null    2   False       efgh
    3       1       1   False       sadad
    4       1       2   False       aasd
    5       3       1   True        qwer
    6       3       1   True        asdad
    7       5       1   False       zxzc
    8       5       2   False       okli

This table has data about all pages with sub sections and tab column indicates that it is tab on that page but not a new page

I want to generate xml and generate a breadcrumb using this data, how can i achieve that using this data?

---

For the breadcrumb, you would need to use a recursive CTE like this:

;with Tree as
(
   select CONVERT(varchar(100), id) as Path, id
   from Tbl
   where Tbl.Parent is null

   union all

   select Tree.Path + ' > ' + id as Path, id
   from Tbl

        inner join 
        Tree
        on Tree.id = Tbl.Parent
)

select * from Tree

The breadcrumb here is just the id of each row, but you could change it to whichever column you wanted (and also include whatever other columns you wanted in your result set as well).