Help! ExecuteXmlReader not returning xml in VB

[gurufordy]

Hello all. I have a stored procedure that take no parameters and just returns XML as below:

CREATE PROCEDURE [dbo].[usp_mySP]
 
AS
 
SELECT
             vchrOne,
             vchrTwo,
             vchrThree
FROM [view_mydatasource]
FOR XML AUTO

This works when i execute it in SQL Managment Studio and returns XML.

However, I want this in VB.Net 2.0 s I wrote this:

Public Function GetMyData() As XmlReader
        Dim objSQLDataReader As XmlReader
        Dim objSQLCommand As New SqlCommand
        Dim objXMLDoc As New XmlDocument
 
        Try
            mobjDataAccess.Open()
 
            objSQLCommand.CommandType = CommandType.StoredProcedure
            objSQLCommand.CommandText = "[usp_mySP]"
            objSQLCommand.Connection = mobjDataAccess
 
            objSQLDataReader = objSQLCommand.ExecuteXmlReader
 
            Return objSQLDataReader
 
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally
            mobjDataAccess.Close()
        End Try
    End Function

Returns nothing and when I debug it shows objSQLDataReader as "None".

Whats going on?

[kenobewan]

Suggests that there is something wrong with your connection...

[gurufordy]

When I was trying to figure it out I put a return parameter on there and that came back fine so it can't be the connection.

Any other guesses mate?

[gurufordy]

I just re-wrote it to return a dataset and that worked fine. Just want XML now :(

[Curtis Rutland]

Well, if you can get the data into a DataTable (which is what datasets use) you can use this method, I think.

[gurufordy]

Well, if you can get the data into a DataTable (which is what datasets use) you can use this method, I think.

Thatnks but I really want SQL to do the XML for me and return that.