使用OleDbDataReader读取从SQL Server XML [英] Read XML from SQL Server using OleDbDataReader

问题描述

我卡试图使用的OleDb从SQL Server读取XML数据。

I'm stuck trying to read XML data from SQL Server using OleDb.

private static void Main(string[] args){
   var con = new OleDbConnection("Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Temp");
   var cmd = new OleDbCommand(
                "SELECT [Id] ,[Description] FROM [Temp].[dbo].[SomeTable] where [Id]= 1 for xml path, root('root')", con);

   con.Open();
   byte[] result = null;

   OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

   while (reader.Read()){
       result = (byte[]) reader[0];
   }

   MemoryStream stream = new MemoryStream(result);
   stream.Position = 0;

   XmlDocument doc = new XmlDocument();
   doc.Load(stream);

   Console.Out.WriteLine(doc.OuterXml);
}

它没有说，数据格式不正确。如果我的字节数组转换为字符串，我看到了很多怪字。我做错了什么？

It fails saying that data is malformed. If I convert the byte array to string I see a lot of "strange " characters. What I'm doing wrong?

推荐答案

由于该结果是直接的XML我相信你正面临issue.You需要得到的结果行集，而不是标量。 读为字符串，可以使用loadXML的，而不是流。

Since the result is direct XML I believe you are facing issue.You need to get the result in row-set instead of scalar. Read as String, Use LoadXML instead of stream.

下面是code我变了。

Below is the code I changed.

    private static void Main(string[] args)
    {
        var con = new OleDbConnection("Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Temp");
        var cmd = new OleDbCommand(
                     "Select (SELECT [Id] ,[Description] FROM [Temp].[dbo].[SomeTable] where [Id]= 1 for xml path, root('root')) AS XML", con);

        con.Open();
        string result = string.Empty;

        OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        while (reader.Read())
        {
            result = reader[0].ToString();
        }
        con.Close();

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(result);

        Console.Out.WriteLine(doc.OuterXml);
    }

这篇关于使用OleDbDataReader读取从SQL Server XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！