Thank you so much, Jeff! I was looking for something like xp_DirTree. Will try it out. Thanks.

Thanks but I really want SQL script so it can loop through many directories. I should have said I have more than one directory.

Thank you DesNorton. That works as well.

It's amazing. Thank you so much.

Now I know why I couldn't get the Title node, I missed [1] , which it pulls from another node level based on @doc.nodes('ROOT/*/*/item') level.

Hi Mark,

It looks like I have another challenge. There is another node inside Item. Can you help me to get the title in the Item node? I've updated the sample...

Thank you so much Jmetape. Much appreciated!

Thank you so much Mark Cowne. This is perfect. Much appreciated!

Thank you so much for your help. But I don't have control over the data and can't edit it and there are a lot of them to go through. It...

Yup, it made sense just now about the join clause you mentioned.

well for this example, we can hard-coded. but if we must not hard-code it, I would take off 5...

Please disregard my previous reply.  I actually got it to work now with CTE.

create table #filetable (
FileID varchar(255),
GroupID varchar(255),
Level varchar(1),
Path varchar(255),
Filename varchar(255),
ExpectingResultofPath varchar(max)
);

insert into #filetable (FileID, GroupID,...

here is another sample. I can't get it to work.

create table #filetable (
FileID varchar(255),
GroupID varchar(255),
Level varchar(1),
Path varchar(255),
Filename varchar(255),
ExpectingResultofPath varchar(max)
);

insert into #filetable (FileID, GroupID, Level, Path, Filename, ExpectingResultofPath)
values...

• This reply was modified 4 years, 4 months ago by  shogunSQL.

Brian,

Thank you for your thoughtful respond. You're right about  LAG won't work for this and I think I need a while loop for this because the level can be greater...

Thank you very much Mr. Brian Gale.

Wow. This is a very nice CTE works with Depth to find relationships from previous record with current record.

LAG() sounds good. Would give it...