[SQL2005] Recursieve data in XML gooien?

[_Thanatos_]

Ik ben es aan de klooi gegaan met SQL2005 en die common table expressions zijn wel fijn voor het werken met recursieve data. Ze zijn vast ergens anders ook handig voor, maar daar gaat het nu ff niet om. Wat ik eigenlijk wil, is een treeview weergeven en de bron daarvoor moet een XML documentje zijn.

Dus ik dacht, kom, laat ik XML uit de database gaan trekken. SQL2005 kan recursieve data uitpoepen en kan XML uitpoepen, dus een combinatie van beide kan ook vast wel. Ik kwam daardoor uit op de volgende query:

SQL:

WITH ArticleTree(ID, Title, [Level], [Order]) AS (
   SELECT ID, Title, 0, CAST(ID AS varchar(max))
   FROM Articles
   WHERE ParentID IS NULL 
   UNION ALL
   SELECT a.ID, a.Title, [Level] + 1, CAST([Order] + ' | ' + CAST(a.ID AS varchar(10)) AS varchar(max))
   FROM Articles a
   INNER JOIN ArticleTree t ON a.ParentID=t.ID
)
SELECT *
FROM ArticleTree
ORDER BY [Order]

Ik krijg als test de volgende output:

ID	Title	Level	Order
1	test 1	0	1
2	test 1.1	1	1 | 2
3	test 2	0	3
4	test 2.1	1	3 | 4

Dus ik dacht, cool, dat is mijn boomstructuur. Een "FOR XML AUTO, ELEMENTS" eronder plakken en klaar is kees. Maar die vlieger ging helaas niet op. Hij plakt de records dan onder elkaar, alsof het een sequentiele structuur is...

Dus hoe krijg ik hier nou hierarchische XML uit? XML die er ongeveer zo uit mag zien:

code:

<Article ID="1" Title="test 1">
  <Article ID="2" Title="test 1.1"/>
</Article>
<Article ID="3" Title="test 2">
  <Article ID="4" Title="test 2.1"/>
</Article>

Iemand?

[ Voor 6% gewijzigd door _Thanatos_ op 24-04-2006 20:45 ]

[_Thanatos_]

Ik heb een oplossing gevonden!! Helaas met 1 minpuntje, de maximale recursie-diepte is 32. Dus als de recursie-diepte verder moet gaan, is mijn gevonden oplossing niet bruikbaar, of moet het in stappen uitgevoerd worden.

Anyhow, mijn oplossing is gebaseerd op het feit dat een FOR XML clause tegenwoordig genest mag worden. Ik zat dus eerst te denken aan zoiets:

SQL:

SELECT a1.ID, a1.ParentID, a1.Title, (
   SELECT a2.ID, a2.ParentID, a2.Title
   FROM Articles a2
   WHERE a2.ParentID=a1.ID
   FOR XML AUTO, TYPE
)
FROM Articles a1
WHERE ParentID IS NULL
FOR XML AUTO, TYPE

Dat werkt (hoewel de geretourneerde xml niet erg fraai is qua naamgeving), maar heeft een nadeeltje: de recursie gaat hier maar 1 niveau diep, dus diepere niveaus moeten hardcoded worden. Dat wilde ik natuurlijk niet en ging ik dus op zoek naar iets beters.

Gek genoeg kwam ik uit op een scalar valued function. Je zou het niet verwachten, maar omdat SQL2005 een xml-type heeft, kun je dat als scalar value returnen in een functie. In principe zou ik dus een functie willen die precies de binnenste query in bovenstaande SQL returnt als xml. En zelf ook weer een binnenste query heeft waarin ie zichzelf aanroept... En waarempel, dat kan!

SQL:

CREATE FUNCTION articles_getxml_byparent (@ParentID int, @Level int)
RETURNS xml
AS
BEGIN
DECLARE @xml xml
IF @Level >= 32
   SET @xml = (
      SELECT ID, ParentID, Title
      FROM Articles AS Article
      WHERE ParentID=@ParentID OR (@ParentID IS NULL AND ParentID IS NULL)
      FOR XML AUTO, TYPE, ROOT('Articles')
   )
ELSE
   SET @xml = (
      SELECT ID, ParentID, Title, (SELECT dbo.articles_getxml_byparent(ID, @Level + 1))
      FROM Articles AS Article
      WHERE ParentID=@ParentID OR (@ParentID IS NULL AND ParentID IS NULL)
      FOR XML AUTO, TYPE, ROOT('Articles')
   )
RETURN @xml
END

Nu zou je denken, "ja dááárom is de recursie-diepte maximaal 32!" Inderdaad, daarom, maar bedenk wel dat als je probeert om dieper te gaan () dat je dan een SQL exception krijgt, omdat de recursie-diepte voor een functie keihard maximaal 32 is. Vandaar.

Maargoed, de aanroep van bovenstaande functie is eigenlijk te simpel voor woorden:

SQL:

SELECT dbo.articles_getxml_byparent(NULL, 0)

Solved

[ Voor 2% gewijzigd door _Thanatos_ op 24-04-2006 19:44 . Reden: Geen EXEC, maar SELECT om de functie aan te roepen ]

[joopst]

Stoere sql man ! ik vind het een gave functie.

Er kunnen echter nog wat situaties ontstaan die je moet zien te voorkomen, of waar je mee om moet gaan in je sql.
bijv:
Hoe gaat het met circular references ?
Hoe gaat het met nodes die niet gekoppeld zijn aan een ancestor met (parent)id 0 ?

[_Thanatos_]

Dat zijn inderdaad twee dingen waarmee geen rekening wordt gehouden, en het lijkt me sowieso ook vrij lastig. Je zou misschien een lijstje met ID's kunnen bijhouden, maar ik denk niet dat het daar efficienter op wordt.

Wat betreft nodes die nergens aan gekoppeld zijn, ik ga er vanuit dat er gewerkt wordt met een table die een relatie met zichzelf heeft. Dus een article met een niet-bestaande ParentID (phantom article) kan dan niet. Alleen een ParentID==NULL kan wel, maar dat is het 0de niveau, dus die krijg je altijd als eerste terug.