Generate Xml in hierarchical order from DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate Xml in hierarchical order from DB

Author

Topic

loken.bhatt
Starting Member

5 Posts

Posted - 2007-03-12 : 06:42:04

Hi
I have a table named UserProfile with following structure :
(TypeId bigint,TypeName nvarchar(100),ParentTypeId bigint)
Have some following sample data :
TypeId TypeName ParentTypeId
1 User 0
2 Artist 1
3 Singer 2
4 DJ 1
5 Band 1
6 Partner 0
7 Movies 6
8 GoodMovies 7
9 BadMovies 7
10 Producer 6

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.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-12 : 12:24:20

for fine grained control of the xml, have a look at FOR XML PATH syntax.

stay away from FOR XML EXPLICIT at all costs. It is a horrid thing.

www.elsasoft.org

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 13:27:45

Post the query you're using now, and someone can help you fix it.

loken.bhatt
Starting Member

5 Posts

Posted - 2007-03-15 : 01:00:14

quote:
Originally posted by snSQL

Post the query you're using now, and someone can help you fix it.

I am using the following query :

With Heirarchy
as
(
Select TypeId,TypeName,ParentTypeId,0 as [Level] from UserProfileTypeDummyTable where ParentTypeId=0
Union All
Select UserProfileTypeDummyTable.TypeId,UserProfileTypeDummyTable.TypeName,
UserProfileTypeDummyTable.ParentTypeId, [Level]+1 from Heirarchy inner join UserProfileTypeDummyTable on
Heirarchy.TypeId=UserProfileTypeDummyTable.ParentTypeId
)
Select Heirarchy.TypeId,Heirarchy.TypeName,Heirarchy.ParentTypeId,
SubProfile.TypeId,SubProfile.TypeName,SubProfile.ParentTypeId from Heirarchy inner join UserProfileTypeDummyTable as SubProfile
on SubProfile.ParentTypeId=Heirarchy.TypeId
for XML Auto,elements,type

please reply to generate the heirarchical data in xml from db

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources