Iv. client-side XML processing in SQL Server 2005
Clients of XML data types support Ado.net XML support in the. NET Framework V2.0
In the System.Data.SqlTypes namespace of the Sqldatareader.getsqlxml () method, the XML data type is exposed as a class SqlXml. You can use the Sqlxml.createreader () function to obtain XmlReader from the SqlXml object.
The name of the XML schema collection of a typed XML column is composed of three parts, which can be obtained from the metadata of the XML column (by using the getschematable () or getsqlmetadata (int) in the SqlDataReader object) as three properties , representing the names of the database (XmlSchemaCollectionDatabase), the relational schema (Xmlschemacollectionowingschema), and the XML schema Collection (XmlSchemaCollectionName), respectively.
The client can retrieve the XML schema from the server using the new schema rowset XmlSchema. The XMLSCHEMA rowset contains three columns of the XML schema collection, the target namespace, and the XML schema content itself.
The following example shows the skeleton code for managed access to the XML data type.
Example: in-process access to XML data types
The following C # code demonstrates how to access an XML data type from an in-process provider. Code that is used for SQL client access needs to be appropriately altered in-process access.
using System;
using System.Xml;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlServer;
class xmldtADONETReadAccessInProc
{
static void ReadXmlDataType () {
// in-proc connection to server
SqlConnection conn = SqlContext.GetConnection();
// prepare query to select xml data
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT xCol FROM docs";
// execute query and retrieve incoming data
SqlDataReader r = cmd.ExecuteReader();
r.Read();
// access XML data type field in rowset
SqlXml xml = r.GetSqlXml(0);
new XmlTextWriter(Console.Out).WriteNode(
xml.CreateReader(), true);
}
}
Example: Updating an XML data type column with a SQL client provider