Solved

"incorrect syntax near 'XML'"

Posted on 2001-08-24
12
918 Views
Last Modified: 2008-02-01
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'"
0
Comment
Question by:EasyAim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:dredge
ID: 6423267
"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.
0
 

Author Comment

by:EasyAim
ID: 6423337
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)

0
 
LVL 5

Expert Comment

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

ConnADO.Provider= ????
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:EasyAim
ID: 6423703
I'll look into it....  but I don't recall every reading anything about that.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6423721
This syntax will not work in SQL 7.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6423734
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.

Anthony
0
 
LVL 5

Expert Comment

by:dredge
ID: 6423736
it doesn't matter what data source you are using.

ADO is the doing the translating, not SQL Server.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6423793
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6423797
Dredge,

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

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

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

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

< query specification > ::=
    SELECT [ ALL | DISTINCT ]
        [ { 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 > ]

0
 

Author Comment

by:EasyAim
ID: 6423843
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.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 55 total points
ID: 6423908
EasyAim

"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.

Anthony
0
 
LVL 3

Expert Comment

by:ibro
ID: 6431867
Agree with Anthony.
 FOR XML is supported by SQL 2000, but not in SQL7.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question