I want generate XML file in a hierarchical form

Question

I have a table like this (Actually it contains more 6000 records)

IdIndustry   |   IndustryCode  |   IndustryName  |  ParentId
---------------------------------
1    |  IND    |   Industry  |   NULL
2    |  PHARM  |   Pharmacy  |   1
3    |  FIN    |   Finance   |   NULL
4    |  CFIN   |   Corporate |   3
5    |  CMRKT  |   Capital M |   4

DDL:

CREATE TABLE [dbo].[tblIndustryCodes](
    [IdIndustry] [int] IDENTITY(1,1) NOT NULL,
    [IndustryCode] [nvarchar](5) NULL,
    [IndustryName] [nvarchar](50) NULL,
    [ParentId] [int] NULL,
CONSTRAINT [PK_tblIndustryCodes] PRIMARY KEY CLUSTERED ([IdIndustry] ASC)

Inserts:

INSERT INTO [tblIndustryCodes]
          ([IndustryCode]
          ,[IndustryName]
          ,[ParentId])
     VALUES
          ('IND','Industry',NULL),
          ('PHARM','Pharmacy',1),
          ('FIN','Finance',NULL),
          ('CFIN','Corporate Finance',3),
          ('CMRKT','Capital Markets',4)

And i want to generate a XML file like this(Simplified tree like structure)

<IND>
      <PHARM>
      </PHARM>
</IND>
<FIN>
      <CFIN>
            <CMRKT>
            </CMRKT>
      </CFIN>
<FIN>

I don't want to use recursion as it would downgrade the performance dramatically as this table has more than 60000 records in table.

I would be glad if i get the output in same format, since i will be using this output XML to send a request.

And more importantly it will be dynamic in nature.

Satterwhite

Ok, i did some extra home work,and some how i came to know that it will be done through "sp_xml_preparedocument" procedure. – Satterwhite

Answer 1

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

create procedure get_path as begin
  DECLARE @cnt INT
  DECLARE @n INT
  DECLARE @tmpTable TABLE(id int, 
                indCode varchar(50), 
                indName varchar(100),
                parentId int,
                path varchar(500))

  insert @tmpTable 
          select [IdIndustry], [IndustryCode], [IndustryName], [ParentId],
          null from tbl

  select @cnt = count(*)  from @tmpTable where parentId is null
  update a set a.path = CONCAT(b.indName,'/',a.indName) from @tmpTable a, @tmpTable b where b.parentid is null and a.parentid = b.id
  select @n = count(*)  from @tmpTable where path is null
  while (@cnt < @n) begin
    update a set a.path = concat(b.path, '/', b.indName, '/', a.indName) from @tmpTable a, @tmpTable b where b.path is not null and a.parentid = b.id
    select @n = count(*) from @tmpTable where path is null
  end
  update @tmpTable set path = indName where parentid is null 
  select * from @tmpTable order by 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

Hathorn

Which language you are using for getting in language? but better first try if there is some problem post it will be glad to help... – Hathorn

What do you mean by max level ? – Hathorn

By level i mean child and parent(generation). – Satterwhite

Sorry don't have that much time right now. Pl post whatever problem you are facing. Will try to solve during later part of day... – Hathorn