XML generation with FOR XML Auto

Consider this query in SQL Server 2000 returning XML.

select * from Mytable employee for xml auto , elements

I'm trying to create an XML file using classic ASP.

The results of the query is fetched using a recordset object. Tresult is one ro开发者_StackOverflow社区w and one column.

The resulting xml file has only first 2 row or 255 characters.

How do i make this unlimited?

Is there a way to obtain XML in multirow format from SQL Server 2000?

Here's the asp code

dim sql dim rs
Response.ContentType = "application/octet-stream"
Response.AddHeader "content-disposition", "attachment;filename="& filename

set rs = server.createobject("adodb.recordset")

sql = "select * from Mytable employee for xml auto , elements"

set rs = con.execute(sql)

response.write "<?xml version=""1.0""?>"

response.write rs(0)

change your sql to

sql = "select * from Mytable employee for xml auto , TYPE, elements"