ODBC, SQL Server, "FOR XML" & Cursors error

Hi,


Has anybody had any luck using the Microsoft SQLServer ODBC drivers (Driver version: 03.81.9030, ODBC version : 03.52)

to connect to a SQL Server 2000 database and passing in a SELECT with a FOR XML clause. I keep

getting Error [Microsoft][ODBC SQL Server Driver][SQL Server]The FOR XML clause is not allowed in a CURSOR statement

., State: 42000, Error: 6819.

Is there a work around (eg putting the select in a stored procedure) or am I missing something ?

I am using SQLExecDirect() to execute the statement.

Or could any one suggest a better way to retrieve data from a standard table in XML formated string?


Please Help!!If you are using a cursor or a loop (it may actually work in a loop, but methinks NOT), then create a stored procedure that contains just SELECT...FOR XML AUTO/RAW/whatever. But I don't see a point doing that because further processing of the data retrieved in XML format within a stored procedure is not supported. Check this site for general guidelines on using FOR XML clause:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_0alh.asp