reader.GetSqlXml(0)需要在数据库中指定数据列为xml类型

问题描述：

Hello All,
probably a very simple solution to this but i am having a problem. I
have 2 tables that contain data, the second table joins to the first
table, neither of these tables contains XML however i have created a
select statement that returns the data i want in XML format. This works
fine in Query analyzer.
The problem i am having is that when i actually try to run this
statement in visual studio i get all kinds of errors and i have no idea
what i am doing wrong and i can't find any information on the internet
to help me.
If i select it as a string and then convert it to an xml document in
code my string is actually cut off and it doesn't give me the field
becuase it is rather big so i want to actually return an xml node using
the dataReader.GetSqlXml object but i get a lot of errors when i do
this... here is a code snippet of what i am doing

SqlXml temp;

.
. actually select the data out of the table.
.

SqlDataReader reader = database.ExecuteQuery();
if (reader.Read())
{
     temp = reader.GetSqlXml(0);
}

and i get this error:

Unable to cast object of type 'System.Data.SqlTypes.SqlString' to type
'System.Data.SqlClient.SqlCachedBuffer

解决方法：

Hello,

reader.GetSqlXml(...) is meant for reading columns that are of datatype Xml.
From your description this is not what you're doing. You're returning a SELECT
statement that is formatted using FOR XML (or a variant thereof). This is
coming back as an Xml stream and not a result set. You therefore need to
call database.ExecuteXmlReader() and not database.ExecuteReader (your .ExecuteQuery()
sample is probably a typo). An example is attached below:

public void CanLoadXml()
{
    using(SqlConnection conn = new SqlConnection("Server=localhost;Integrated
Security=SSPI"))
    using(SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "select * from sysobjects for xml raw";
        cmd.CommandType = CommandType.Text;

        conn.Open();
        using(XmlReader reader = cmd.ExecuteXmlReader())
        {
            Console.WriteLine(new XPathDocument(reader).CreateNavigator().OuterXml);
        }
    }
}

Cheers,
Stefan Delmarco