具有XML的SQL Server 2005中的层次结构 [英] Hierarchy in SQL server 2005 with XML
问题描述
我想知道是否仍然可以在SQL Server 2005中选择层次结构并返回xml格式?
我有一个包含大量数据(大约2000到3000条记录)的数据库,现在我正在SQL Server 2005中使用一个函数来检索层次结构中的数据并返回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
这是我的功能
数据库
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
我会ike在层次结构中使用XML,因为与我有很多关系:)
任何最佳解决方案plzzzzz
I would like to use XML in hierarchy because with me there's alot of thing to do with it :) Any best solutions plzzzzz
推荐答案
您可以使用递归CTE构建层次结构,并在各个级别之间循环以构建XML。
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
结果
<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>
这篇关于具有XML的SQL Server 2005中的层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!