SQL Server 2005中的层次结构和XML。

Question

我想知道在SQL Server 2005中是否有任何方法可以选择层次结构并返回XML格式的数据？我有一个包含大量数据的数据库（约2000到3000条记录），我现在正在使用SQL Server 2005中的一个函数来检索分层数据并返回XML，但当数据量很大时它似乎不够完美，因为速度太慢。

以下是我的函数： 数据库

ID      Name      Parent       Order

功能

 CREATE FUNCTION [dbo].[GetXMLTree]
(
    @PARENT bigint
)
RETURNS XML
AS
    BEGIN
    RETURN /* value */
(SELECT [ID] AS "@ID",
        [Name] AS "@Name",
        [Parent] AS "@Parent",
        [Order] AS "@Order",
        dbo.GetXMLTree(Parent).query('/xml/item')
    FROM MyDatabaseTable
    WHERE [Parent]=@PARENT
    ORDER BY [Order]
    FOR XML PATH('item'),ROOT('xml'),TYPE)
    END

我希望使用XML层次结构，因为我需要对其进行很多操作 :) 有什么最好的解决方案吗？

- Hieu Nguyen Trung

Answer 1

您可以使用递归公用表达式（CTE）构建层次结构，并循环遍历级别以构建XML。

-- Sample data
create table MyDatabaseTable(ID int,  Name varchar(10), Parent int, [Order] int)
insert into MyDatabaseTable values
(1, 'N1',     null, 1),
(2, 'N1_1',   1   , 1),
(3, 'N1_1_1', 2   , 1),
(4, 'N1_1_2', 2   , 2),
(5, 'N1_2',   1   , 2),
(6, 'N2',     null, 1),
(7, 'N2_1',   6   , 1)

-- set @Root to whatever node should be root
declare @Root int = 1

-- Worktable that holds temp xml data and level
declare @Tree table(ID int, Parent int, [Order] int, [Level] int, XMLCol xml)

-- Recursive cte that builds @tree
;with Tree as 
(
  select 
    M.ID,
    M.Parent,
    M.[Order],
    1 as [Level]
  from MyDatabaseTable as M
  where M.ID = @Root
  union all
  select 
    M.ID,
    M.Parent,
    M.[Order],
    Tree.[Level]+1 as [Level]
  from MyDatabaseTable as M
    inner join Tree
      on Tree.ID = M.Parent  
)
insert into @Tree(ID, Parent, [Order], [Level])
select *
from Tree


declare @Level int
select @Level = max([Level]) from @Tree

-- Loop for each level
while @Level > 0
begin

  update Tree set
    XMLCol = (select
                M.ID as '@ID',
                M.Name as '@Name',
                M.Parent as '@Parent',
                M.[Order] as '@Order',
                (select XMLCol as '*'
                 from @Tree as Tree2
                 where Tree2.Parent = M.ID
                 order by Tree2.[Order]
                 for xml path(''), type)
              from MyDatabaseTable as M
              where M.ID = Tree.ID
              order by M.[Order]
              for xml path('item'))
  from @Tree as Tree             
  where Tree.[Level] = @Level

  set @Level = @Level - 1
end

select XMLCol
from @Tree
where ID = @Root

结果

<item ID="1" Name="N1" Order="1">
  <item ID="2" Name="N1_1" Parent="1" Order="1">
    <item ID="3" Name="N1_1_1" Parent="2" Order="1" />
    <item ID="4" Name="N1_1_2" Parent="2" Order="2" />
  </item>
  <item ID="5" Name="N1_2" Parent="1" Order="2" />
</item>

- Mikael Eriksson

---

使用CTE时不需要循环。 - George Polevoy

@GeorgePolevoy - 如果不知道如何使用CTE构建分层XML，如果您能提供一个答案来展示，我将非常乐意给您+1，并从中学习并删除我的回答。在此之前，我会认为您不理解问题的实际内容。 - Mikael Eriksson

@HieuNguyenTrung，我通过创建一个临时表并在其中仅存储层次结构中所需的记录，然后在一些字段（id和parentId）上创建索引，成功地大幅提高了脚本的性能。最初我的脚本需要几分钟才能运行，但是使用临时表和索引后，运行时间缩短至4秒。 - Ads

Answer 2

您使用XML会有什么好处？当您必须使用XML时，我无法提供完美的解决方案，但是也许您可以调查替代方案？

通过递归CTE（公共表达式），您可以轻松地在单个结果集中获取整个层次结构，并且性能应比使用递归XML构建函数更好。

请查看此CTE：

;WITH Hierarchy AS
(
    SELECT
        ID, [Name], Parent, [Order], 1 AS 'Level'
    FROM
        dbo.YourDatabaseTable
    WHERE
        Parent IS NULL

    UNION ALL

    SELECT
        t.ID, t.[Name], t.Parent, t.[Order], Level + 1 AS 'Level'
    FROM
        dbo.YourDatabaseTable t
    INNER JOIN  
        Hierarchy h ON t.Parent = h.ID
)
SELECT *
FROM Hierarchy
ORDER BY [Level], [Order]

这将为您提供单个结果集，其中返回所有行，按级别排序（根级别为1，每个下层级别增加1），以及它们的[Order]列。

这对您来说是否是一种替代方案？它的性能是否更好？

- marc_s

---

有没有办法进行订购？我不能像您那样订购，那不正确 :( - Hieu Nguyen Trung

请在你的外部SELECT语句上指定一个ORDER BY，以便你能够正确地进行排序。 @chrno love - marc_s

我想像 XML 的后代一样对它进行排序。 - Hieu Nguyen Trung

Answer 3

我知道我的回答有点晚了，但它可能会帮助其他正在寻找解决此问题的不幸人士。我在使用XML时遇到了类似的性能问题：hierarchyid。

对于我来说，最简单的解决方案实际上是在选择XML列之前调用hierarchyid值上的ToString()。在某些情况下，这加快了我的查询速度十倍！

以下是展示问题的代码片段。

create table #X (id hierarchyid primary key, n int)

-- Generate 1000 random items
declare @n int = 1000
while @n > 0 begin

    declare @parentID hierarchyID = null, @leftID hierarchyID = null, @rightID hierarchyID = null
    select @parentID = id from #X order by newid()
    if @parentID is not null select @leftID = id from #X where id.GetAncestor(1) = @parentID order by newid()
    if @leftID is not null select @rightID = min(id) from #X where id.GetAncestor(1) = @parentID and id > @leftID

    if @parentID is null set @parentID = '/'

    declare @id hierarchyid = @parentID.GetDescendant(@leftID, @rightID)
    insert #X (id, n) values (@id, @n)

    set @n -= 1 
end

-- select as XML using ToString() manually
select id.ToString() id, n from #X for xml path ('item'), root ('items')

-- select as XML without ToString() - about 10 times slower with SQL Server 2012
select id, n from #X for xml path ('item'), root ('items')

drop table #X

- Lee Houghton