Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

convert XML path to semicolon delimeted list by joining tables

Posted on 2016-09-13
1
Medium Priority
?
68 Views
Last Modified: 2016-09-20
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</ChildUserRoleID><ChildUserRoleID>343</ChildUserRoleID>
396433216              <ChildUserRoleID>105500128</ChildUserRoleID><ChildUserRoleID>105608599</ChildUserRoleID><ChildUserRoleID>343</ChildUserRoleID>

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</ChildUserRoleID><ChildUserRoleID>343</ChildUserRoleID>                                     ppp;sss  
396433216              <ChildUserRoleID>105500128</ChildUserRoleID>                                                                                                    ttt;yyy;zzz
                                   <ChildUserRoleID>105608599</ChildUserRoleID>          <ChildUserRoleID>343</ChildUserRoleID>

Thanks
0
Comment
Question by:PratikShah111
1 Comment
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 41796688
I cannot test it as I dont have test data.
I assume you have a login table and you can join to that table to get the name.
try something like this.
select s.UserRoleId,
STUFF(( select eh.ChildUserRoleID from stgERMHierarchy eh
where eh.UserRoleID = s.UserRoleId
for XML Path('')),1,0,'') as concatenatedText,
STUFF(( select lt.name from loginTable lt
where eh.ChildUserRoleID = lt.UserRoleID
for XML Path('')),1,0,'') as loginNames
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

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question