0

I using SQL Server 2012 and need to use FOR XML PATH to get a comma-separated string for like projects. The query runs and works but when I try to alter #T2, I get an error

String or binary data would be truncated

My code:

select distinct a.WBS1, (select #Team.Team as [text()] from #Team where #team.WBS1 = a.wbs1 for xml path('')) as Team into #T2from #Team a order by WBS1 ASC alter table #T2 alter column Team varchar(236)

#Team.Team is defined as varchar(236) after running the code I get #T2.Team is a nvarchar(-1) which I believe is nvarchar(max).

Is there any easy way to convert nvarchar to varchar? Why does #T2.Team end up being a nvarchar if #Team.Team is varchar(236)?

Improve this question
3
  • While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). Commented Jul 2, 2024 at 20:23
  • You aren't defining the data type, you're relying on implicit conversion from xml. Commented Jul 2, 2024 at 20:38
  • 2
    FOR XML always returns nvarchar(max) unless you specify , TYPE in which case you get xml. What's wrong with just using CONVERT(varchar(max), (SELECT ... FOR XML ...) ) Anyway your actual error is clearly because the data is longer than 236 after concatenating. Commented Jul 3, 2024 at 1:54
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.