Problem using sql xml path

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I want to concatenate one column values. So I give following, But it is not working the error displays.

SQL

SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH(")),1,2,")

The error is

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')),1,2,'.

Posted 27-Nov-12 17:21pm

devausha

Updated 27-Nov-12 17:50pm

v2

Add a Solution

3 solutions

Solution 1

It seems you have used double quote" instead of two single qoutes''. check updating your query with new query as shown below

SQL

your query :
SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH(")),1,2,")

SQL

new query :
SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH('')),1,2,'')

Posted 29-Nov-12 5:56am

hitesh_tech

Comments

devausha 29-Nov-12 23:25pm

Thanks for your reply. I try your new query, But the error displays like

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

hitesh_tech · Accepted Answer · 2012-11-30T01:30:00

Solution 2

Do casting as shown below.

SQL

new query :
SELECT STUFF((SELECT ',' + ltrim(cast(scd_service_id as varchar(20))) FROM Service_Details FOR XML PATH('')),1,2,'')

Posted 30-Nov-12 1:30am

hitesh_tech

Comments

devausha 30-Nov-12 7:51am

Hi Thank you so much...
I am used following query for matching name from another table. It is also working. But the first letter is not displaying
My query is

SELECT STUFF((SELECT ',' + ltrim(T1.service_name) FROM Service_Details T2 Left outer join Service_master T1 on T2.scd_service_id=T1.service_id where T2.scd_jc_id=5 FOR XML PATH('')),1,2,'')

The result is 'arwashing project,Welding'

But actual result is 'Carwashing project,Welding'

devausha 30-Nov-12 7:52am

Why it comes without first letter?

hitesh_tech · Accepted Answer · 2012-11-30T02:06:00

Solution 3

Try replacing SELECT ',' with SELECT ', '. Give one space after comma.

Posted 30-Nov-12 2:06am

hitesh_tech

Updated 30-Nov-12 2:13am

v2

Comments

devausha 1-Dec-12 3:39am

Hi, Thank u so much...It is working correctly. Very nice...