By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Select query help

deepuv04
Expert 100+
P: 227
I have a table called "Company"

Expand|Select|Wrap|Line Numbers
  1. Company:
  2.  
  3. Id    Name    ParentId    Level
  4. 1    A        NULL        0
  5. 2    B        1            1
  6. 3    C        1            1
  7. 4    D        2            2
  8. 5    E        2            2
  9. 6    F        3            2
  10. 7    G        3            2
  11.  
  12.  
  13. Here I want to return the list of all chaild companies in XML format.
  14.  
  15. The XML format should be like
  16.  
  17. <Node Id="1" Name="A">
  18.     <Node Id="2" Name="B">
  19.         <Node Id="4" Name="D" />
  20.         <Node Id="5" Name="E" />
  21.     </Node>
  22.     <Node Id="3" Name="C">
  23.         <Node Id="6" Name="F">
  24.         <Node Id="7" Name="G">
  25.     </Node>
  26. </Node>
  27.  
  28.  
How can i do this... any help
Jun 12 '08 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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
Jun 13 '08 #2

deepuv04
Expert 100+
P: 227
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. create table dbo.company
  3. ( Id smallint,
  4.   Name varchar(5),
  5.   ParentId smallint,
  6.   Level smallint
  7. )
  8. insert into dbo.company
  9. select 1, 'A', null, 0 union all
  10. select 2, 'B', 1, 1 union all
  11. select 3, 'C', 1, 1 union all
  12. select 4, 'D', 2, 2 union all
  13. select 5, 'E', 2, 2 union all
  14. select 6, 'F', 3, 2 union all
  15. select 7, 'G', 3, 2
  16. --select * from dbo.company
  17.  
  18. go
  19.  
  20.  
  21.  
  22. alter function dbo.getSubtree( @Id int, @level int)
  23. returns xml
  24. begin
  25.  
  26.  
  27.  
  28.   return
  29.   ( select
  30.       Id as [@Id],
  31.       Name as [@Name],
  32.       case when ParentId is not null
  33.            then dbo.GetSubtree (id, @level + 1)
  34.       end
  35.     from dbo.company
  36.     where parentId = @Id
  37.     order by Id
  38.     for xml path('Node'), type
  39.   )
  40.  
  41.  
  42.  
  43. end
  44.  
  45.  
  46.  
  47. go
  48.  
  49.  
  50.  
  51. select
  52.   Id as [@Id],
  53.   Name as [@Name],
  54.   dbo.GetSubtree(Id, 1)
  55. from dbo.company
  56. where parentId is null
  57. order by Id
  58. for xml path('Node'), type
  59.  
  60.  
Any suggestions please.

Thanks
Jun 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.