SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


'ROOT' directive in SELECT query


'ROOT' directive in SELECT query

Author
Message
Ramesh Lende
Ramesh Lende
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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.
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3248 Visits: 4350
The "ROOT" keyword is only available with the "RAW" option and is not available with the "AUTO" option.

SQL = Scarcely Qualifies as a Language
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87173 Visits: 45270
Check that the compatability mode of the database you're running this in is set to 90

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Ramesh Lende
Ramesh Lende
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 104
I tried with RAW this also fails...

SELECT * FROM customer FOR XML RAW, ELEMENTS,
root('customers')
Adam Haines
Adam Haines
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3174 Visits: 3135
Check that the compatability mode of the database you're running this in is set to 90


Agreed.



My blog: http://jahaines.blogspot.com
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3248 Visits: 4350
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
Adam Haines
Adam Haines
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3174 Visits: 3135
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
Ramesh Lende
Ramesh Lende
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search