creating xml file from sql server

i am very new at this xml thing, however, i know how to develop sql. i would
like to write a simple sql statement (using one table) and generate/create an
XML FILE that i can save onto my desktop. does anyone have any advice?
I have had limited success by doing...
osql -E -S(servername) -d(database name) -H-1 -Q "select * from tablename
for xml auto" -ooutput.xml
But the XML generated is rough. So therefore I posted a similiar question,
amazingly within minutes of yours...
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||Write an ADO or ADO.net based client program that opens a connection, sends
a FOR XML query over the command stream object, sets the root property on
the result stream and then pipes the result into a file.
HTH
Michael
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>i am very new at this xml thing, however, i know how to develop sql. i
>would
> like to write a simple sql statement (using one table) and generate/create
> an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||See "Retrieving and Writing XML Data" in Sql Server books online.
Also, www.sqlxml.org has some good information and examples.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Try this link...
http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||thanks to www.sqlxml.org this example works great:
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from table for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "all my connection parameters"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
Set oCmd = Nothing
%>
so once i have done this...how do you pipe the results into a file? thanks
for your patients and help. also, this takes forever to run because there
are about 50,000 records being returned...do you have any ideas for speeding
it up?
"Michael Rys [MSFT]" wrote:

> Write an ADO or ADO.net based client program that opens a connection, sends
> a FOR XML query over the command stream object, sets the root property on
> the result stream and then pipes the result into a file.
> HTH
> Michael
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>
>
|||thanks...that helped a lot...now, i just need to see some examples that pipe
the results to file. got anything up your sleeve? =)
"Chris" wrote:
[vbcol=seagreen]
> Try this link...
> http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
> "snickerskid" wrote:
|||Here are some examples:
http://www.sqlteam.com/Forums/topic...=5&CAT_ID=3&To
pic_Title=SQL+Server+2000+XML&Forum_Title=Develope r
http://www.sqlxml.org/faqs.aspx?faq=29
Andrew Conrad
Microsoft Corp