Menu
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??
521
After you've defined the first CTE, it is separated from the second one only by the comma, i.e. you write WITH only once. After that, it doesn't matter how many CTEs you define; it's only important that you separate them by comma and start every CTE using its name.
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins.
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
