Typed XML in ADO.Net v2.0

  • Article

In the earlier sections, we have been looking at Untyped XML which are different from Typed XML. Typed XML data type is an XML defined with an associated xml schema in Sql Server 2005. You may ask if there is anything different on how Typed XML are exposed via ADO.Net 2.0. The answer is a heart-warming NO!. Typed XML are exposed the same way that any untyped XML does through SqlDataReader and SqlParameter. Please note that the check to see if the given XML adheres to the give schema is not done on the client side. This check is done when the statement is executed on the server side.

There are new set of columns added to the DataTable returned from SqlDataReader.GetSchemaTable() method that give extra information pertaining to the associated XML Schema. The three columns that are added are:

  1. XmlSchemaCollectionDatabase:Provides the database in which the schema for this XML type exists.
  2. XmlSchemaCollectionOwningSchema:Provides the owning relational schema where the schema for this XML instance is located
  3. XmlSchemaCollectionName: Provides the name of the schema for this XML type. 

Consider the typed XML is defined with the following TSQL:
-- Change to Library database
USE Library
-- OrderSchema is defined using the CREATE XML SCHEMA COLLECTION statement
CREATE XML SCHEMA COLLECTION OrderSchema AS
N'XSD_DEFINITION_HERE'--Consider the Schema/namespace
-- Customer table is created with a Typed XML column name OrderXml
CREATE TABLE Customer (CustomerId int, CustomerName varchar(40), OrderXml xml(OrderSchema))

The three-part name of the embedded XML schema can be got as follows:
sqlcommand1.CommandText = "SELECT OrderXml from Customer";
SqlDataReader sqldatareader1 = sqlcommand1.ExecuteReader();
DataTable datatable1 = sqldatareader1.GetSchemaTable(); // SchemaTable
Console.WriteLine(datatable1.Rows[0]["XmlSchemaCollectionDatabase"]); //returns Library
Console.WriteLine(datatable1.Rows[0]["XmlSchemaCollectionOwningSchema"]); //returns dbo
Console.WriteLine(datatable1.Rows[0]["XmlSchemaCollectionName"]); //retuns OrderSchema

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights