4

I have a SQL Server data table that has an xml column. I run a select query using DataTable (Fill method of the SqlDataAdapter class). After execution, the type of the column is string.

I am wondering how I could determine the actual SQL Server datatype (in this case xml) from the DataTable materialized by the Fill method.

I cannot use SqlDataReader (which has GetSchemaTable method that returns the SQL Server datatype information) - I have to use SqlDataAdapter/DataTable. Doing something like:

DataTableReader reader = new DataTableReader(table);
DataTable schemaTable = reader.GetSchemaTable();

is also not helpful since it also does not seem to contain the SQL Server datatype information.

Chris Moutray
  • 18,029
  • 7
  • 45
  • 66
user1417808
  • 61
  • 4
  • You could always "inspect" the column's datatype in the SQL Server catalog views by executing something like this: `SELECT user_type_id FROM sys.columns WHERE object_id = OBJECT_ID('Person.Person')`. If that value is `241` - it's a column of SQL Server datatype `XML` (those types are defined in `sys.types`) – marc_s May 25 '12 at 19:21
  • That would mean I have to issue another query. Any way to not go to sql but rely on the obtained data table? Additionally, I could be executing a stored proc that returns data and not know apriori that table/schema it belongs to – user1417808 May 25 '12 at 19:28

1 Answers1

5

There is a property on the SqlDataAdapter called ReturnProviderSpecificTypes and if you set this true, then GetSchemaTable() should tell you the column is typeof(SqlXml) rather than typeof(string)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sgmoore
  • 15,694
  • 5
  • 43
  • 67