Select query help

I'm not an expert on XML but this seems to me to be a sorting issue. Sort the records in the correct order and then its simply coverting the fields into a string.

Perhaps something like this will work for you.
This should handle up to 3 levels deep, I think :)
It did for my limited dataset anyhow.

you will need to add extra joins and Srt columns to handle deeper levels

Hope it helps

I'm not an expert on XML but this seems to me to be a sorting issue. Sort the records in the correct order and then its simply coverting the fields into a string.

Perhaps something like this will work for you.

Expand|Select|Wrap|Line Numbers

1. select '<Node Id="' + convert(varchar(6),ID) + '" Name="' + Name +'">'
2. from
3. (select top 100 percent a.ID,a.Name,a.ParentID,a.Level,
4.     case when a.Level=0 then 0 
5.     else case when a.Level=1 then a.id 
6.     else case when a.Level=2 then a.ParentID 
7.     else case when a.Level=3 then b.ParentID 
8.     end end end end as srt,
9.  
10.     case when a.Level<3 then a.ID 
11.     else case when a.Level=3 then a.ParentID 
12.     end end as srt2
13. from  YourTable a
14. left join YourTable b on b.ID=a.ParentID
15. left join YourTable c on c.ID=b.ParentID
16. order by srt,Srt2,a.ID) a
17.  

This should handle up to 3 levels deep, I think :)
It did for my limited dataset anyhow.

you will need to add extra joins and Srt columns to handle deeper levels

Hope it helps

Hi,
Thanks for the reply.

Here I came across with another solution. I am using a function (calling recursively) to get all the child companies in a tree order. The code I am using is

Any suggestions please.

Thanks