CLR and Xml results

[Dan Holmes]

I am trying to get XML data back from a stored proc. This proc works in SSMS but the same code in a CLR proc doesn't
want to return data or i am trying to access the data incorrectly.

HasValue is always false in the following code. You can see i have tried to write this as many ways as i can think of.
All of them have yielded the same results.

I do have another piece of SQL that just returns a dataset that works. This is a context connection.

SqlCommand agencyGlobalCmd = con.CreateCommand();
//agencyGlobalCmd.CommandType = CommandType.StoredProcedure;
//agencyGlobalCmd.CommandText = "spGetXmlRSEGlobals";
////ado.net, unlike ado 2.8 requires the parameter names be the same between the parameter defintions
////and the actual parameter names in the proc.
//agencyGlobalCmd.Parameters.Add("@ContextStr", SqlDbType.VarChar, 255).Value = ctx;
//agencyGlobalCmd.Parameters.Add("@dEpoch", SqlDbType.DateTime).Value = timeAtStart.Value.Date;
//agencyGlobalCmd.Parameters.Add("@dEffectiveStartDate", SqlDbType.DateTime).Value = timeAtStart.Value;
//agencyGlobalCmd.Parameters.Add("@dEffectiveEndDate", SqlDbType.DateTime).Value =
timeAtStart.Value.Date.AddDays(1);
//agencyGlobalCmd.Parameters.Add("@bMinimalRestrictions", SqlDbType.Bit).Value = 0;
//agencyGlobalCmd.Parameters.Add("@AgencyID", SqlDbType.SmallInt).Value = agencyID;
agencyGlobalCmd.CommandType = CommandType.Text;
agencyGlobalCmd.CommandText =
String.Format("EXEC spGetXMLRSEGlobals '" + ctx + "', '"
+ timeAtStart.Value.Date.ToString("yyyyMMdd") + "', '"
+ timeAtStart.Value.ToString("yyyyMMdd hh:mm") + "', '"
+ timeAtStart.Value.Date.AddDays(1).ToString("yyyyMMdd") + "', 0, 1;"

);
using (System.Xml.XmlReader xr = agencyGlobalCmd.ExecuteXmlReader())
//using (System.Data.IDataReader xr = cmd.ExecuteReader())
{
//agencyGlobals = xr[0].ToString();
if (xr.HasValue)
{
agencyGlobals = xr.ReadInnerXml();
SqlContext.Pipe.Send("XmlGlobals = " + agencyGlobals);
}
else
{
SqlContext.Pipe.Send("XmlGlobals doesn't have a value");
foreach (SqlParameter p in agencyGlobalCmd.Parameters)
{
SqlContext.Pipe.Send(String.Format("parameter {0} value {1}", p.ParameterName, p.Value));
}
}
agencyGlobals = xr.ReadInnerXml();
}
con.Close();
SqlContext.Pipe.Send("agencyGlobals = " + agencyGlobals);

[Bob Beauchemin]

Hi Dan,

What is the SQL statement(s) in the procedure spGetXMLRSEGlobals that
returns the XML that you are trying to consume? If you're using SELECT...
FOR XML to produce it, you should be able to consume the XML as a column of
type System.Data.SqlTypes.SqlXml in a DataReader. If you're using
SELECT...FOR XML you may have to add ",TYPE" at the end of the statement,
ie, "SELECT * FROM table FOR XML AUTO" becomes "SELECT * FROM table FOR XML
AUTO, TYPE". That produces a one row, one column table with the column's
data type as XML. The data type would be SqlXml or string in the consumer.I
have my doubts about SqlCommand.ExecuteXmlReader being useable inside
SQLCLR.

Hope this helps,
Bob Beauchemin
SQLskills

"Dan Holmes" <dan.h...@routematch.com> wrote in message
news:%23SGDSh9...@TK2MSFTNGP05.phx.gbl...

[Bob Beauchemin]

One more thing that might be helpful. If what you want is an XmlReader, the
System.Data.SqlTypes.SqlXml data type has a method, GetReader() that returns
an XmlReader over the content.

Cheers,
Bob Beauchemin

"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:uhrz%23WEgJ...@TK2MSFTNGP05.phx.gbl...

[Dan Holmes]

This turned out to be a code issue. Before i could check xr.HasValue i needed to call xr.Read(). What is odd is that
it worked sometimes without the xr.Read().