FOR XML clause WITH CTE

Question

Hi all,

As stated here ,

• The following clauses cannot be used in the CTE_query_definition:

  • COMPUTE or COMPUTE BY

  • ORDER BY (except when a TOP clause is specified)

  • INTO

  • OPTION clause with query hints

  • FOR XML

  • FOR BROWSE

But  !!!

;WITH CTE
AS
(
SELECT (SELECT name TableName FROM sys.tables FOR XML PATH (''),TYPE ) tmp
)
SELECT * FROM CTE FOR XML PATH('')

;WITH CTE1
AS
(
SELECT (SELECT name TableName FROM sys.tables FOR XML PATH (''),TYPE ) tmp
UNION ALL
SELECT (SELECT name ProcedureName FROM sys.procedures  FOR XML PATH (''),TYPE ) tmp
)
SELECT * FROM CTE1 FOR XML PATH('')

I was working on one of my task using FOR XML clause WITH CTE but I found in CTE documentation that it cannot be used .

Correct me if my understanding is wrong .

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 1

Dear Sathya, 

It's about using these clauses directly in cte definition. Please see the following code. The first query done without any error. But same query cannot be within a cte:

SELECT   name TableName
               FROM     sys.tables
               FOR      XML PATH('') , TYPE;

WITH    CTE
          AS ( SELECT   name TableName
               FROM     sys.tables
               FOR      XML PATH('') ,
                            TYPE
             )
    SELECT  *
    FROM    CTE ;

---

Saeid Hasani, sqldevelop.wordpress.com

Download Books Online for SQL Server 2012

Answer 2

In fact you used for xml clause within derived table. 

---

Saeid Hasani, sqldevelop.wordpress.com

Download Books Online for SQL Server 2012

Answer 3

Saeid, your example works if you specify column alias for the CTE:

WITH    CTE ( TableName)
          AS ( SELECT   name as TableName
               FROM     sys.tables
               FOR      XML PATH('') ,
                            TYPE
             )
    SELECT  *
    FROM    CTE ;

I opened a thread on this issue: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5a2f123-81c7-4ed9-920c-2f30cf0ad9df/using-for-xml-clause-in-cte-definition?forum=sqldocumentation

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 4

BOL: "The following clauses cannot be used in the CTE_query_definition:

• ORDER BY (except when a TOP clause is specified)

• INTO

• OPTION clause with query hints

• FOR XML

• FOR BROWSE

"

LINK: http://msdn.microsoft.com/en-us/library/ms175972.aspx

Yet the following works:

USE Northwind;
WITH    CTE ( TableName)
          AS ( SELECT   name as TableName
               FROM     sys.tables
               FOR      XML PATH('') ,
                            TYPE
             )
    SELECT  *
    FROM    CTE ;
/*
<TableName>Employees</TableName>
<TableName>Categories</TableName>
<TableName>CustOrderHistory</TableName>
<TableName>Customers</TableName>
<TableName>TestOrders</TableName>
<TableName>testOrderSummary</TableName>
<TableName>Shippers</TableName>
<TableName>Suppliers</TableName>
<TableName>Orders</TableName>
<TableName>Products</TableName>
<TableName>Order Details</TableName>
<TableName>Reorder</TableName>
<TableName>Product</TableName>
<TableName>CustomerCustomerDemo</TableName>
<TableName>CustomerDemographics</TableName>
<TableName>Region</TableName>
<TableName>Territories</TableName>
<TableName>EmployeeTerritories</TableName>
<TableName>sysdiagrams</TableName>
*/

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 5

I get an error when trying to enter a simple note here:

http://msdn.microsoft.com/en-us/library/ms175972.aspx

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 6

Hi Saeid,

Thanks for your reply !!

Like Kalmon Toth said , it throws error 

" No Column name was specified for column 1 of CTE  "

So if we specify column alias its going to work  . 

If you take the case of ORDER BY clause with CTE , it is clearly documented saying exceptional reason that it works "only when a TOP clause is specified " .

And if we want to append XML fragment with another XML fragment (either first or last) , we can also do this way rather than getting the XML into sql:variable and modifying with XML DML

Just for example :

;WITH CTE1
AS
(
SELECT (SELECT name TableName FROM sys.tables FOR XML PATH (''),TYPE ) tmp
UNION ALL
SELECT (SELECT name ProcedureName FROM sys.procedures  FOR XML PATH (''),TYPE ) tmp
)
SELECT * FROM CTE1 FOR XML PATH('')

 

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 7

Thanks,

I did not test it in this state!

---

Saeid Hasani, sqldevelop.wordpress.com

Download Books Online for SQL Server 2012

Answer 8

When I tried to post the link to this thread , I am also getting the same error !!.

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 9

Hello Kalman,

I was able to...please check the post and see my ID there,but my comment should be removed

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 10

It takes "hello". Not nearly sufficient. I cannot remove it. Maybe you can.

Can you post what I was going to post if you agree with it?   Thanks.

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 11

Hi,

Also can any one merge this with current thread , discussions about the same topic .

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 12

Hi SathyanarrayananS,
 
Thank you for your post.

For submitting a feedback to Microsoft such issues, I would recommend to submit it to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.

Best Regards,
Allen Li

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.

Answer 13

Hi SathyanarrayananS,
 
Thank you for your post.

For submitting a feedback to Microsoft such issues, I would recommend to submit it to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.

Best Regards,
Allen Li

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.

Hi Allen Li ,

Thanks for jumping in and replying .

First of all , I am not sure why Kalman Toth , opened two new threads discussing about the same topic , he could have proceeded the discussion with my OP .

Allen Li ,

Just a clarification , I am not talking about a bug or a feedback for any improvements in CTE .

I found a statement in BOL about CTE :

The following clauses cannot be used in the CTE_query_definition:

• FOR XML

But it can be used if you see my previous or Kalman's post for an example .

Can I create a connect for this (for a change in BOL documentation) , also when Kalman and myself tried to post about this on BOL Community Additions , we couldn't post , you can check the screenshot in Kalman 's post .

If the statement "FOR XML clause can be used in the CTE query definition" is correct , then kindly suggest me on what should be done to correct the same on BOL .

Let me close this thread ASAP before people open many too many threads for discussing about the  same topic :^)

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 14

Hi SathyanarrayananS,

I suggest also you open a Connect case.

Thanks.

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 15

Thanks Kalman , Still I am unable to add the comments under Community Additions of BOL .

Here is the Connect case .

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.