xml - ADO 无法使用 native 客户端 (SQLNCLI) 访问 SQL Server XML 类型的列

Question

我正在使用 ADO 和其中一个“ native ” 驱动程序(例如 SQLNCLI、SQLNCLI10、SQLNCLI11) 连接到 SQL Server(而不是遗留的 SQLOLEDB 驱动程序)。

ADO 不理解 native 驱动程序公开的 XML SQL Server 数据类型:

field: ADOField;

field := recordset.Fields.Items["SomeXmlColumn"];

尝试访问 field.Value 会抛出一个 EOleException:

• 来源:微软游标引擎
• 错误代码: 0x80040E21 (E_ITF_0E21)
• 消息:多步操作产生错误。检查每个状态值

native 客户端驱动程序(例如 SQLNCLI、SQLNCLI10、SQLNCLI11)呈现一个 Xml 数据类型为 ADO 为

field.Type_ = 141 //???

而旧版 SQLOLEDB 驱动程序将 Xml 数据类型作为 adLongVarWChar 呈现给 ADO ，一个 unicode 字符串:

field.Type_ = 203 //adLongVarWChar

并且 field.Value 中包含的 VARIANT 是一个 WideString (technically known as a BSTR) :

TVarData(field.Value).vtype = 8 //VT_BSTR

在我看来，这是 ADO (Windows 7 SP1) 中的错误，我无法修复。

我该如何解决？

红利阅读

• Delphi with SQL Server: OLEDB vs. Native Client drivers

Answer 1

MSDN documentation说

To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the SQL Server Native Client OLE DB provider which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005: xml and udt. These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. SQL Server Native Client adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. In addition, the SQL Server Native Client OLE DB provider also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.

因此要在 Native Client 中启用新的 SQL Server 功能，您需要在连接字符串中添加以下关键字:

Provider=SQLNCLI11
DataTypeCompatibility=80

哪里DataTypeCompatibility :

Specifies the mode of data type handling to use. Recognized values are "0" for provider data types and "80" for SQL Server 2000 data types.