Assigning result of For Xml and With clause to a variable

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.

When I run it gives

Lookup Error – SQL Server Database Error: Incorrect syntax near the keyword ‘with’.

Is it possible to get this to run, or do I have to re-write my whole query to not use ‘with’ clauses?

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 + '</td><td>' + cast(rs2.email as varchar(80)) + '</td><td>'
      from resultset2 rs2
      for xml path('tr'), type) as varchar(max)
  )

Answer

I guess it’s a syntax matter – you should try like so:

DECLARE @htmltable VARCHAR(MAX)
WITH resultset1 AS (
    SELECT 'foo' colrs1
),
resultset2 AS (
SELECT 'bar' colrs2, rs1.colrs1
FROM resultset1 rs1
)

SELECT @htmltable = cast(
  (
      select rs2.colrs2 + '</td><td>' + cast(rs2.colrs2 as varchar(80)) + '</td><td>'
      from resultset2 rs2
      for xml path('tr'), type) as varchar(max)
  )

SELECT @htmltable