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).
精彩评论