WITH JoinedTables(UserGroup, UserName) as (
	SELECT 'UG1', 'Tom'    UNION All
	SELECT 'UG1', 'Harry'  UNION all
	SELECT 'UG2', 'Albert' UNION all
	SELECT 'UG3', 'Jim'    UNION all
	SELECT 'UG3', 'Sam'
	)

SELECT UserGroup "@name"
	,  (SELECT UserName  "User" FROM JoinedTables T2
			Where t2.UserGroup = t1.UserGroup
			FOR XML PATH(''), TYPE) 

FROM (SELECT DISTINCT UserGroup FROM JoinedTables) t1
FOR XML PATH('UserGroup'), ROOT('UserGroupsInfo')

<UserGroupsInfo>
  <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>
</UserGroupsInfo>

declare @xml XML = '
<UserGroupsInfo>
  <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>
</UserGroupsInfo>
'

;with 
	Users(UserName, GroupName, rn) as (
		select [User].value('.', 'varchar(50)') Name
		     , [User].value('../@name[1]', 'varchar(50)') GroupName
		     , ROW_NUMBER() over(
					partition by [User].value('../@name[1]', 'varchar(50)') 
					order by (select 1)) as rn
		from @xml.nodes('/UserGroupsInfo/UserGroup/User') x([User])
	)

select UG1, UG2, UG3 from Users
pivot (max(UserName) for GroupName in (UG1, UG2, UG3)) pvt
return