ADO.NET 3.5 Cookbook, 2nd Edition

9.6. Storing and Retrieving XML with a Non-XML Data Type Column

Problem

You need to store XML to a database column that does not directly support XML.

Solution

Store the contents of the InnerXml of the XmlDocument to the database. You can later load this into an empty XmlDocument with LoadXml().

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

Example 9-9. T-SQL statement: Create table StoreXmlDatabaseField

USE AdoDotNet35Cookbook
GO
CREATE TABLE StoreXmlDatabaseField(
    Id int NOT NULL PRIMARY KEY,
    XmlField nvarchar(max) NULL)

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

Table 9-5. StoreXmlDatabaseField schema

Column name	Data type	Length	Allow nulls?
Idi	int	4	No
XmlField	nvarchar	MAX	Yes

The solution uses the XML file Department.xml shown in Example 9-10. This file contains the XML representation of the first three records in the HumanResources.Department table in AdventureWorks.

Example 9-10. File: Department.xml

<?xml version="1.0" standalone="yes"?> <DocumentElement> <Department> <DepartmentID>1</DepartmentID> <Name>Engineering</Name> <GroupName>Research and Development</GroupName> <ModifiedDate>2007-10-06T19:15:31.803-04:00</ModifiedDate> </Department> <Department> <DepartmentID>2</DepartmentID> <Name>Tool Design</Name> <GroupName>Research and Development</GroupName> <ModifiedDate>2007-10-06T18:52:47.04-04:00</ModifiedDate> </Department> <Department> <DepartmentID>3</DepartmentID> <Name>Sales</Name> <GroupName>Sales ...