Create XML for recursive data — Part 1

For recursive data, create an XML to represent its structure is challeging in SQL Server. The folowing stored procedure can generate the xml according to the levels of the hirarchy, the element name for each level and attributes to be included. It does minimum validation for inputs and if some setting is wrong, it throws an error. E.g. if ColName specified is not in the data table @Table, it will throws error. The caller SP can catch the errors.

 Feel free to use  and modify it if the copyright statement is maintained

SET

ANSI_NULLS ON

GO

CREATE

PROCEDURE dbo.uspBuildXML

@Table

nvarchar(100),

@ItemIDCol

nvarchar(100),

@ParentIDCol nvarchar(100),

@Root

nvarchar(100),

@Debug

tinyint=0

AS

–==================================================

Feel free to use and modify it if the following modification history is maintained

–==================================================

— Modification History

— [1],2008-08-06,Created By Phe. All rights reserved.

–==================================================

— The store procedure produces xml document for hirarchy data stored in table @Table

— @Table can be temp table or a physical table. It must has a column LevelID, which defines the hirarchy level

— The root level is 0.

— @ItemIDCol defines the column that reprents the item (the PK of the data)

— @ParentIDCol defines the column that reprents the item’s parent. For root, the column value is NULL

— @Root defines the root element name for the xml. If empty string, no root element is added

— @Debug if 1, print debug information

— The caller must define two temp tables:

— #Nodes, defines the element name for each level

— #Attributes, defines the attributes to be included in the xml

SET

NOCOUNT ON

DECLARE @SQL nvarchar(max),@T1 nvarchar(50),@T0 nvarchar(50),@Att nvarchar(max),@PathName nvarchar(100)

DECLARE

@LevelID int,@Row int,@ID int

BEGIN

TRY

CREATE

TABLE #Levels (LevelID int not null)

SET

@SQL=N

INSERT #Levels(LevelID) SELECT ISNULL(MAX(LevelID),0) FROM ‘

+@Table

IF

@Debug=1 PRINT @SQL

EXEC

(@SQL)

SELECT @LevelID=LevelID FROM #Levels

IF

@Debug=1 SELECT @LevelID AS Levels

SET

@SQL=N

IF NOT EXISTS(SELECT 1 FROM ‘

+@Table+‘ WHERE LevelID=0)

RAISERROR(N”Root item level is not found!”,16,1)

IF NOT EXISTS(SELECT 1 FROM #Nodes WHERE LevelID=-1)

AND EXISTS(SELECT 1 FROM ‘

+@Table+‘ WHERE LevelID NOT IN (SELECT LevelID FROM #Nodes))

RAISERROR(N”The element name for some levels are not defined in table #Nodes!”,16,1)

IF NOT EXISTS(SELECT 1 FROM #Attributes)

RAISERROR(N”No attributes are defined in table #Attributes!”,16,1)

IF

@Debug=1 PRINT @SQL

EXEC

(@SQL)

SET

@Att=N

SELECT

@Att=@Att+ColName+N‘ AS "’+AttributeName+N‘",’+nchar(13)+nchar(10)

FROM

#Attributes

ORDER BY ID

SET

@Att=LEFT(@Att,LEN(@Att)-3)

IF

@Debug=1 PRINT @Att

SET @SQL=N

WHILE

@LevelID>=0

BEGIN

SET @T1=N‘T’+CAST(@LevelID as nvarchar(30))

SET @T0=N‘T’+CAST(@LevelID1 as nvarchar(30))

SET @PathName=N

SELECT @PathName=ElementName FROM #Nodes WHERE LevelID=@LevelID

IF @PathName=N

SELECT @PathName=ElementName FROM #Nodes WHERE LevelID=-1

SET @SQL=N

SELECT ‘

+@Att+ CASE WHEN @SQL=N THEN N ELSE N‘,(‘+@SQL+N‘)’ END+N

FROM ‘+@Table+N‘ ‘+@T1+N

WHERE ‘

+CASE WHEN @LevelID=0 THEN N‘T0.’+@ParentIDCol+‘ IS NULL’ ELSE

@T1

+N‘.’+@ParentIDCol+N‘=’+@T0+N‘.’+@ItemIDCol END +N

FOR XML PATH(”’

+@PathName+N”’)’+

CASE WHEN @LevelID=0 THEN CASE WHEN @Root<>N THEN ‘,ROOT(”’+@Root+N”’)’ ELSE N END ELSE N‘,TYPE’ END

SET @LevelID=@LevelID1

IF @Debug=1 PRINT @SQL

END

IF

@Debug=1 PRINT @SQL

EXEC

(@SQL)

END TRY

BEGIN

CATCH

DECLARE @Msg nvarchar(2048)

SET @Msg=N‘Failed to creat the XML:’+nchar(13)+nchar(10)+ERROR_MESSAGE()

RAISERROR(@Msg,16,1)

END

CATCH

GO

 

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