title: Cannot assign CTE result to a varchar variable?
body: <p>I am trying to write the following SQL.</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>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.</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p><strong>Update:</strong><br>
  The <code>for xml</code> and <code>order by</code> makes <code>select @s = ...</code> </p>