For XML -> ADO Recordset

I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
results of a SELECT...FOR XML statement. The SQL I use is:
http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
FOR XML AUTO,XMLDATA&root=Policies
This returns the Schema and Data, which I capture in strXML, and try to load
as follows:
Dim rs As New ADODB.Recordset
Dim s As New ADODB.Stream
...
s.Open
s.WriteText strXML
s.Position = 0
rs.Open s
I get the following Error on the last line:
"Recordset cannot be created from the Specified source. The source file or
stream must contain recordset data in XML if ADTG format."
I have also tried to save the XML to a file, and then do the following:
rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
This gives me the following error:
Recordset cannot be created. Source XML is incomplete or invalid.
The XML from the file loads into IE without error.
Is what I am trying to do possible without much trouble, or will I need to
use MSXML? Any FAQs or articles you could point me to would be appreciated.
TIA
Mike
Mike see my response to "A Mindboggingly simple question" Below
Basically this should do what you want
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
Obviously if you dont want to persist it you can simply stream the oDom.xml
Hope this helps
Graham
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
> results of a SELECT...FOR XML statement. The SQL I use is:
> http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
> FOR XML AUTO,XMLDATA&root=Policies
> This returns the Schema and Data, which I capture in strXML, and try to
load
> as follows:
> Dim rs As New ADODB.Recordset
> Dim s As New ADODB.Stream
> ...
> s.Open
> s.WriteText strXML
> s.Position = 0
> rs.Open s
> I get the following Error on the last line:
> "Recordset cannot be created from the Specified source. The source file
or
> stream must contain recordset data in XML if ADTG format."
> I have also tried to save the XML to a file, and then do the following:
> rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
> This gives me the following error:
> Recordset cannot be created. Source XML is incomplete or invalid.
> The XML from the file loads into IE without error.
> Is what I am trying to do possible without much trouble, or will I need to
> use MSXML? Any FAQs or articles you could point me to would be
appreciated.
> TIA
> --
> Mike
>
|||Graham:
I tried it, and am getting an error still. The Code is as follows:
Dim oCmd As Command
Dim oDom As IXMLDOMDocument2
Dim rs As New ADODB.Recordset
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
' Added next line to add a root node
oCmd.Properties("xml root") = "root"
oCmd.Execute , , 1024
oDom.save "f:\junk\results.xml"
' I get error "Recordset cannot be created. Source XML is incomplete or
invalid." on next line (err # -2147467259)
' although the xml loads into IE
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
Employees_sp source:
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees FOR XML AUTO, XMLDATA
I am using ADO 2.8
Any thoughts?
Thanks
Mike
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> Mike see my response to "A Mindboggingly simple question" Below
> Basically this should do what you want
> Sub SaveXml()
> Dim oCmd As Command
> Dim oPrm As Parameter
> Dim oDom As IXMLDOMDocument2
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
> Source=."
> oCmd.CommandText = "SQL_First"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> oCmd.Execute , , 1024
> oDom.Save "c:\temp\results.xml"
> End Sub
> Obviously if you dont want to persist it you can simply stream the
oDom.xml[vbcol=seagreen]
> Hope this helps
> Graham
> "Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
> news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
PolicyID='FPHM016182'[vbcol=seagreen]
> load
> or
to
> appreciated.
>
|||Mike,
The xml you are getting is not a persisted recordset it is simply pure xml
therefore you can't load it into a recordset. If all you want is a recordset
then just use a plain sp e.g.
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees
Dim oCmd As Command
Dim rs As New ADODB.Recordset
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
set rs=oCmd.Execute( )
then you can save the resulting recordset as xml with
rs.save "f:\junk\result.xml", 1
rs.close
and later do
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:uR922v%23cEHA.3632@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Graham:
> I tried it, and am getting an error still. The Code is as follows:
> Dim oCmd As Command
> Dim oDom As IXMLDOMDocument2
> Dim rs As New ADODB.Recordset
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
> "Security=SSPI;Persist Security Info=False;Initial
> Catalog=Northwind;Data " & _
> "Source=tcs2003s"
> oCmd.CommandText = "Employees_sp"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> ' Added next line to add a root node
> oCmd.Properties("xml root") = "root"
> oCmd.Execute , , 1024
> oDom.save "f:\junk\results.xml"
> ' I get error "Recordset cannot be created. Source XML is incomplete or
> invalid." on next line (err # -2147467259)
> ' although the xml loads into IE
> rs.Open "f:\junk\results.xml", "Provider=MSPersist"
> Employees_sp source:
> CREATE PROCEDURE Employees_sp
> AS
> SELECT * FROM Employees FOR XML AUTO, XMLDATA
> I am using ADO 2.8
> Any thoughts?
> Thanks
> --
> Mike
> "Graham Shaw" <Graham@.somewhere.com> wrote in message
> news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> oDom.xml
> PolicyID='FPHM016182'
to[vbcol=seagreen]
file[vbcol=seagreen]
following:[vbcol=seagreen]
need
> to
>