In sql server 2000, to send an xml document to a stored procedure, I needed to use the following method:
Here is the C# .NET code to prepare the xml to be sent to sproc.
SqlCommand cmd = new SqlCommand("ScanResults", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@doc", SqlDbType.NText).Value = XMLReturn;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
Here is the how the sproc handles this:
CREATE PROCEDURE [csTest].[ScanResults] (@doc ntext)
DECLARE @iTree int
EXEC sp_xml_preparedocument @iTree OUTPUT, @doc
EXEC sp_xml_removedocument @iTree
Now I am working with sql server 2005, and from what I understand it has the ability to send actualy xml document types to sprocs... I would like to know how to change the sproc and code snippet to use this new method. Thanks.