我想以分层形式生成XML文件 [英] I want generate XML file in a hierarchical form

问题描述

我有一个这样的表(实际上它包含了更多的6000条记录)

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)

插入:

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

我想生成这样的XML文件(简化的树状结构)

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

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

我不想使用递归，因为该表中有60000条记录，因此会大大降低性能.

如果我获得相同格式的输出，我会很高兴，因为我将使用此输出XML发送请求.

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

更重要的是，它本质上将是动态的.

推荐答案

尝试此过程的效率不是很高，因为我正在创建一个临时表来获取结果

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

查询1 :

exec get_path

结果:

| 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.....

这篇关于我想以分层形式生成XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！