exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
ThanksHere's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
" SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks