none
sql server 解决了xml 列用sqldatareader读取时2033长度的问题了吗? RRS feed

答案

  • I believe this issue (The XML data row is truncated at 2,033 characters when you use the SqlDataReader object) was fixed according to my test as following:

     

    On SQL 2008 R2 Aug CTP, with a test table that consists tens of thousands of characters.

    Run:

    SELECT  LEN(varstring) FROM [testDB].[dbo].[Table_1] for xml auto, elements 

    SELECT sum(LEN(varstring)) FROM [testDB].[dbo].[Table_1] for xml auto, elements

    Returns:

    XML_F52E2B61-18A1-11d1-B105-00805F49916B

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    <testDB.dbo.Table_1>107870</testDB.dbo.Table_1><testDB.dbo.Table_1>93389</testDB.dbo.Table_1><testDB.dbo.Table_1>88861</testDB.dbo.Table_1><testDB.dbo.Table_1>11</testDB.dbo.Table_1>

     

    (4 row(s) affected)

     

    XML_F52E2B61-18A1-11d1-B105-00805F49916B

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    <testDB.dbo.Table_1>290131</testDB.dbo.Table_1>

     

    (1 row(s) affected)

     

    Code:

                string sqlstr = "SELECT * FROM [testDB].[dbo].[Table_1] for xml auto, elements";

                StringBuilder sbXM = new StringBuilder();

                using (SqlConnection sc = new SqlConnection("Data Source=xxx; Initial Catalog=testDB; Integrated Security=SSPI;"))

                {

                    SqlCommand cmd = new SqlCommand(sqlstr, sc);

                    sc.Open();

                    SqlDataReader sdr = cmd.ExecuteReader();

                    sdr.Read();

                    while (sdr.Read())

                    {

                        sbXM.Append(sdr.GetString(0));

                    }

                    Console.WriteLine("length:" + sbXM.Length.ToString());

                    Console.ReadLine();

                    sdr.Close();

                }

    Result:

                    length:288357

     


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    • 已标记为答案 uucharge 2010年2月13日 1:46
    2010年2月11日 8:47

全部回复

  • Hi,
    Sorry, I don't know what you want, could you give me more information?  And could you tell me why not use ExecuteXmlReader?
    I checked the link which is posted by you, the link has give us the solution.
    2010年2月1日 7:32
    版主
  • Hi,
    Sorry, I don't know what you want, could you give me more information?  And could you tell me why not use ExecuteXmlReader?
    I checked the link which is posted by you, the link has give us the solution.
    我想这样:
    SqlDataReader rdr = command.ExecuteReader();
    while (rdr.Read())
    {
       
    SqlXml myXML = rdr.GetSqlXml(1);
    ...................
    }

    不知这样写受不受那个bug的影响?

    另外如果用ExecuteXmlReader读取的是For Xml存储过程以xml形式返回的数据而不是行集,
    如果我有这样的一个数据类:
    public class mydata
    {
         public string ID{get;set;}
         public SqlXml xmldata{get;set}
         .........其他属性
    }
    当我用ExecuteXmlReader读取的时候需要写另外的转换代码将xml形式的数据转换成实体形式的集合(这不多此一举吗)
    :)

    逐微软者
    2010年2月3日 6:33
  • I believe this issue (The XML data row is truncated at 2,033 characters when you use the SqlDataReader object) was fixed according to my test as following:

     

    On SQL 2008 R2 Aug CTP, with a test table that consists tens of thousands of characters.

    Run:

    SELECT  LEN(varstring) FROM [testDB].[dbo].[Table_1] for xml auto, elements 

    SELECT sum(LEN(varstring)) FROM [testDB].[dbo].[Table_1] for xml auto, elements

    Returns:

    XML_F52E2B61-18A1-11d1-B105-00805F49916B

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    <testDB.dbo.Table_1>107870</testDB.dbo.Table_1><testDB.dbo.Table_1>93389</testDB.dbo.Table_1><testDB.dbo.Table_1>88861</testDB.dbo.Table_1><testDB.dbo.Table_1>11</testDB.dbo.Table_1>

     

    (4 row(s) affected)

     

    XML_F52E2B61-18A1-11d1-B105-00805F49916B

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    <testDB.dbo.Table_1>290131</testDB.dbo.Table_1>

     

    (1 row(s) affected)

     

    Code:

                string sqlstr = "SELECT * FROM [testDB].[dbo].[Table_1] for xml auto, elements";

                StringBuilder sbXM = new StringBuilder();

                using (SqlConnection sc = new SqlConnection("Data Source=xxx; Initial Catalog=testDB; Integrated Security=SSPI;"))

                {

                    SqlCommand cmd = new SqlCommand(sqlstr, sc);

                    sc.Open();

                    SqlDataReader sdr = cmd.ExecuteReader();

                    sdr.Read();

                    while (sdr.Read())

                    {

                        sbXM.Append(sdr.GetString(0));

                    }

                    Console.WriteLine("length:" + sbXM.Length.ToString());

                    Console.ReadLine();

                    sdr.Close();

                }

    Result:

                    length:288357

     


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    • 已标记为答案 uucharge 2010年2月13日 1:46
    2010年2月11日 8:47