Create XML for recursive data — Part 2

/*

Now Let’s test the stored procedure. We need to create the temp table #Nodes, which defines the element name for each level. If -1 is defined, any levels not defined will take the element name with LevelID=-1.

The table #Attributes defines what attributes need to be included in the xml. It can’t be empty. We define a temp table #Trees as the data table

*/

IF object_id(‘tempdb..#Trees’) IS NOT NULL

DROP

TABLE #Trees

IF object_id(‘tempdb..#Nodes’) IS NOT NULL

DROP

TABLE #Nodes

IF

object_id(‘tempdb..#Attributes’) IS NOT NULL

DROP

TABLE #Attributes

GO

SET

NOCOUNT ON

DECLARE @Row int,@LevelID int

CREATE TABLE #Trees (LevelID int not null default 0, ItemID int not null, ParentItemID int null, ItemName nvarchar(255) NOT NULL, ItemDescription nvarchar(255) NOT NULL)

CREATE

TABLE #Nodes(LevelID int not null, ElementName nvarchar(100) NOT NULL)

CREATE

TABLE #Attributes(ID int not null, ColName nvarchar(100) NOT NULL, AttributeName nvarchar(100) NOT NULL)

— Populate testing data

INSERT

#Trees(ItemID,ParentItemID,ItemName,ItemDescription)

SELECT

1,NULL,N‘RootFolder01’,N‘It”s First Root Folder’

UNION ALL

SELECT 2,NULL,N‘RootFolder02’,N‘It”s Second Root Folder’

UNION ALL SELECT 3,1,N‘SubFolder011’,N‘It”s Sub Folder’

UNION ALL

SELECT 4,1,N‘SubFolder012’,N‘It”s Sub Folder’

UNION ALL

SELECT 5,2,N‘SubFolder021’,N‘It”s Sub Folder’

UNION ALL

SELECT 6,2,N‘SubFolder022’,N‘It”s Sub Folder’

UNION ALL

SELECT 7,3,N‘SubFolder0111’,N‘It”s Sub Folder’

UNION ALL

SELECT 8,3,N‘SubFolder0112’,N‘It”s Sub Folder’

UNION ALL SELECT 9,5,N‘SubFolder0211’,N‘It”s Sub Folder’

UNION ALL

SELECT 10,6,N‘SubFolder0221’,N‘It”s Sub Folder’

UNION ALL

SELECT 11,9,N‘SubFolder02111’,N‘It”s Sub Folder’

UNION ALL

SELECT 12,10,N‘SubFolder02211’,N‘It”s Sub Folder’

— Build the level:

UPDATE

#Trees SET LevelID=0 WHERE ParentItemID IS NULL

SET

@Row=ISNULL(@@ROWCOUNT,0)

SET

@LevelID=0

WHILE

@Row>0

BEGIN

SET @LevelID=@LevelID+1

UPDATE A SET LevelID=@LevelID FROM #Trees A, #Trees B

WHERE B.LevelID=@LevelID1 AND B.ItemID=A.ParentItemID

SET @Row=ISNULL(@@ROWCOUNT,0)

END

— show the test data

SELECT

* FROM #Trees

— Define element name for each level

INSERT

#Nodes(LevelID,ElementName)

SELECT

0,N‘Folders’ UNION ALL

SELECT

1,N‘Folder’ UNION ALL

SELECT

2,N‘SubFolder’ UNION ALL

SELECT

3,N‘SubFolder’ UNION ALL

SELECT

4,N‘SubFolder’

— Define the attributes for the xml element

INSERT

#Attributes(ID,ColName,AttributeName)

SELECT

1,‘ItemName’,‘@Name’ UNION ALL

SELECT

2,‘ItemDescription’,‘@Description’

— Call the SP, with Root name as AllFolders, the data table is #Trees

— The ItemIDCol in data table is ItemID, and @ParentIDCol is ParentItemID

EXEC

dbo.uspBuildXML @Table=N‘#Trees’,@ItemIDCol=N‘ItemID’,@ParentIDCol=N‘ParentItemID’,@Root=N‘AllFolders’,@Debug=0

— Now use Folder as first level element name, and SubFolder as other levels. Root name is Folders

DELETE

FROM #Nodes

INSERT

#Nodes(LevelID,ElementName)

SELECT

1,N‘SubFolder’ UNION ALL

SELECT

0,N‘Folder’

EXEC

dbo.uspBuildXML @Table=N‘#Trees’,@ItemIDCol=N‘ItemID’,@ParentIDCol=N‘ParentItemID’,@Root=N‘Folders’,@Debug=0

— No root

EXEC

dbo.uspBuildXML @Table=N‘#Trees’,@ItemIDCol=N‘ItemID’,@ParentIDCol=N‘ParentItemID’,@Root=N,@Debug=0

GO

— The result for the first XML:

<AllFolders>

<Folders Name="RootFolder01" Description="It’s First Root Folder">

<Folder Name="SubFolder011" Description="It’s Sub Folder">

<SubFolder Name="SubFolder0111" Description="It’s Sub Folder" />

<SubFolder Name="SubFolder0112" Description="It’s Sub Folder" />

</Folder>

<Folder Name="SubFolder012" Description="It’s Sub Folder" />

</Folders>

<Folders Name="RootFolder02" Description="It’s Second Root Folder">

<Folder Name="SubFolder021" Description="It’s Sub Folder">

<SubFolder Name="SubFolder0211" Description="It’s Sub Folder">

<SubFolder Name="SubFolder02111" Description="It’s Sub Folder" />

</SubFolder>

</Folder>

<Folder Name="SubFolder022" Description="It’s Sub Folder">

<SubFolder Name="SubFolder0221" Description="It’s Sub Folder">

<SubFolder Name="SubFolder02211" Description="It’s Sub Folder" />

</SubFolder>

</Folder>

</Folders>

</

AllFolders>

 

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s