Using XML PATH in Sql Stored Procedure

Question

I'm trying to get a row with the concatenation of a welders names. This is what i've got:

SELECT  jsd1.JuntaSoldaduraID, 
        REPLACE(RTRIM((SELECT s1.Nombre + ' ' + s1.ApPaterno + ' ' + 
            s1.ApMaterno + '' + CAST('' AS VARCHAR(MAX)) + ' ' 
FROM JuntaSoldaduraDetalle jsd
INNER JOIN  Soldador s1 on s1.SoldadorID = jsd.SoldadorID
WHERE (jsd1.JuntaSoldaduraID = jsd.JuntaSoldaduraID) 
          and (jsd.TecnicaSoldadorID = 2)
FOR XML PATH (''))),' ',', ') AS NombreSoldador
FROM JuntaSoldaduraDetalle jsd1
INNER JOIN Soldador s
ON s.SoldadorID = jsd1.SoldadorID
GROUP BY jsd1.JuntaSoldaduraID

I'm able to get the information that i want but with a little problem. What I want is "John Smith, David Rogers, Peter Simons" etc.. in other words, full names separated by commas. But i'm receiving "John, smith, David, Rogers, Peter, Simons"..

Any help appreciated.

Thanks in advance.

Joe Stefanelli · Accepted Answer · 2010-11-30 21:30:42Z

3

Your REPLACE will replace all spaces with a comma. Instead, make the comma part of your query and use STUFF to remove the first occurrence of the comma. You might also want to incorporate the use of COALESCE in case any of the name fields are NULL.

SELECT  jsd1.JuntaSoldaduraID, 
        STUFF((SELECT ', ' + COALESCE(s1.Nombre + ' ','') 
                           + COALESCE(s1.ApPaterno + ' ','') 
                           + COALESCE(s1.ApMaterno,'')
                   FROM JuntaSoldaduraDetalle jsd
                       INNER JOIN  Soldador s1 
                           on s1.SoldadorID = jsd.SoldadorID
                   WHERE jsd1.JuntaSoldaduraID = jsd.JuntaSoldaduraID 
                       and jsd.TecnicaSoldadorID = 2
                   FOR XML PATH ('')),1,2,'') AS NombreSoldador
    FROM JuntaSoldaduraDetalle jsd1
        INNER JOIN Soldador s
            ON s.SoldadorID = jsd1.SoldadorID
    GROUP BY jsd1.JuntaSoldaduraID

edited Nov 30, 2010 at 21:30

answered Nov 30, 2010 at 21:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

valin077 Over a year ago

hey Joe, i've tried this out and it works great. Thank you so much!

Lieven Keersmaekers Over a year ago

+1 my approach of teach a man to fish and such clearly didn't pay off.

Joe Stefanelli Over a year ago

@Lieven: Sometimes, a man may come from the desert and you have to spend a little more time explaining what a rod and reel are. :-)

Lieven Keersmaekers · Accepted Answer · 2010-11-30 21:05:59Z

0

You are replacing single spaces in the result with a comma and a space.

What is the result if you remove the REPLACE from the query?

answered Nov 30, 2010 at 21:05

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

2 Comments

valin077 Over a year ago

I could remove the REPLACE, but still i'll have the names without separation, just: "john smith david jones peter simons", etc.

Lieven Keersmaekers Over a year ago

You could append the comma in the inner select and only remove the last comma in the outer select.

Collectives™ on Stack Overflow

Using XML PATH in Sql Stored Procedure

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related