Select query help

[deepuv04]

I have a table called "Company"

[code=text]
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>

[/code]
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.
[code=sql]
select '<Node Id="' + convert(varchar (6),ID) + '" Name="' + Name +'">'
from
(select top 100 percent a.ID,a.Name,a.P arentID,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
[/code]

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.
[code=sql]
select '<Node Id="' + convert(varchar (6),ID) + '" Name="' + Name +'">'
from
(select top 100 percent a.ID,a.Name,a.P arentID,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
[/code]

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

[code=sql]

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

[/code]

Any suggestions please.

Thanks