title: Assigning result of For Xml and With clause to a variable
body: <p>I'm trying to use XML for to concatenate a bunch of columns together and then assign the result set to a variable.
  I'm using 'with' clauses because my initial sub-queries contain calculated fields that later sub-queries need to generate additional fields.</p>
  <p>When I run it gives</p>
  <blockquote>
  <p>Lookup Error - SQL Server Database Error: Incorrect syntax near the keyword 'with'.</p>
  </blockquote>
  <p>Is it possible to get this to run, or do I have to re-write my whole query to not use 'with' clauses?</p>
  <pre><code>declare @htmltable varchar(max)
  set
    @htmltable = cast(
    (
        with resultset1 as (
          select t1.customer_email
          from table1 t1
        ),
        resultset2 as (
          select t2.email,
          rs1.*
          from resultset1 rs1
          join table2 t2 
          on rs1.customer_email = t2.email
        )
        select rs2.customer_email + '&lt;/td&gt;&lt;td&gt;' + cast(rs2.email as varchar(80)) + '&lt;/td&gt;&lt;td&gt;'
        from resultset2 rs2
        for xml path('tr'), type) as varchar(max)
    )
  </code></pre>