sql-server - 在 SQL Server 中组合 CTE "WITH"和 "WITH XMLNAMESPACES...."

Question

是否有人设法在 SQL Server 的 T-SQL 中创建一个还包含 WITH XMLNAMESPACES 声明的 CTE？

似乎两个 WITH 关键字都坚持是“T-SQL 批处理中的第一个”，但这实际上不起作用......

我尝试过:

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

不起作用:-((语法错误)

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.

所以我尝试在第二个 WITH 前面加上分号:

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

得到了这个:

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

然后我尝试将 WITH XMLNAMESPACES 放入 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

得到了这个:

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 ')'.

那么我到底该怎么做呢？

Answer 1

使用逗号代替第二个WITH，例如

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

如果您想要多个 CTE 表达式，则相同。您只需指定 WITH 一次，然后所有其他 WITH block 只需使用逗号而不是关键字。