I was having a discussion on a different thread and this seems to work. I have to try this on real data, but it works here...

with cte as (

select [owner]...

Right, it's not well structured. It is not really designed for SQL to spin through - the application code that sits on top of it sorts it out. However, I...

Here is some sample code:

if object_id('#recursive_example','U') is not null

drop table #recursive_example

create table #recursive_example (

owner varchar(10),

owner_type varchar(10),

item_type varchar(10),

item_name varchar(10)

)

insert into #recursive_example values ('REC1','RECORD','FIELD','FLD1')

insert into #recursive_example values ('REC1','RECORD','FIELD','FLD2')

insert into #recursive_example values...