Last post Jun 02, 2015 02:32 PM by maggiemays
Jun 01, 2015 07:33 PM|maggiemays|LINK
I have a stored procedure that joins several tables together. One of the tables being joined has multiple entries. I read about using xml path' in order to put those values together in one column along with using STUFF. I tried it on a simple join, but
I'm having syntax problems when I want to use it in my stored procedure with many joins. Does somebody know of a good example that uses xml path and STUFF in combination with several joins ?
Jun 01, 2015 09:19 PM|gimimex|LINK
I believe the number of Joins is indifferent.
The Joins can be added both inside and outside the Stuff function according to need.
Check an example without Joins in the thread below:
If the problem persists, you'd better post your query to be analyzed.
Hope this helps.
Jun 01, 2015 10:07 PM|Edwin Guru Singh|LINK
I tried it on a simple join, but I'm having syntax problems when I want to use it in my stored procedure with many joins. Does somebody know of a good example that uses xml path and STUFF in combination with several joins ?
As per this case, you can join all your corresponding tables & get the result through Common table expression(CTE) .after that you can apply STUFF & XML with CTE. for your reference , check the sample query below :
with CTE_Tables as
inner join tbltest2[t2]
select distinct id ,
from CTE_Tables [T1]
FOR XML PATH('')),1,1,'') [Names]
from CTE_Tables [T2]
Further any queries, kindly post your corresponding query with issue explanation which may help us to resolve the case.
Jun 02, 2015 10:01 AM|maggiemays|LINK
Thank you. I'll try it and post if I need more help.
Are there other ways to accomplish the same thing?
Jun 02, 2015 12:33 PM|maggiemays|LINK
What am I doing wrong? I get errors about 'multi-part identifier not bound' on incidentparty.classification. If I include an explicit join, I get more than one record in the output.
How do i fix this? Do I need to put in another 'STUFF' function for those columns I need from incidentparty?
select distinct incident.CaseNumber ,incident.IncidentDate ,IncidentParty.Classification,
STUFF((SELECT ',' + incidentparty.firstname + ' ' + INCIDENTPARTY.LASTNAME AS [text()]
FROM INCIDENTPARTY WHERE INCIDENTPARTY.CASENUMBER = Incident.CaseNumber
FOR XML PATH('')),1,1,'')
inner join defPreserve p on incident.PreserveCode = p.Code
inner join defUser s on incident.ReportingRanger = s.Abbreviation
where s.Abbreviation = 'mg'
order by incident.IncidentDate desc, incident.CaseNumber
Jun 02, 2015 12:57 PM|gimimex|LINK
How is the result including the explicit join? And how it should be?
Jun 02, 2015 01:24 PM|maggiemays|LINK
when I use explicit join, I end up with more than one row. I only want to have one row of data per casenumber. I think I can see why I'm getting that multi-part error, but I don't know how to get a single row per casenumber.
Jun 02, 2015 01:54 PM|maggiemays|LINK
I think I'm getting more than one row of data per casenumber when the classification is different. When I do an inner join on incidentparty, I get the the rows of data that contain different values per classification. Is there something I can do to only
get one row of data even though the column values are different?
Do you think this is why I'm getting more data?
Jun 02, 2015 01:57 PM|gimimex|LINK
If there are different classifications, which of them should be returned?
Jun 02, 2015 02:32 PM|maggiemays|LINK
I included multiple 'stuff' commands and I think it looks good now. Thank you so much for your time.
Actually, when I added another field inside 'stuff', I'm now getting dupicates, so it doesnt work. :(