Select query help

[deepuv04]

I have a table called "Company"

Company:
 
Id    Name    ParentId    Level
1    A        NULL        0
2    B        1            1
3    C        1            1
4    D        2            2
5    E        2            2
6    F        3            2
7    G        3            2
 
 
Here I want to return the list of all chaild companies in XML format.
 
The XML format should be like
 
<Node Id="1" Name="A">
    <Node Id="2" Name="B">
        <Node Id="4" Name="D" />
        <Node Id="5" Name="E" />
    </Node>
    <Node Id="3" Name="C">
        <Node Id="6" Name="F">
        <Node Id="7" Name="G">
    </Node>
</Node>
 
 

How can i do this... any help

[Delerna]

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.

select '<Node Id="' + convert(varchar(6),ID) + '" Name="' + Name +'">'
from
(select top 100 percent a.ID,a.Name,a.ParentID,a.Level,
    case when a.Level=0 then 0 
    else case when a.Level=1 then a.id 
    else case when a.Level=2 then a.ParentID 
    else case when a.Level=3 then b.ParentID 
    end end end end as srt,
 
    case when a.Level<3 then a.ID 
    else case when a.Level=3 then a.ParentID 
    end end as srt2
from  YourTable a
left join YourTable b on b.ID=a.ParentID
left join YourTable c on c.ID=b.ParentID
order by srt,Srt2,a.ID) a
 

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

[deepuv04]

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.

select '<Node Id="' + convert(varchar(6),ID) + '" Name="' + Name +'">'
from
(select top 100 percent a.ID,a.Name,a.ParentID,a.Level,
    case when a.Level=0 then 0 
    else case when a.Level=1 then a.id 
    else case when a.Level=2 then a.ParentID 
    else case when a.Level=3 then b.ParentID 
    end end end end as srt,
 
    case when a.Level<3 then a.ID 
    else case when a.Level=3 then a.ParentID 
    end end as srt2
from  YourTable a
left join YourTable b on b.ID=a.ParentID
left join YourTable c on c.ID=b.ParentID
order by srt,Srt2,a.ID) a
 

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

 
create table dbo.company
( Id smallint,
  Name varchar(5),
  ParentId smallint,
  Level smallint
)
insert into dbo.company
select 1, 'A', null, 0 union all
select 2, 'B', 1, 1 union all
select 3, 'C', 1, 1 union all
select 4, 'D', 2, 2 union all
select 5, 'E', 2, 2 union all
select 6, 'F', 3, 2 union all
select 7, 'G', 3, 2
--select * from dbo.company
 
go
 
 
 
alter function dbo.getSubtree( @Id int, @level int)
returns xml
begin
 
 
 
  return
  ( select
      Id as [@Id],
      Name as [@Name],
      case when ParentId is not null
           then dbo.GetSubtree (id, @level + 1)
      end
    from dbo.company
    where parentId = @Id
    order by Id
    for xml path('Node'), type
  )
 
 
 
end
 
 
 
go
 
 
 
select
  Id as [@Id],
  Name as [@Name],
  dbo.GetSubtree(Id, 1)
from dbo.company
where parentId is null
order by Id
for xml path('Node'), type
 
 

Any suggestions please.

Thanks