Cannot assign CTE result to a varchar variable?

Question

I am trying to write the following SQL.

declare @s varchar(max) = (
  with c as (select ...)
  select a, b, c, d, ....
  from ... join c on .... join c on .... join c on ....
  order by ...
  for xml raw('...'), elements
);

However, it's incorrect syntax (The following shows the error message). Do I have to convert it to subqueries? I am trying to avoid to expand the CTE in multiple places.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Update:
The for xml and order by makes select @s = ...

Answer 1

You need to separate the declaration of @s from the assignment.

Something like this will work for you.

declare @T table
(
  ID int,
  Col1 int
)

insert into @T values(1, 10),(2, 20)

declare @s varchar(max)

;with C as
(
  select *
  from @T
)
select @s = 
  (
    select *
    from C as C1
      inner join C as C2
        on C1.ID = C2.ID
    for xml raw, elements
  )

select @s

Result:

<row>
  <ID>1</ID>
  <Col1>10</Col1>
  <ID>1</ID>
  <Col1>10</Col1>
</row>
<row>
  <ID>2</ID>
  <Col1>20</Col1>
  <ID>2</ID>
  <Col1>20</Col1>
</row>