"incorrect syntax near 'XML'"

I loaded the latest ADO 2.6 DLL on my computer and I'm using SQL Server 7.0 but I can't get the following to work either in the "Query Analyzer" nor in my VB project with a compiled inline SQL statement:

  "SELECT field1, field2, field3 FROM myTable FOR XML RAW"

  I get the error:

  "Incorrect syntax near 'XML'"
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

"FOR XML" will not work in Query Analyzer because QA does not use ADO - it uses a direct OLEDB connection.

you must specifically use ADO objects in order to use the FOR XML command.
EasyAimAuthor Commented:
But I'm getting the exact same error message in the VB code also upon....

 Set connADO = New ADODB.Connection
 connADO.Open strConn
  sSQL = "SELECT field1, field2, field3
  sSQL = sSQL & " FROM myTable "
  sSQL = sSQL & " FOR XML RAW"
  Set rs = connADO.Execute(sSQL, lngAffect, adCmdText)

you have to specify a certain data provider to use this. but i don't remember what it is...

ConnADO.Provider= ????
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

EasyAimAuthor Commented:
I'll look into it....  but I don't recall every reading anything about that.
Anthony PerkinsCommented:
This syntax will not work in SQL 7.
Anthony PerkinsCommented:
I guess I should have stated that the "FOR XML" will work in SQL2000.

If you have the Technology Preview for SQL 7 you can use "FOR XML" ony from IE.

it doesn't matter what data source you are using.

ADO is the doing the translating, not SQL Server.
Anthony PerkinsCommented:
I beg to differ.  This has nothing to do with ADO.  SQL 2000 includes the "FOR XML" clause in the SELECT syntax.

The workaround I was suggesting using SQL 7 does not use ADO it uses ISAPI.

Anthony PerkinsCommented:

In case you are having some difficulty tracking down the definition for Select in SQL 2000, here it is:

Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

The UNION operator can be used between queries to combine their results into a single result set.

SELECT statement ::=
    < query_expression > 
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
        [ ,...n ]    ]
        { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]
        [ BY expression [ ,...n ] ]
            [ , XMLDATA ]
            [ , ELEMENTS ]
            [ , BINARY base64 ]
    [ OPTION ( < query_hint > [ ,...n ]) ]

< query expression > ::=
    { < query specification > | ( < query expression > ) }
    [ UNION [ ALL ] < query specification | ( < query expression > ) [...n ] ]

< query specification > ::=
        [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
        < select_list > 
    [ INTO new_table ]
    [ FROM { < table_source > } [ ,...n ] ]
    [ WHERE < search_condition > ]
    [ GROUP BY [ ALL ] group_by_expression [ ,...n ]
        [ WITH { CUBE | ROLLUP } ]
    [ HAVING < search_condition > ]

EasyAimAuthor Commented:
Actually I am more interested in getting my VB/ADO stuff to work.   I am changing my code to use the

   rs.Save "filename.xml", adPersistXML

And using the DOM controls to tweak it but I was surprised that my embedded SQL statement using the "...FOR XML" passed into the ADO 2.6 returned the same error as SQL 7.0.
Anthony PerkinsCommented:

"FOR XML" is not supported in SQL 7, regardless of the version of ADO.

The only way you can get something like "FOR XML" to work with SQL 7, is if you have installed the Technology Preview for XML.  In this case you can use it from IE using ISAPI.

But to get back to your real situation.  Rather than saving it to a file and then opening it with XML DOM, you may want to take a look at the Stream object, this allows you to skip the saving to a file prior to manipulating it with XML DOM.

Let me know if you need an example.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Agree with Anthony.
 FOR XML is supported by SQL 2000, but not in SQL7.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.