export tree data to xml
-
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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply