I Want Generate Xml File In A Hierarchical Form

I have a table like this (Actually it contains more 6000 records) IdIndustry | IndustryCode | IndustryName | ParentId --------------------------------- 1 | IND |

Solution 1:

Try this procedure not much sure about its efficiency as I am creating a temp table to get result

createprocedure get_path asbeginDECLARE@cntINTDECLARE@nINTDECLARE@tmpTableTABLE(id int, 
                indCode varchar(50), 
                indName varchar(100),
                parentId int,
                path varchar(500))

  insert@tmpTableselect [IdIndustry], [IndustryCode], [IndustryName], [ParentId],
          nullfrom tbl

  select@cnt=count(*)  from@tmpTablewhere parentId isnullupdate a set a.path = CONCAT(b.indName,'/',a.indName) from@tmpTable a, @tmpTable b where b.parentid isnulland a.parentid = b.id
  select@n=count(*)  from@tmpTablewhere path isnull
  while (@cnt<@n) beginupdate a set a.path = concat(b.path, '/', b.indName, '/', a.indName) from@tmpTable a, @tmpTable b where b.path isnotnulland a.parentid = b.id
    select@n=count(*) from@tmpTablewhere path isnullendupdate@tmpTableset path = indName where parentid isnullselect*from@tmpTableorderby path
end
go

Query 1:

exec get_path

Results:

| ID | INDCODE |   INDNAME | PARENTID |                                  PATH |
-------------------------------------------------------------------------------
|  3 |     FIN |   Finance |   (null) |                               Finance |
|  4 |    CFIN | Corporate |        3 |                     Finance/Corporate |
|  5 |   CMRKT | Capital M |        4 | Finance/Corporate/Corporate/Capital M |
|  1 |     IND |  Industry |   (null) |                              Industry |
|  2 |   PHARM |  Pharmacy |        1 |                     Industry/Pharmacy |

Hope this helps.....

SQL FIDDLE