If you are working with XML data types in your .NET and SQL Server code, then you will most likely encounter this error message at some point.
Specifically, this error is related to improperly passing an XML value as a SQLParameter to your database.
Since most of the work one does with XML in .NET is done using the XmlDocument object, it is certainly surprising when Visual Studio complains when one tries to save the XML object as an XmlDocument to the database (say to a stored procedure or directly to a database table).
The bottom line is that you must adjust your XmlDocument object to a format compatible with the SqlDbType.XML accepted by your SqlParameters.
There are a few ways that you can use to pass your XML object to your database:
1) The first way would be simply to convert the object to a String and then receive the object as a string parameter in a stored procedure which can then cast it to the XML data type, and save it to a table.
2) The second way is to use the XmlNodeReader object. This is quite straightforwad and can be done as follows:
‘ Create the XmlDocument object (in this case from a session object)
Dim xmlDocSave As New XmlDocument()
‘ Set up a new XmlNodeReader populated with the XmlDocument object
Dim xdrSave As New XmlNodeReader(xmlDocSave)
‘ Add the XmlNodeReader to your XML parameter
Dim paramSave As New SqlParameter()
paramSave.DbType = DbType.Xml
paramSave.ParameterName = “SaveMyXML”
paramSave.Value = xdrSave
3) A third way is to import the XmlDocument’s OuterXml into a StringReader and then to populate an XmlTextReader with the contents of the String
' Create the XmlDocument object (in this case from a session object) Dim xmlDocSave As New XmlDocument() xmlDocSave.LoadXml(CStr(Session("xml"))) ' Set up a new StringReader populated with the XmlDocument object's outer Xml Dim srObject As New StringReader(xmlDocSave.OuterXml) ' Set up a new XmlTextReader populated with the Xml from the StringReader Dim xtrSave As New XmlTextReader(srObject) ' Add the XmlTextReader to your XML parameter Dim paramSave As New SqlParameter() paramSave.DbType = DbType.Xml paramSave.ParameterName = "SaveMyXML" paramSave.Value = xtrSave