SQL Server 2000
I'm using the code listed below to export a table to an xml file but the
resulting xml is not structured as I need

The code writes the following xml structure
<ROOT>
<Prova field1="xx" field2="yy"...>
<Prova field1="xx" field2="yy"...>
</ROOT>

But I need the following

<ROOT>
<ROW value=1>
<FIELD1>xx</FIELD1>
<FIELD2>yy</FIELD2>
</ROW>
<ROW value=2>
<FIELD1>xx</FIELD1>
<FIELD2>yy</FIELD2>
</ROW>
</ROOT>

Do you have any suggestions?
Thanks


This is the script I'm using
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim oCmd, sSQL, oDom

''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Microsoft.XMLDOM")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection =
"PROVIDER=SQLOLEDB;SERVER=sql.intra.net;UID=sa;PWD =login4sa;DATABASE=Chelab;
"

sSQL = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>"
& _
"select * from prova for xml auto</sql:query></ROOT>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "c:\denis.xml"

Main = DTSTaskExecResult_Success

End Function