none
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 **.



    Saturday, October 12, 2013 4:21 AM

Answers

All replies