Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

'ROOT' directive in SELECT query Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2007 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 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
Posted Wednesday, December 19, 2007 9:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
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
Posted Wednesday, December 19, 2007 9:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 39,879, Visits: 36,227
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
Posted Wednesday, December 19, 2007 9:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 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
Posted Wednesday, December 19, 2007 3:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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
Posted Thursday, December 20, 2007 8:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
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
Posted Thursday, December 20, 2007 11:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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
Posted Thursday, December 20, 2007 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 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 »

Add to briefcase

Permissions Expand / Collapse