recursive data,create an XMLto represent its structure is challeging inSQL Server. The folowing stored procedure can generate the xml according to the levels of the hirarchy, the element namefor each leveland attributes to be included. It does minimum validation for inputs andifsome setting is wrong, it throws an error. E.g. if ColName specified isnotin 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_NULLSON

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