export tree data to xml
-
Morning all,
i am trying to output the hierarchical data of a tree to xml format.
i can query the data from the tables into a friendly format like this:
create table dummy(
id int,
childname nvarchar(max),
parentid int,
parentname nvarchar(max)
)
go
insert into dummy (id, childname, parentid, parentname)
select 2, 'Dirname A', 1, 'C:\' union
select 3, 'Dirname B', 1, 'C:\' union
select 4, 'File 1' , 2, 'Dirname A' union
select 5, 'File 2' , 2, 'Dirname A' union
select 6, 'Dirname C', 2, 'Dirname A' union
select 7, 'File 1' , 6, 'Dirname C'
--C:\
--Dirname A
--File 1
--File 2
--Dirname C
--File 1
--Dirname B
and i always know the root ID from the first record on "table" dummy (generated with a common table expression), in this case it's ID 1
but from here, how to process this for any level of depth ?
-
You'll need a function for this
CREATE FUNCTION dbo.GetHier(@parentid INT)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT childname AS "@name",
dbo.GetHier(id)
FROM dummy
WHERE parentid = @parentid
FOR XML PATH('node'),TYPE)
END
GO
SELECT parentname AS "@name",
dbo.GetHier(parentid)
FROM dummy
WHERE parentid = 1
GROUP BY parentid,parentname
FOR XML PATH('node'),TYPE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hello Mark,
i was browsing the forum for similar questions and indeed found an old thread where you adviced the same.
do you think i can use this solution with a memory table ?
-
This may be possible with table valued parameters, can you post a bit more information on what you have.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
i generate the "data" i need like this
;WITH data AS
(
SELECT descendants.id as id, descendants.name AS childname, ancestors.id as parentid, ancestors.name as parentname FROM TreePaths paths
INNER JOIN Treelabels ancestors ON ancestors.id = paths.ancestor
INNER JOIN Treelabels descendants ON descendants.id = paths.descendant
WHERE paths.length = 1 and paths.active=1
),
recursion AS
(
SELECT c1.id, c1.childname, c1.parentid, c1.parentname, 1 as level
FROM data c1
WHERE c1.parentid NOT IN (SELECT id FROM data c2)
UNION ALL
SELECT t.id, t.childname, t.parentid, t.parentname, h.Level + 1 AS 'Level'
FROM recursion h
INNER JOIN data t ON t.parentid = h.id
)
select * from recursion
-
This should work for you
CREATE TYPE dbo.HierTVP AS TABLE
(
id int,
childname nvarchar(max),
parentid int,
parentname nvarchar(max)
)
GO
CREATE FUNCTION dbo.GetHier(@parentid INT, @HierTVP dbo.HierTVP READONLY)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT childname AS "@name",
dbo.GetHier(id, @HierTVP)
FROM @HierTVP
WHERE parentid = @parentid
FOR XML PATH('node'),TYPE)
END
GO
DECLARE @HierTVP dbo.HierTVP;
WITH data AS
(
SELECT descendants.id as id, descendants.name AS childname, ancestors.id as parentid, ancestors.name as parentname FROM TreePaths paths
INNER JOIN Treelabels ancestors ON ancestors.id = paths.ancestor
INNER JOIN Treelabels descendants ON descendants.id = paths.descendant
WHERE paths.length = 1 and paths.active=1
),
recursion AS
(
SELECT c1.id, c1.childname, c1.parentid, c1.parentname, 1 as level
FROM data c1
WHERE c1.parentid NOT IN (SELECT id FROM data c2)
UNION ALL
SELECT t.id, t.childname, t.parentid, t.parentname, h.Level + 1 AS 'Level'
FROM recursion h
INNER JOIN data t ON t.parentid = h.id
)
INSERT INTO @HierTVP(id,childname,parentid,parentname)
SELECT id,childname,parentid,parentname
FROM recursion;
SELECT parentname AS "@name",
dbo.GetHier(parentid,@HierTVP)
FROM @HierTVP
WHERE parentid = 1
GROUP BY parentid,parentname
FOR XML PATH('node'),TYPE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thank you Mark, perfect !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply