SQL for concatenating strings/rows into one string/row? (How to use FOR XML PATH with INSERT?)

Question

I am concatenating several rows/strings in an table (on Microsoft SQL Server 2010) into a string by using a method as suggested here:

SELECT ',' + col FROM t1 FOR XML PATH('')

However, if I try to insert the resulting string as (single) row into another table like so:

INSERT INTO t2
SELECT ', ' + col FROM t1 FOR XML PATH('')

I receive this error message:

The FOR XML clause is not allowed in a INSERT statement.

t2 currently has a single column of type NVARCHAR(80). How can I overcome this problem, i.e. how can I collapse a table t1 with many rows into a table t2 with row that concatenates all the strings from t1 (with commas)?

TheProvost · Accepted Answer · 2014-11-04 09:27:22Z

3

Rather than xml path why not do it like this?

DECLARE @Cols VARCHAR(8000) 
SELECT @Cols = COALESCE(@Cols + ', ', '') + 
    ISNULL(col, 'N/A')
FROM t1
Insert into t2 values(@Cols);

answered Nov 4, 2014 at 9:27

TheProvost

1,9133 gold badges18 silver badges42 bronze badges

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

3 Comments

asontu Over a year ago

I see this more often when concatenating string, that people use COALESCE() with just 2 arguments. Is there a reason to use COALESCE() here rather than ISNULL()? I've used ISNULL() in these cases and didn't have problems with it evaluating @Cols + ', ' only once or anything.

Drux Over a year ago

+1 VG but I still can't get the ISNULL part to work (result is null instead of N/A). I am using a subquery that starts with SELECT ... AS col ... instead of t2. Any further ideas?

TheProvost Over a year ago

Show me the exact query.

asontu · Accepted Answer · 2014-11-04 09:38:18Z

2

You need to cast it back to an nvarchar() before inserting. I use this method, deletes the first separator as well and as I'm doing the , type part, it handles entities correctly.

insert into t2
select stuff((
    select ', ' + col from t1
    for xml path(''), type
).value('.', 'nvarchar(80)'), 1, 2, '')

So you concatenate all col with prepending comma+space as an xml-object. Then you take the .value() of child with xquery-path . which means "take the child we are at, don't traverse anywhere". You cast it as an nvarchar(80) and replace a substring starting at position 1 and length 2 with an empty string ''. So the 2 should be replaced with however long your separator is.

answered Nov 4, 2014 at 9:38

asontu

4,6591 gold badge24 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL for concatenating strings/rows into one string/row? (How to use FOR XML PATH with INSERT?)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related