Hierarchy in SQL server 2005 with XML

问题:

I wonder is there anyway to select hierarchy in SQL server 2005 and return xml format? I have a database with a lot of data (about 2000 to 3000 records), and i am now using a function in SQL server 2005 to retrieve the data in hierarchy and return an XML but it seems not perfect because it's too slow when there is a lot of data

Here is my function

Database

ID      Name      Parent       Order

Function

 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

I would like to use XML in hierarchy because with me there's alot of thing to do with it :) Any best solutions plzzzzz

回答1:

You can use a recursive CTE to build the hierarchy and loop over levels to build the 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

Result

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

回答2:

What benefit do you expect from using XML? I don't have a perfect solution for the case when you need XML by all means - but maybe you could also investigate alternatives??

With a recursive CTE (Common Table Expression), you could easily get your entire hierarchy in a single result set, and performance should be noticeably better than doing a recursive XML building function.

Check this CTE out:

;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]

This gives you a single result set, where all rows are returned, ordered by level (1 for the root level, increasing 1 for each down level) and their [Order] column.

Could that be an alternative for you? Does it perform better??

回答3:

I realise this answer is a bit late, but it might help some other unlucky person who is searching for answers to this problem. I have had similar performance problems using hierarchyid with XML:

It turned out for me that the simplest solution was actually just to call ToString() on the hierarchyid values before selecting as an XML column. In some cases this sped up my queries by a factor of ten!

Here's a snippet that exhibits the problem.

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