Marcus Aurelius
asked on
SQL SERVER - FOR XML PATH
EXPERTS,
I have a stored procedure that I need to return data in a concatenated string format like this:
Reg~Dist~Clus!Reg~Dist~Clu s!Reg~Dist ~Clus&Reg~ Dist~Clus!
In this example I have 3 UserIDS, the first 2 have only ONE Region,District,Cluster combination but the last one has 2 combinations.
I need to use FOR XML PATH to product this display.
RDC
000~002~351!000~037~309!00 0~003~124& 000~003~14 8!
The "&" sign needs to be used to separate Reg,Dist,Clus combos within the same USER_ID.
This is what my source table looks like:
USER_ID REGION DISTRICT CLUSTER
253020 000 002 351
253254 000 037 309
253339 000 003 124
253339 000 003 148
This is my current "FOR XML PATH" SQL:
--FINAL DISPLAY
; WITH CTE_RDC_DISPLAY AS
(
SELECT
(SELECT REGION+'~'+DISTRICT+'~'+CL USTER+'!'
FROM #TEMP
--GROUP BY USER_ID,REGION,DISTRICT,CL USTER
FOR XML PATH('')) RDC
)
SELECT RDC
FROM CTE_RDC_DISPLAY
This SQL currently produces THIS:
RDC
000~002~351!000~037~309!00 0~003~124! 000~003~14 8!
I need to have the XML modified to return THIS:
RDC
000~002~351!000~037~309!00 0~003~124& 000~003~14 8!
The reason is that the last 2 RDC combinations belong to the SAME USER_ID.
Thanks.
I have a stored procedure that I need to return data in a concatenated string format like this:
Reg~Dist~Clus!Reg~Dist~Clu
In this example I have 3 UserIDS, the first 2 have only ONE Region,District,Cluster combination but the last one has 2 combinations.
I need to use FOR XML PATH to product this display.
RDC
000~002~351!000~037~309!00
The "&" sign needs to be used to separate Reg,Dist,Clus combos within the same USER_ID.
This is what my source table looks like:
USER_ID REGION DISTRICT CLUSTER
253020 000 002 351
253254 000 037 309
253339 000 003 124
253339 000 003 148
This is my current "FOR XML PATH" SQL:
--FINAL DISPLAY
; WITH CTE_RDC_DISPLAY AS
(
SELECT
(SELECT REGION+'~'+DISTRICT+'~'+CL
FROM #TEMP
--GROUP BY USER_ID,REGION,DISTRICT,CL
FOR XML PATH('')) RDC
)
SELECT RDC
FROM CTE_RDC_DISPLAY
This SQL currently produces THIS:
RDC
000~002~351!000~037~309!00
I need to have the XML modified to return THIS:
RDC
000~002~351!000~037~309!00
The reason is that the last 2 RDC combinations belong to the SAME USER_ID.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry...didn't read entire comment...
LOOKS LIKE ITS GOING TO WORK..!!
LOOKS LIKE ITS GOING TO WORK..!!
ASKER
EXCELLENT !!!!!
ASKER
here is the result I'm getting:
000-002-351!000-037-309!00
Can you tell me WHY I'm seeing '&' in the data instead of just '&'....?
Do you know how to fix this?
THANKS!!!