How to save multiple recordsets from SP as separate XML files (SQL

Hi All,

Could anyone help me with the problem described in subject?

I tried two approaches (both with ADO and VB6):
1. Using SQLXML 3.0 provider for SQL Server
2. Using functionality that saves recordset as XML (Save method with
adPersistXML)

First approach gives me with one (instead of separate) XML file. And it
constraints me by requirement to use FOR XML clause.

Second one was unsuccessful because it:
1. Returnes recordsets as BLOBs instead of XML files despite I'm returning
them with FOR XML RAW clause. And this is confusing... it seems that ADO has
a bug.
2. Rises an exception when I try to save second recordset with adPersistXML.

Below is the code I use for these exercises:

Private Sub Command1_Click()
Dim oRst As ADODB.Recordset
Dim oCmd As ADODB.Command
Dim sResponseStream As Stream


Set oCmd = New ADODB.Command
Set sResponseStream = New Stream
sResponseStream.Open

oCmd.ActiveConnection = "provider=SQLXMLOLEDB.3.0;data
provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Initial
Catalog=test;Data Source=SPB-LUX-018\SQL2K"
oCmd.CommandText = "exec TestXML"
oCmd.Properties("ClientSideXML") = "True"
oCmd.Properties("Output Stream").Value = sResponseStream
oCmd.Properties("xml root") = "root"
oCmd.Execute , , adExecuteStream
Call sResponseStream.SaveToFile("C:\\test1.xml", adSaveCreateOverWrite)
Set oCmd = Nothing

Set oCmd = New ADODB.Command
oCmd.ActiveConnection = "provider=SQLOLEDB;Persist Security
Info=False;User ID=sa;Initial Catalog=test;Data Source=SPB-LUX-018\SQL2K"
oCmd.CommandText = "exec TestXML"
'oCmd.Properties("Output Stream").Value = sResponseStream
Set oRst = oCmd.Execute
oRst.MoveFirst
MsgBox CStr(oRst.Fields.Item(0).Name) & " = " & vbCrLf &
CStr(oRst.Fields.Item(0).Value)
oRst.Save sResponseStream, adPersistXML
Call sResponseStream.SaveToFile("C:\\test2_1.xml", adSaveCreateOverWrite)
Set oRst = oRst.NextRecordset
oRst.MoveFirst
MsgBox CStr(oRst.Fields.Item(0).Name) & " = " & vbCrLf &
CStr(oRst.Fields.Item(0).Value)
oRst.Save sResponseStream, adPersistXML
Call sResponseStream.SaveToFile("C:\\test2_2.xml", adSaveCreateOverWrite)
oRst.Close

End Sub



CREATE PROCEDURE TestXML AS
select 1 as RstNo1, * from Table1 for XML RAW, BINARY BASE64
select 2 as RstNo2, * from Table1 for XML RAW, BINARY BASE64
GO


.