Nigel,
That's probably because you've gone back to using just a single piece of xml
in the result set (don't forget that I am returning multiple snippets of xml)
The way I managed to get this working in the end (in another posting) is this:
- use a sqlxml command
- make sure to put placeholders for each parameter in the commandtext (this
is crap as it does not appear documented) e.g. "select_bananas ?,?"
- put the result into a stream (putting it into an xmlreader fails with
invalid xml)
- load the alleged 'invalid xml' in the stream into a dom
it works
i don't understand why
thanks for trying though
i hate microsoft for their poorly documented afterthought products.
not knowing about the "?,?" wasted soooo much time
in protest i have moved to firefox and have just opened a gmail account and
am recommending everyone i know to do the same :]
Let me know if you do ever manage to get it working with a manually crafted
bit of xml.
"Nigel Armstrong" wrote:
Hi Adolf
I've just tried adding an input parameter to the SP, and modified the code
and it still works...
Nigel
SP:
ALTER PROCEDURE dbo.test
(
@id int,
@test int OUTPUT
)
AS
SELECT @test = 23
SELECT Food FROM FavouriteFoods WHERE id = @id FOR XML AUTO
RETURN 34
Code:
Me.SqlConnection1.Open()
Me.SqlCommand1.Parameters("@id").Value = InputBox("Tell Me",
"Number", "2")
Dim x As Xml.XmlReader = Me.SqlCommand1.ExecuteXmlReader()
MessageBox.Show(Me.SqlCommand1.Parameters("@test") .Value)
MessageBox.Show(Me.SqlCommand1.Parameters("@RETURN _VALUE").Value)
Dim d As New Xml.XmlDocument
d.Load(x)
MessageBox.Show(d.OuterXml)
Me.SqlConnection1.Close()
"adolf garlic" wrote:
New problem
The solution you supplied fails when the stored procedure in question has
input parameters.
The result that comes back now is
Invalid command sent to ExecuteXmlReader. The command must return an Xml
result
"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml
In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()
----CODE END
I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)
Does anyone know why the result is being lost?
Thanks