inserting to xml column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inserting to xml column

Author

Topic

vishalg
Starting Member

29 Posts

Posted - 2009-07-10 : 03:07:30

Hello,

First I would like to explain what I am doing. I am trying to read and xml file using c# webservices to extract xmlnodes. For this purpose m using the following

XmlNode root = xmldoc.DocumentElement;
XmlNodeList visitsummaryxml = xmldoc.GetElementsByTagName("//CLINICALINFO");

Thus m getting the CLINICALINFO xmlnode and its children in the visitsummaryxml nodelist

then m trying to pass this xmlnodelist to a stored procedure,

command.Parameters.Add(new SqlParameter("@VISITSUMMARYXML", visitsummaryxml));

my stored procedure looks like this

CREATE PROCEDURE [dbo].[savePatientDetails]
(
	@VISITID VARCHAR(50),
	@VISITDATE VARCHAR(50),
	@VISITLOCATION VARCHAR(MAX),
	@VISITSUMMARYXML XML
	
)
AS
	SET NOCOUNT ON 

INSERT INTO cdb_clinicalvisit
		(
			VISITID,
			VISITDATE,
			VISITLOCATION,
			VISITSUMMARYXML
					)VALUES(
				@VISITID,
				(convert(datetime, @VISITDATE)),
				@VISITLOCATION,
				@VISITSUMMARYXML
				)
RETURN

now the problem is.. when i try to run this web service, it gives me the following runtime error

System.ArgumentException: No mapping exists from object type System.Xml.XmlElementList to a known managed provider native type.
at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen)
at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at xmldataretrive.xmldataretrive.xmldata() in C:\Users\vinchenzo\Documents\Visual Studio 2005\Projects\xmldataretrive\xmldataretrive\xmldataretrive.asmx.cs:line 90