ADO.NET 3.5 Cookbook, 2nd Edition

9.7. Working with a SQL Server XML Data Type Column

Problem

You need to store an XML document to an xml data type in SQL Server and subsequently retrieve that data as an XML document.

Solution

Example 9-12 shows the T-SQL statement that creates the table TableWithXmlDataType used in this solution.

Example 9-12. T-SQL statement: Create table TableWithXmlDataType

USE AdoDotNet35Cookbook
GO
CREATE TABLE TableWithXmlDataType(
    Id int NOT NULL PRIMARY KEY,
    XmlField xml NULL)

The T-SQL statement creates a table with the schema shown in Table 9-6.

Table 9-6. TableWithXmlDataType schema

Column name	Data type	Allow nulls?
Id	int	No
XmlField	xml	Yes

The solution executes a T-SQL insert command to insert a row into the table TableWithXmlDataType with the xml data type field XmlField set to a simple XML fragment <MyElement>MyValue</MyElement>. A data adapter is created and used to fill a DataTable with the values TableWithXmlDataType—the newly added row. The contents of the DataTable are output to the console, including the string representation of the value in the field XmlField. A data reader is used to retrieve the value of XmlField as an XmlDocument object and the results are output to the console.

The C# code in Program.cs in the project TableWithXmlDataType is shown in Example 9-13.

Example 9-13. File: Program.cs for TableWithXmlDataType solution

using System; using System.Data; using System.Data.SqlClient; using System.Xml; using System.Data.SqlTypes; namespace TableWithXmlDataType { class Program { static ...