结合CTE“ WITH”和“ WITH XMLNAMESPACES ...”。在SQL Server中 [英] Combine CTE "WITH" and a "WITH XMLNAMESPACES...." in SQL Server

问题描述

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

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

似乎两个 WITH 关键字都坚持要成为 T-SQL批处理中的第一个，但这实际上并没有用....

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

我尝试过：

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)

消息156，级别15，状态1，第2行

关键字'WITH'附近的语法不正确。

消息319，级别15，状态1，第2行


关键字'with'附近的语法不正确。如果此语句是
公用表表达式，则
xmlnamespaces子句或更改
跟踪上下文子句，前一个
语句必须为te

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

并得到以下信息：

消息102，级别15，状态1，第2行

';'附近的语法不正确。

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

，然后我尝试将 WITH XMLNAMESPACES 放入CTE：

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

并得到以下信息：

消息156，级别15，状态1，第4行

关键字
'WITH'附近的语法不正确。

消息319，级别15，状态1，第4行


关键字'with'附近的语法不正确。如果此语句是
公用表表达式，
xmlnamespaces子句或change
跟踪上下文子句，则先前的
语句必须以
分号终止。 br>
消息102，级别15，状态1，第21行

'）'附近的语法不正确。

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

那么我该怎么做呢？

推荐答案

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

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

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

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.

这篇关于结合CTE“ WITH”和“ WITH XMLNAMESPACES ...”。在SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！