common table expression - Combine CTE "WITH" and a "WITH XMLNAMESPACES...." in SQL Server

Question

Has anyone managed to create a CTE in SQL Server's T-SQL that also includes a WITH XMLNAMESPACES declaration?

It seems both WITH keywords insist on being the "first in the T-SQL batch", and that doesn't really work....

I tried:

WITH XMLNAMESPACES('http://schemas.myself.com/SomeSchema' as ns)
WITH CTEQuery AS
(
SELECT (list of fields)
    FROM dbo.MyTable
    WHERE (conditions)
)
SELECT * FROM CTEQuery

Didn't work :-( (syntax errors)

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 2
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.

So I tried prepending the second WITH with a semicolon:

WITH XMLNAMESPACES('http://schemas.myself.com/SomeSchema' as ns)
;WITH CTEQuery AS
(
SELECT (list of fields)
    FROM dbo.MyTable
    WHERE (conditions)
)
SELECT * FROM CTEQuery

and got this:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ';'.

and then I tried putting the WITH XMLNAMESPACES into the CTE:

WITH CTEQuery AS
(
   WITH XMLNAMESPACES('http://schemas.myself.com/SomeSchema' as ns)
   SELECT (list of fields)
      FROM dbo.MyTable
      WHERE (conditions)
)
SELECT * FROM CTEQuery

and got this:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 4
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.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.

So how the heck do I do this??

question from:https://stackoverflow.com/questions/3685155/combine-cte-with-and-a-with-xmlnamespaces-in-sql-server

Answer 1

Use a comma instead of the second WITH, e.g.

WITH XMLNAMESPACES('http://schemas.myself.com/SomeSchema' as ns)
,CTEQuery AS
(
SELECT (list of fields)
    FROM dbo.MyTable
    WHERE (conditions)
)
SELECT * FROM CTEQuery

The same if you want multiple CTE expressions. You only need to specify WITH once, and then all other WITH blocks just use a comma instead of the keyword.