Insert XML into SQL Server

[Mervin]

Does anyone have any sample code for inserting the XML from a XDocument into
a SQL Server XML column?

Thanks.

 

[Mervin]

I'm actually using a SqlDataSource which sets the type for the xml parameter
as "Object". However, I have tried 3 different methods of inserting the data
in the formview ItemInserting event) and have received exceptions for each:

XDocument responseXML = XDocument.Load(tr);

1) e.Values["Response_Xml"] = responseXML.ToString();
--> Exception: "Operand type clash: sql_variant is incompatible
with xml"

2) e.Values["Response_Xml"] = new SqlXml(responseXML.CreateReader());
--> Exception: "Operand type clash: sql_variant is incompatible
with xml"

3) e.Values["Response_Xml"] = responseXML;
--> Exception: "No mapping exists from object type
System.Xml.Linq.XDocument to a known managed provider native type."

Thanks.

=============================================

 

[Mervin]

I discovered the answer. If using a SqlDataSource on a FormView, within the
..aspx page change the Parameter type from Object to String. Then, set the
value of the XML column to the string of the XDocument value:

XDocument responseXML = XDocument.Load(tr);
e.Values["Response_Xml"] = responseXML.ToString();

Mervin