sql - 我想以分层形式生成 XML 文件

Question

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

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

数据链接:

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 文件(简化的树状结构)

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

我不想使用递归，因为它会显着降低性能，因为该表在表中有超过 60000 条记录。

如果我得到相同格式的输出，我会很高兴，因为我将使用这个输出 XML 来发送请求。

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

Answer 1

尝试这个过程不太确定它的效率，因为我正在创建一个临时表来获得结果

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 |

希望这对你有帮助......

SQL FIDDLE