SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / 'ROOT' directive in SELECT query / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 03:40:25 GMT20RE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxAll,Thanks for your valuable inputs. I Asked DBA's to change compatability level to 90 but he did not agree as he is not sure if that would cause any other issues.I created a new test database in SQL Server 2005 and was able to use ROOT command in that database. Other database (created using SQL Server 2000) on same server gives error.Thu, 20 Dec 2007 11:23:38 GMTRamesh LendeRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxRamesh,I do not know if you have resolved this issue, but the reason we want you to check your compatibility level is because the new SQL 2005 features are not available in compatability mode 80.Thu, 20 Dec 2007 11:10:23 GMTAdam HainesRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxI am not having a problem and here is test case.[code]select database_id,namefrom master.sys.databaseswhere database_id <= 5FOR XML AUTO, ELEMENTS, ROOT('databases')[/code]Thu, 20 Dec 2007 08:20:21 GMTCarl FederlRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspx[quote]Check that the compatability mode of the database you're running this in is set to 90 [/quote]Agreed.Wed, 19 Dec 2007 15:40:50 GMTAdam HainesRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxI tried with RAW this also fails...SELECT * FROM customer FOR XML RAW, ELEMENTS, root('customers')Wed, 19 Dec 2007 09:54:53 GMTRamesh LendeRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxCheck that the compatability mode of the database you're running this in is set to 90Wed, 19 Dec 2007 09:54:35 GMTGilaMonsterRE: 'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxThe "ROOT" keyword is only available with the "RAW" option and is not available with the "AUTO" option.Wed, 19 Dec 2007 09:52:21 GMTCarl Federl'ROOT' directive in SELECT queryhttp://www.sqlservercentral.com/Forums/Topic434837-338-1.aspxHello,I am trying to create an xml from a table with tags for each field in the table. Basically i am trying to run following query in SQL Server 2005SELECT * FROM customer FOR XML AUTO, ELEMENTS, ROOT('customers')This gives an error. Line 2: Incorrect syntax near 'ROOT'.I think ROOT command is correct so not sure where the problem is. Do i need to change any settings on SQL Server?Thanks,Ramesh.Wed, 19 Dec 2007 09:39:41 GMTRamesh Lende