PratikShah111
asked on
convert XML path to semicolon delimeted list by joining tables
I have the following query
select s.UserRoleId,
STUFF(( select eh.ChildUserRoleID from stgERMHierarchy eh
where eh.UserRoleID = s.UserRoleId
for XML Path('')),1,0,'') as concatenatedText
from StgERMUser s
inner join HubUser h on h.Login = s.NetworkID
inner join stgERMHierarchy eh on eh.UserRoleID = s.UserRoleId
group by s.UserRoleId
order by s.UserRoleId
The resultset that I get is something like this :
userRoleId concatenatedText
344321762 <ChildUserRoleID>326</Chil dUserRoleI D><ChildUs erRoleID>3 43</ChildU serRoleID>
396433216 <ChildUserRoleID>105500128 </ChildUse rRoleID><C hildUserRo leID>10560 8599</Chil dUserRoleI D><ChildUs erRoleID>3 43</ChildU serRoleID>
I want to take the concatenatedText column and read each childuserroleid and get actual login name for that particular userrole and show it in semicolon delimeted.
so it should be something like this
userRoleId concatenatedText login
344321762 <ChildUserRoleID>326</Chil dUserRoleI D><ChildUs erRoleID>3 43</ChildU serRoleID> ppp;sss
396433216 <ChildUserRoleID>105500128 </ChildUse rRoleID> ttt;yyy;zzz
<ChildUserRoleID>105608599 </ChildUse rRoleID> <ChildUserRoleID>343</Chil dUserRoleI D>
Thanks
select s.UserRoleId,
STUFF(( select eh.ChildUserRoleID from stgERMHierarchy eh
where eh.UserRoleID = s.UserRoleId
for XML Path('')),1,0,'') as concatenatedText
from StgERMUser s
inner join HubUser h on h.Login = s.NetworkID
inner join stgERMHierarchy eh on eh.UserRoleID = s.UserRoleId
group by s.UserRoleId
order by s.UserRoleId
The resultset that I get is something like this :
userRoleId concatenatedText
344321762 <ChildUserRoleID>326</Chil
396433216 <ChildUserRoleID>105500128
I want to take the concatenatedText column and read each childuserroleid and get actual login name for that particular userrole and show it in semicolon delimeted.
so it should be something like this
userRoleId concatenatedText login
344321762 <ChildUserRoleID>326</Chil
396433216 <ChildUserRoleID>105500128
<ChildUserRoleID>105608599
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.