'ROOT' directive in SELECT query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » 'ROOT' directive in SELECT query

'ROOT' directive in SELECT query

Rate Topic

Display Mode

Topic Options

Author

Message

Ramesh Lende

Posted Wednesday, December 19, 2007 9:39 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104

Hello,
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 2005

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

Post #434837

Carl Federl

Posted Wednesday, December 19, 2007 9:52 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,224, Visits: 4,094

The "ROOT" keyword is only available with the "RAW" option and is not available with the "AUTO" option.

SQL = Scarcely Qualifies as a Language

Post #434851

GilaMonster

Posted Wednesday, December 19, 2007 9:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 38,086, Visits: 30,381

Check that the compatability mode of the database you're running this in is set to 90

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #434855

Ramesh Lende

Posted Wednesday, December 19, 2007 9:54 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104

I tried with RAW this also fails...

SELECT * FROM customer FOR XML RAW, ELEMENTS,
root('customers')

Post #434856

Adam Haines

Posted Wednesday, December 19, 2007 3:40 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 2,278, Visits: 3,011

Check that the compatability mode of the database you're running this in is set to 90

Agreed.

My blog: http://jahaines.blogspot.com

Post #435031

Carl Federl

Posted Thursday, December 20, 2007 8:20 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,224, Visits: 4,094

I am not having a problem and here is test case.

select database_id,name
from master.sys.databases
where	database_id <= 5
FOR XML AUTO, ELEMENTS, ROOT('databases')

SQL = Scarcely Qualifies as a Language

Post #435222

Adam Haines

Posted Thursday, December 20, 2007 11:10 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 2,278, Visits: 3,011

Ramesh,

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.

My blog: http://jahaines.blogspot.com

Post #435325

Ramesh Lende

Posted Thursday, December 20, 2007 11:23 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104

All,
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.

Post #435333

« Prev Topic | Next Topic »

Permissions