Hi
I have a table named UserProfile with following structure :
(TypeIdbigint,TypeNamenvarchar(100),ParentTypeIdbigint)
Have some following sample data :
TypeId TypeName ParentTypeId
1User0
2Artist1
3Singer2
4DJ1
5Band1
6Partner0
7Movies6
8GoodMovies7
9BadMovies7
10Producer6
I want to generate the XML in following form
<Main>
<TypeId>1</TypeId>
<TypeName>User</TypeName>
<ParentTypeId>0</ParentTypeId>
<SubProfiles>
<SubProfile>
<TypeId>2</TypeId>
<TypeName>Artist</TypeName>
<ParentTypeId>1</ParentTypeId>
</SubProfile>
<SubProfile>
<TypeId>4</TypeId>
<TypeName>DJ</TypeName>
<ParentTypeId>1</ParentTypeId>
</SubProfile>
<SubProfile>
<TypeId>5</TypeId>
<TypeName>Band</TypeName>
<ParentTypeId>1</ParentTypeId>
</SubProfile>
</SubProfiles>
</Main>
I am usign CTE for recursive query and then using for xml auto mode to generate xml. But the data is not generating when the parent child relation is in depth level.
Kindly anybody tell me the query so that I can generate the xml in heirarchical order of data.
CREATE FUNCTION dbo.GetProfileSubTree(@.TypeId int)
RETURNS XML
BEGIN RETURN
(SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=@.TypeId
ORDER BY TypeId
FOR XML PATH('SubProfile'),ROOT('SubProfiles'),TYPE)
END
GO
SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=0
ORDER BY TypeId
FOR XML PATH(''),ROOT('Main'),TYPE
|||
Hi,
This solution helps me to generate the heirarchical data
Thanks
|||Note that SQL Server 2005 has a maximum limit of 32 recursively nested function invocations.
This is mentioned here at
http://msdn2.microsoft.com/en-us/library/ms345137.aspx
Can you suggest any alternate method for unlimited depth?
