[rhaazy]

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.

con.Open();
SqlCommand cmd = new SqlCommand("ScanResults", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@doc", SqlDbType.NText).Value = XMLReturn;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dataset, "ID");
con.Close();

Here is the how the sproc handles this:

CREATE PROCEDURE [csTest].[ScanResults] (@doc ntext)

AS
DECLARE @iTree int
EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

~buncha crap~

EXEC sp_xml_removedocument @iTree
	
RETURN

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.

[rhaazy]

Also, if someone could perhaps make more clear to me, the benefits one can gain from using an xsd document in addition to using XML with SQL Server 2005. From what I understand, the xsd resides on the server and I use it to validate the xml. But how else can the xsd benefit me in terms of performance?

[urstop]

Not sure about 2005, But XSDs can be used to exuecute queires on the SQL Database. So they can be used as alternatives to direct SQL Queries or SQL Queries in stored procedures.

The advantage is similar to SPs, where in we can jsut modify the XSD if we need to modify the data that is returned instead of changing your whole code.