Post reply

'ROOT' directive in SELECT query

  • December 19, 2007 at 9:39 am

    #182149

    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.

  • December 19, 2007 at 9:52 am

    #761463

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

    SQL = Scarcely Qualifies as a Language

  • December 19, 2007 at 9:54 am

    #761466

    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

  • December 19, 2007 at 9:54 am

    #761467

    I tried with RAW this also fails...

    SELECT * FROM customer FOR XML RAW, ELEMENTS,

    root('customers')

  • December 19, 2007 at 3:40 pm

    #761620

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

    Agreed.


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

  • December 20, 2007 at 8:20 am

    #761773

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

    select database_id,name

    from master.sys.databases

    wheredatabase_id <= 5

    FOR XML AUTO, ELEMENTS, ROOT('databases')

    SQL = Scarcely Qualifies as a Language

  • December 20, 2007 at 11:10 am

    #761856

    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

  • December 20, 2007 at 11:23 am

    #761863

    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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply