Sql-server – Combine CTE “WITH” and a “WITH XMLNAMESPACES….” in SQL Server

common-table-expressionsql server

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??

Best Answer

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.

Related Topic