May 21, 2014 at 5:27 am
Hi,
I am joining multiple tables and my result is following:
UserGroup UserName
UG1 Tom
UG1 Harry
UG2 Albert
UG3 Jim
UG3 Sam
[/code]
I want xml in following format:
<UserGroupsInfo>
<UserGroups>
<UserGroup name="UG1">
<User>
Tom
</User>
<User>
Hary
</User>
</UserGroup>
<UserGroup name="UG2">
<User>
Albert
</User>
</UserGroup>
<UserGroup name="UG3">
<User>
Jim
</User>
<User>
Sam
</User>
</UserGroup>
</UserGroups>
</UserGroupsInfo>
I have tried all combinations of for xml path but no success yet. Can someone help?
Regards,
Shpunk
May 21, 2014 at 11:01 am
This may be unwinding your multiple tables join, but the easiest way is to create sub-queries by level.
Something like this:
;with fun as (
select 'UG1' as UserGroup, 'Tom' as UserName union all
select 'UG1', 'Harry' union all
select 'UG2', 'Albert' union all
select 'UG3', 'Jim' union all
select 'UG3' , 'Sam'),
distinctuserGroups as (select distinct usergroup from fun)
select '',
(
select usergroup as '@name',
(
select username as 'user'
from fun
where fun.UserGroup=dug.UserGroup
for XML PATH(''), TYPE
)
from distinctuserGroups dug
for XML PATH('UserGroup'), Root('UserGroups'), TYPE
)
for XML PATH(''), root('UserGroupInfo')
You then get:
<UserGroupInfo>
<UserGroups>
<UserGroup name="UG1">
<user>Tom</user>
<user>Harry</user>
</UserGroup>
<UserGroup name="UG2">
<user>Albert</user>
</UserGroup>
<UserGroup name="UG3">
<user>Jim</user>
<user>Sam</user>
</UserGroup>
</UserGroups>
</UserGroupInfo>
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply