ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--