RE: .net loses sp result when calling executeXmlReader. please hel

microsoft.public.dotnet.xml

16 Nov 2004 10:07 AM

adolf garlic

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.

Show quote

"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("ConnectionString").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
> > >