Tsql XmlTree Generatator

Objective: to create tsql function to generate XML tree structure.
Data for the tree structure will be stored with “Folder” table:

FolderId Name ParentFolderID
1 ROOT 0
2 1 1
3 1.1 2
4 2 1
5 2.1 4
6 2.2.1 5
7 3 1
8 3.1 7
9 3.2 7

Generated structure should look like:

<Folder Id="1" ParentFolderId="0" Name="ROOT">
  <Folder Id="2" ParentFolderId="1" Name="1">
    <Folder Id="3" ParentFolderId="2" Name="1.1" />
  </Folder>
  <Folder Id="4" ParentFolderId="1" Name="2">
    <Folder Id="5" ParentFolderId="4" Name="2.1">
      <Folder Id="6" ParentFolderId="5" Name="2.2.1" />
    </Folder>
  </Folder>
  <Folder Id="7" ParentFolderId="1" Name="3">
    <Folder Id="8" ParentFolderId="7" Name="3.1" />
    <Folder Id="9" ParentFolderId="7" Name="3.2" />
  </Folder>
</Folder>

First, let’s create the table and populate it with data:

CREATE TABLE [dbo].[Folder](
	[FolderId] [int] NULL,
	[Name] [varchar](150) NULL,
	[ParentFolderId] [int] NULL
) 

INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (1, 'ROOT', 0)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (2, '1', 1)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (3, '1.1', 2)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (4, '2', 1)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (5, '2.1', 4)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (6, '2.2.1', 5)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (7, '3', 1)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (8, '3.1', 7)
INSERT INTO [dbo].[Folder] ([FolderId], [Name], [ParentFolderId]) VALUES (9, '3.2', 7)

At the end our script:

/************************************
Example:
select dbo.GenerateXmlTree(0)
************************************/
CREATE FUNCTION [dbo].[GenerateXmlTree] ( @FolderId INT )
RETURNS XML
BEGIN 
    DECLARE @myXml XML
    SET @myXml = ( SELECT   FolderId		AS '@Id',
                            ParentFolderId	AS '@ParentFolderId',
                            Name			AS '@Name',
                            dbo.GenerateXmlTree(FolderId)
                   FROM     dbo.Folder
                   WHERE    ParentFolderId = @FolderId
                   ORDER BY Name
                 FOR XML PATH('Folder'))
    RETURN @myXml
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s