sql query result in xml?

Question

Hi,
I have a sql query which works fine but I don't know how to save it as xml. Can anyone please help.

sql="SELECT ID,Name, FROM User for XML"

it gives me the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'XML'.

Answer 1

This applies to MS SQL Server 2005;
First a simple query such as:
"SELECT ID, Name FROM User FOR XML AUTO" would create a XML query for you;
You could of course create a XML Datatype which takes the result such as:
DECLARE @result AS XML;
SET @result = (SELECT ID, Name FROM User FOR XML AUTO);
Remember that the Root Node and the XML Declaration are not created by default; so you need to add them to the stream;
My suggestion is that you create a CLR application; create a stored procedure which returns the XML Query; then in your .NET CLR Application, you create a StreamWriter which will add the XML Declaration Line and the Root Node to the stream, append the stored procedure output to the stream and finally closes the stream by adding the closing Tag for the XML Root Node.

Answer 2

sql="SELECT ID,Name FROM User for XML AUTO"

there is no comma after Name, and try adding AUTO/RAW at the end.

Answer 3

You can't use "for" as part of a field name